CODEWITHSUNDEEP
c#entity-framework
Daybreaker
Daybreaker

Reputation: 1047

Select records that does not exist in another table in Entity Framework

I have two tables - "Customer" table and "Blacklist" customer table. When I blacklist a customer, I put the CusId as a foreign key into to Blacklist table.

What I want to do is get CusId and Name that are not in the BlackList Table.

How can I achieve this in Entity Framework C#?

Customer
---------
(CusId,Name,Telephone,Email)

Blacklist
---------
(CusId)

Upvotes: 34

Views: 39554

Answers (5)

Laith Sa'd Al-Deen
Laith Sa'd Al-Deen

Reputation: 104

This query I used and it works perfectly:

var query = _context.Customer.Where(x => x.Id == id && !x.BlackList.Any(z => x.Id == x.CustId)).ToList();

Upvotes: 0

Mohammad Komaei
Mohammad Komaei

Reputation: 9676

Any() is the best performance :

db.Customers.Where(c => !db.Blacklists.Any(b => b.CusId == c.Id));

If you also need columns of Blacklists , use join with condition : b.CusId == c.Id

Upvotes: 5

Diego
Diego

Reputation: 2372

IF in table BlackList yo have a List of Customer You can perform something like

 IEnumerable<Customer> model = (from c in db.Customer
                                from b in c.BlackList.DefaultIfEmpty()
                                 where b.CusID== null
                                  select new Customer
                                   {
                                        CusId= c.OrderID
                                    }).ToList();

Upvotes: 1

Tim Rolen
Tim Rolen

Reputation: 57

How about something like this:

var subselect = (from b in BlackList select b.CusId).ToList();

var result = from c in Customer where !subselect.Contains(c.CusId) select c;

Upvotes: 6

Timothy Walters
Timothy Walters

Reputation: 16884

What you want is something like the following:

db.Customers
    .Where(c => !db.Blacklists
        .Select(b => b.CusId)
        .Contains(c.CusId)
    );

EF will happily turn that into a sub-query that will run quite well.

This pattern works for static lists (creates an IN(a, b, c) expression) as well as other tables. You can use it to check for being in the list or not in the list.

If you want to test it and see the SQL it generates, I strongly recommend LINQPad (it is free). That's what I use to test out little ideas in LINQ all the time.

Upvotes: 80

Related Questions