Tag Archives: LINQ

Filter Sub Entity in Query of LinqToEntities DomainService

If you are one of the developers, who are working on the project that use Domain Service with LinqToEntities, this common issue should annoy you sometimes.

Normally we use Include(“RelatedEntity”) to tell LinqToEntitiesDomainService to also retrieve ALL records of RelatedEntity that have relation to the main querying entity.

For example with this data:
Customer Table

CustomerId Name Sex
1 Mr. Wise Male
2 Mrs. Fool Female

Order Table

OrderId CustomerId Total PaymentTypeId
1 1 $5000 1
2 1 $200 1

PaymentType Table

PaymentTypeId PaymentTypeName
1 Cash
2 Credit Card

With this sample data, if we need all Male Customers along with their Orders. It would look like this.

var customers = from c in this.ObjectContext.Customers.Include("Orders")
                where c.CustomerId == 1
                select c;

But what if we need all Male Customers along with their Orders that Total over $1000 ONLY ?

Well, there is no regular operation to embed condition into the query for filtering out the sub entities. The Where statement only filters the main entity, the Customer in this case.

There is a workaround by modify query with this tricky query.

var customers = (from c in this.ObjectContext.Customers
                 where c.CustomerId == 1
                 select new {
                   MaleCustomer = c,
                   OrderOverThousand = c.Orders.Where(o => o.Total > 1000)
                 }).ToList().Select(c=>c.MaleCustomer);

Note that you do not need to have Include operation for this query. It does populate all entities you specified in the enclosed anonymous class after the Select statement.

You may find this workaround in several blogs but I couldn’t find two level depth of related entity.

With the same query condition but also expect PaymentType of Orders, it would look like this:

var customers = (from c in this.ObjectContext.Customers
                 where c.CustomerId == 1
                 let filteredOrders = c.Orders.Where(o => o.Total > 1000)
                 select new {
                   MaleCustomer = c,
                   OrderOverThousand = filteredOrders,
                   OrderPaymentType = filteredOrders.Select(o => o.PaymentType)
                 }).ToList().Select(c=>c.MaleCustomer);

As you can see, all you have to do is just put another property of anonymous class as many entities as you want. You may doubt, “What does the let statement do?” and “Why do we need it?”. It is optional but it can save you from unnecessary addition Select operation when the context transforms it into the SQL statement.

As you can see at line number 6 and 7, without let statement, it would be c.Orders.Where(o => o.Total > 1000) instead of the filteredOrders variable.  As a result, both will do Select operation twice for the same result set.

Hope this post can elucidate some who struggle in such problems 😉 .

Tagged , , , , , ,