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)
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.
db.Customers.Where(c=>!db.Blacklists.Any(b=>b.CusId==c.CusId)). However, same SQL expression will be generated by Entity Framework.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;
Where (but it could be that EF optimizes that so that it is only fetched once as well)
BlackList table was added as a WHERE NOT EXISTS (SELECT NULL AS [Empty] FROM [Blacklist] ... so it doesn't even retrieve it, SQL does all the work and it is nicely optimized.
WHERE CusId NOT IN (1, 2, 3, 4) type of statement. For short lists or where there's extra logic to filter the lists that is the best solution, use it myself often too.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
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();
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();
