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.

For re-using select lambdas: stackoverflow.com/questions/38766411/… (This question has a higher SEO) — ono2012
– ono2012, Commented Nov 24, 2017 at 21:27

Robert McKee · Accepted Answer · 2015-11-09 19:54:14Z

5

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");

answered Nov 9, 2015 at 19:54

Robert McKee

21.5k1 gold badge48 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Laurence Frost Over a year ago

I didn't think of doing it this way. I have marked the other answer as correct since it most closely answers my question, but I prefer this syntax.

Robert McKee Over a year ago

To be fair, I liked his answer too. Short, sweet, and did exactly what you asked. I just offered an alternative.

Jon Hanna Over a year ago

Both approaches have their place, depending on just how you're likely to re-use the filter only as a Where or re-use the predicate in other ways.

Jon Hanna · Accepted Answer · 2015-11-09 23:07:24Z

3

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");

edited Nov 9, 2015 at 23:07

answered Nov 9, 2015 at 19:06

Jon Hanna

114k10 gold badges151 silver badges258 bronze badges

1 Comment

Jon Hanna Over a year ago

@JeffDunlop it doesn't even compile, as I was thinking of delegates rather than expressions. It is possible to compose predicates into other predicates with expressions, but it's more convoluted than is worth in this case. I'd edited to remove that bit.

Collectives™ on Stack Overflow

Entity Framework Linq embed lambda expressions into a reusable function

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related