How can I avoid repeating myself in LinqToEntities?

Question

I find myself repeating business-riles 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:

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?

Answer 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;