Question:
I find myself repeating business-rules too much in my LinqToEntities when querying. That's not good. ;)
Say I have two tables:
Member
- Id
- Name
MemberShip
- Id
- MemberId (fk to member.Id)
- StartDate
- ExpirationDate
- IsCancelled
Now I define a valid membership as:
- Now is between StartDate and ExpirationDate
- IsCancelled is false
So now I would write functions in repositories like...:
- GetActiveMemberships
- GetMembersWithActiveMemberships
- MemberHasActiveMembership
- (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?
Solution:1
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
EmoticonEmoticon