CODEWITHSUNDEEP
c#linq-to-sql
DreamsDotNet
DreamsDotNet

Reputation: 48

how to select from two tables with two conditions in linq

i have two tables which has relation, i want to query from it with two conditions, like this:

var data = from d in db.table1
           where !d.isdeleted & d.table2.Where(dm => !dm.isdeleted)
           select d;

but i have this error:

Operator '&' cannot be applied to operands of type 'bool' and 'System.Collections.Generic.IEnumerable'

thanx

Upvotes: 0

Views: 1599

Answers (3)

Tilak
Tilak

Reputation: 30698

You should do two changes

1 . Change & to && (this is not required, but a good practice until you want second operand to execute in all cases)

2 . Change IEnumerable<T> (or IQueryable<T>) to bool. Instead of Where, use Any

var data = from d in db.table1
       where !d.isdeleted && d.table2.Any(dm => !dm.isdeleted) 
       select d;

Upvotes: 1

TylerOhlsen
TylerOhlsen

Reputation: 5578

If they are related, you could join the tables...

public class Program
{
    private static void Main(string[] args)
    {
        List<ItemA> itemATable = new List<ItemA>(new[]
            {
                new ItemA {Id = "A1", IsDeleted = true},
                new ItemA {Id = "A2", IsDeleted = false},
            });

        List<ItemB> itemBTable = new List<ItemB>(new[]
            {
                new ItemB {Id = "B1", ItemAId = "A1", IsDeleted = true},
                new ItemB {Id = "B2", ItemAId = "A1", IsDeleted = false},
                new ItemB {Id = "B3", ItemAId = "A2", IsDeleted = true},
                new ItemB {Id = "B4", ItemAId = "A2", IsDeleted = false},
            });

        var test = from a in itemATable
                   join b in itemBTable on a.Id equals b.ItemAId
                   where !a.IsDeleted && !b.IsDeleted
                   select new { a, b };
    }
}

public class ItemA
{
    public string Id { get; set; }
    public bool IsDeleted { get; set; }
}

public class ItemB
{
    public string Id { get; set; }
    public string ItemAId { get; set; }
    public bool IsDeleted { get; set; }
}

Upvotes: 1

Andrew Cooper
Andrew Cooper

Reputation: 32576

Maybe this is what you're after?

var data = from d in db.table1
           where !d.isdeleted && d.table2.All(dm => !dm.isdeleted)
           select d;

This will select rows from table1 where isdeleted is false and all the corresponding rows in table2 have isdeleted equal to false.

Upvotes: 1

Related Questions