Entity Framework Linq embed lambda expressions into a reusable function

Question

When using Linq with Entity Framework to query the database, is there a way of reducing repetitive lambdas? For example:

db.Users.FirstOrDefault(x => x.Username == "MM001" && x.Type == 1 && x.IsActive == True && x.ExpiryDate > DateTime.Now);

I would like to turn it into just:

db.Users.FirstOrDefault(x => x.Username == "MM001" && x.IsActiveStaff());

I have tried writing a method as follow into my Users POCO:

public bool IsActiveStaff()
{
    return Type == 1 && IsActive == True && ExpiryDate > DateTime.Now;
}

However, I get the following error: LINQ to Entities does not recognize the method 'Boolean IsActiveStaff()' method, and this method cannot be translated into a store expression.

I realise this is because LINQ To Entities cannot turn this method into an SQL Expression, but is there any way I can get this to work?

I know that I can write a query command class which simply takes a Username as a parameter, and place all of my logic in there, but I'd like to know if you can embed a series of lambdas into a method or extension method etc, and use them when required like in my example above.

Answer 1

I prefer using extension methods like:

public static class UsersExtensions
{
  public static IQueryable<User> IsActive(this IQueryable<User> u)
  {
    // Should really use UTC
    return u.Where(x=>x.IsActive && x.ExpiryDate>DateTime.Now);
  }
  public static IQueryable<User> IsStaff(this IQueryable<User> u)
  {
    return u.Where(x=>x.Type==1);
  }
}

Used like (with full intellisense):

var result=db.Users
  .IsActive()
  .IsStaff()
  .FirstOrDefault(x=>x.UserName=="MM001");

Answer 2

Create an expression of the appropriate type:

Expression<Func<User, bool>> IsActiveStaff = x => x.Type == 1 && x.IsActive && x.ExpiryDate > DateTime.Now;

Then:

db.Users.Where(IsActiveStaff).FirstOrDefault(x => x.Username == "MM001");