Automatically add some Where clauses to a Linq Expression Tree

By : Ryan M
Source: Stackoverflow.com
Question!

I am using the Entity Framework and Linq to Entities. I have created a small database pattern & framework to implement versioning as well as localization. Every entity now consists of two or three tables, (ie Product, ProductBase & ProductLocal).

My linq always includes the following boilerplate code:

from o in DB.Product
from b in o.Base
from l in o.Local
WHERE o.VersionStatus == (int)VersionStatus.Active 
   && b.VersionStatus == (int)VersionStatus.Active 
   && l.VersionStatus == (int)VersionStatus.Active 
   && l.VersionLanguage == Context.CurrentLanguage
select new ProductInstance { Instance = o, Base = b, Local = l }

What I would like to accomplish is to turn the above into:

(from o in DB.Product
 from b in o.Base
 from l in o.Local
 select new ProductInstance { Instance = o, Base = b, Local = l }).IsActive()

Or at worst, something like:

from o in DB.Product.Active()
from b in o.Base.Active()
from l in o.Local.Active()
select new ProductInstance { Instance = o, Base = b, Local = l }

I have extended the base classes the EDM generates to implement some interfaces that enforce the properties ( IVersionStatus and/or IVersionLanguage ). Is there some way I can walk the expression tree, check if the type in the expression implements that interface, then set the VersionStatus accordingly?

I woud love it to be as simple as the first option, just less to write and/or forget. I have seen examples that do it after the fact, after its IEnumerable, but I would rather not pull more from the database than I need to.

Thanks for any tips!

By : Ryan M


Answers

I might be wrong, but I don't think DataLoadOptions works with the Entity Framework.

By : Ryan M


Yes.

You can do this by defining an extension method named IsActive on IQueryable. There is a property on IQueryable called "Expression" that returns an expression tree representing the chain of LINQ method calls that was generated from your query.

In your case that will look something like this:

DB.Product.SelectMany(o=>o.base, (o, b)=>new{o.b}).SelectMany(item=>o.local, (item, local)=>new {item.o, item.b, item.local}).Select(item=>new ProductInstance { Instance = item.o, Base = item.b, Local=item.Local});

The "DB.Product" is the item from the first From clause. Each remaining "SelectMany" call is an additional from clause.

You can then dig into the expression trees to gather all the from clause elements. look at their types, and then finally generate an expression tree for a where clause.

Your extension method would then return .Where off of it's IQueryable argument using the where clause that you generated.

The resulting Where clause would then be executed on the server with the rest of the query when you try to "foreach" over the results.

EDIT:

Please note that if you want this to work with explicit "Join" clauses then you will also need to add support for the "Join" method in addition to "SelectMany".



You need to use the DataLoadOptions class so that it automatically loads the foreign key relationships you specify on that object. That will make it so that it will automatically get the linked tables that you specify which is really what you're doing.

This page details how to do that and tells more about what I think you're looking for.

http://www.crazysalsadancer.com/2008/08/efficient-data-loading-with-linq-using.html



This video can help you solving your question :)
By: admin