Include IEnumerable in LINQ To Entities queries

Imagine the following scenario based on Northwind.

For a customer I want to get his orders validity. I have an order collection and I want to know if there are some new orders, if some orders were deleted on the DB or if they are already there.

For this, we will get an anonymous collection with OrderID property and a nullable of bool property State which returns true if it’s a new Order, false if the order was deleted in DB else null.

Of course we want only one SQL query to do it with L2E.

EF4 supports it:

var q1 = (from o in context.Orders
          where o.CustomerID == customerId
          select new { o.OrderID, State = ordersId.Contains(o.OrderID) ? null : (bool?)true });
var q2 = from oId in ordersId.AsQueryable()
         where ! context.Orders.Any(o => o.CustomerID == customerId && o.OrderID == oId)
         select new { OrderID = oId, State = (bool?)false };
var q = q1.Union(q2);

EF4 rocks!!! [:)]

This entry was posted in 12253, 7671, 7674. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *