How to create an extension method that is translatable to SQL

Question

I want to create a method to be reused in where clauses in other conditions.

I have something like this:

public static bool IsActive(this Store store, DateTime date)
{
    return store.StartDate <= date && (store.EndDate == null || storeSku.EndDate > date);
}

and I would like to use it like:

var activeStores = _dbContext.Store.Where(s => s.IsActive(date) && !s.deleted);
var toDeletedStores = _dbContext.Store.Where(s => !s.IsActive(date) && !s.deleted);

My problem when I do this is that the code is not translatable to sql. Can you tell me how to create this as reusable code that is translatable to SQL ?

Does this answer your question? [Writing an extension method for Entity Framework](https://stackoverflow.com/questions/36490302/writing-an-extension-method-for-entity-framework) — gunr2171, Nov 09 '20 at 17:18
I'm pretty sure you need to return Expression from your method (https://stackoverflow.com/questions/793571/why-would-you-use-expressionfunct-rather-than-funct) but I never done it myself so can't asnwer. — Alexei Levenkov, Nov 09 '20 at 17:18
@gunr2171 I don't think it is what OP is looking for - it looks like they want to extract shared conditions to be passed to SQL into a method rather than add more methods around calls to Where. — Alexei Levenkov, Nov 09 '20 at 17:21
It must be something along those lines, the thing with those answers is that they just allow one expression. I can't concatenate expressions with && inside my Where clause — RagnaRock, Nov 09 '20 at 17:21

Jonesopolis · Answer 1 · 2020-11-09T17:37:49.113

Try like this, where you create an extension method off an IQueryable<Store>, and return it, adding your where clause:

public static IQueryable<Store> ActiveOnlyByDate(this IQueryable<Store> query, DateTime date) where T : class
{
    return query.Where(x => x.StartDate <= date && (x.EndDate == null || x.EndDate > date);
}

using it like:

var activeStores = _dbContext.Store
                             .ActiveOnlyByDate(DateTime.Now)
                             .Where(x => !x.deleted);

What's nice about this pattern is you can begin to apply interfaces to your EF models, if you moved StartDate and EndDate to an interface, you can make an extension method off that, and make the code very reusable.

+1 for the good idea regarding the interfaces. Although its not exactly what I want. Plus Ideally this would work with IQueryable and IEnumerable so I could use the same code either retrieving in from the database of for other filterings — RagnaRock, Nov 09 '20 at 20:53

score 0 · Answer 2 · answered Nov 09 '20 at 17:57

0

You can use Expression Trees to convert a re-usable method to SQL in EF like in below.

NOTE: I Use this way instead of Extension-Method. But this will get converted to SQL Query as per your need.

Expression<Func<TStore, bool>> FilterActive<TStore>(DateTime date) where TStore : ITStore
    {
        return store => store.StartDate <= date && (store.EndDate == null || store.EndDate > date);
    }

Your Interface =>

public interface ITStore
{
    DateTime StartDate { get; set; }
    DateTime EndDate { get; set; }
}

Usage goes like this:

_context.Store.Where(FilterActive<Store>(new DateTime()))

answered Nov 09 '20 at 17:57

sntpcvan

5
1
2

What if you want to use a second expression on the same where clause? (to filter by something else for example) – RagnaRock Nov 09 '20 at 18:05
1

You cannot apply multiple expressiontree predicates in same where, you can try https://stackoverflow.com/a/46172422/8054508 All I would suggest you to have this .where(predicate) and then use another "where" there you apply your other business logics. Anyhow Importance of ExpressionTree in case EF Queries is well explained here: [link](http://fascinatedwithsoftware.com/blog/post/2012/01/10/More-on-Expression-vs-Func-with-Entity-Framework.aspx) – sntpcvan Nov 10 '20 at 06:41

How to create an extension method that is translatable to SQL

2 Answers2