EF IQueryable Extension Methods

I've started building extension methods for my EF classes like below because it makes building queries so easy, and the intellisense provided helps keep people from writing a new Where() clause in code every time they do to do something.

Example Extensions to an EF User Class

public static class UserIQX
{
public static IQueryable<User> WhereInSearch(this IQueryable<User> value, string search)
{
search = search.ToLower();
return value.Where(user => user.FirstName.ToLower().Contains(search) || user.LastName.ToLower().Contains(search) || user.EmailAddress.ToLower().Contains(search));
}

public static IQueryable<User> WhereInRole(this IQueryable<User> value, RoleEnum role)
{
return value.Where(user => user.RoleID == (int)role);
}
}


Examples - "Get all admins named Bob"

protected void Page_Load(object sender, EventArgs e)
{
var search = "Bob";

//With Extensions
.WhereInSearch(search)
.ToList();

//Without extensions
search = search.ToLower();
var pq = db.Users
.Where(u => (
u.FirstName.ToLower().Contains(search)
|| u.LastName.ToLower().Contains(search)

.ToList();
}


Results

The results are comparable with the inline one edging out ahead in speed. It's interesting that the extension-based one used a parameter for the roleID and the inline one did not. That's the only real difference I can see.

I really like the fluent-like style the Extension method provides, and how it encapsulates logic to prevent erroneous queries from being written (like forgetting to ToLower() the string fields or something). Is there a good reason why this would be a Really Bad Idea? If not, I prefer the easy maintainability of the Extension methods over inline.

• Fully agree. I'm using this full out through my projects, in combination with overloading. You'll get like Db.Comments.By(currentUser).By(thread).OrderByDate(). Personally I think it is really readable and well maintainable, but I still need to test that with others ;) – Dirk Boer Jan 23 '16 at 2:26
• So in your example I would take it even a step further: db.Users.By(RoleEnum.Admin).FilterBy(keyword).SelectEmails() - but quite curious what others think. – Dirk Boer Jan 23 '16 at 2:30
• Normally I extract the expressions themselves into reusable methods, but I really like this idea. – RubberDuck Jan 23 '16 at 12:24

By moving this question from StackOverflow to here, usr's sensible remarks were lost. I restore them here as community wiki:

No, this is fine and recommended. You'll find a limitation of this approach: You cannot use these extension methods in places where they get turned into an expression:

db.Something.Any(x => x.Users.WhereInRole(RoleEnum.Admin))


This does not work because EF does not know the WhereInRole method.

The AsExpandable extension method can help with this. Search the web to find it as a small library.

Using the same approach you should be able to inline constants such as the role ID into the query as well.

Note, that the design of WhereInRole is not really ideal. Normally, you'd write a method bool IsUserInRole(User, int). No need to operate on sequences at all. The only reason this is not possible here is that you need to find a way to marshal the filtering logic into the final expression tree. Shoving the logic into the argument to Where is a hack to do that. This issue/concern is a "dirty secret" of LINQ. It's quite hacky and arbitrary.