Tutorial :How can I avoid repeating myself in LinqToEntities?


I find myself repeating business-rules too much in my LinqToEntities when querying. That's not good. ;)

Say I have two tables:


  • Id
  • Name


  • Id
  • MemberId (fk to member.Id)
  • StartDate
  • ExpirationDate
  • IsCancelled

Now I define a valid membership as:

  1. Now is between StartDate and ExpirationDate
  2. IsCancelled is false

So now I would write functions in repositories like...:

  1. GetActiveMemberships
  2. GetMembersWithActiveMemberships
  3. MemberHasActiveMembership
  4. (etc)

...So in all these functions, that use LinqToEntities, I have code like..:

...  And membership.IsCancelled = 0 _  And membership.StartDate < Now() _  And membership.ExpirationDate > Now() _  ...  

What is the best way to avoid repeating this in every single Linq-to-entities query?
Can I separate my definition of a valid membership and apply that in other functions?


It looks like you're using VB.NET. I apologize for answering with some C# code, but hopefully you can translate.

I can think of a couple ways of tackling this. The first idea is to create a function that returns a predicate of type Func. This allows EF to reverse engineer the predicate into a query. For example:

static Func<MemberShip, bool> IsActiveMember()  {      return m => (          (m.IsCancelled == 0) &&          (m.StartDate < DateTime.Now) &&          (m.ExpirationDate > DateTime.Now));  }  

The big drawback to this approach is that you can't use the "comprehension query" syntax (from...where...select). You have to use the LINQ methods directly, as in:

var context = new AppEntities();  var activeMembers = context.MemberShipSet.Where(IsActiveMember());  

Depending on how you need to use it, a better approach might be to create a function or property that returns the set of MemberShip entities pre-filtered to only include the "active" ones. Here is an example of extending the context class created by the EF designer with such a property:

partial class AppEntities  {      public IQueryable<MemberShip> ActiveMembers      {          get          {              return                  from m in this.MemberShipSet                  where (                      (m.IsCancelled == 0) &&                      (m.StartDate < DateTime.Now) &&                      (m.ExpirationDate > DateTime.Now))                  select m;          }      }  }  

The neat thing about this approach is that you can formulate LINQ queries against ActiveMembers. For example:

var lastThirty = DateTime.Now.AddDays(-30);  var context = new AppEntities();  var recentActiveMembers =      from mr in context.ActiveMembers      where (mr.StartDate > lastThirty)      select mr;   

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »