CODEWITHSUNDEEP
c#entity-frameworklinqentity-framework-core
Stuart Aitken
Stuart Aitken

Reputation: 1012

How to use my own methods or write a DbFunction for EF Core querying (EF Core 3.0)

I previously had the following set up:

public static bool BlogIsLive(BlogPost b)
{
    return b.Status == (int)ItemStatus.Active && b.PublishDate < DateTime.Now ;
}

/// Database query

var blogs = (from b in db.BlogPost 
             where BlogIsLive(b) // <--- super useful, used in multiple places
             select b
             ).ToList()

But after updating to EF Core 3.0, it throws the following error

/// The LINQ expression ... could not be translated. Either rewrite the query in a form 
/// that can be translated, or switch to client evaluation explicitly by inserting a 
/// call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

I understand this is part of the breaking changes in EF Core 3.0

Now I have to write the query manually in all the places where BlogsIsLive() was before.

var blogs = from b in db.BlogPost 
            where b.Status == (int)ItemStatus.Active  //<--- Annoying repetition of code
            && b.PublishDate < DateTime.Now           //<---
            select b

This is highly annoying. Is there no way I can write a method that slots into there?

I know EF has DbFunctions which, for example, can ease the process of comparing Date values, so I see no reason why it would not be possible to write something of my own that does similar involving Int, string or bool.

Something like: 

public static DbFunction BlogIsLive(BlogPost b)
{
    //Example
    return DbFunction(b.Status == (int)ItemStatus.Active && b.PublishDate < DateTime.Now);
}

/// Database query

var blogs = (from b in db.BlogPost 
             where MyDbFunctions.BlogIsLive(b)
             select b
             ).ToList();

I've tried a few variations on the above, but no luck.

Thanks.

Upvotes: 1

Views: 1237

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131722

The original code has a serious bug that would throw in any non-Core version of EF too - it's a local function, it can't be translated to SQL. Where accepts expressions as arguments, not functions. You don't need that function anyway.

LINQ works with IQueryable and expressions. Each operator takes one IQueryable and returns another. That's how Where and Select work already. This means you can create your own function that adds the Where condition you want :

public static IQueryable<BlogPost> WhereActive(this IQueryable<BlogPost> query)
{
    return query.Where(b=>b.Status == (int)ItemStatus.Active && b.PublishDate < DateTime.Now);
}

And use it with any IQueryable<BlogPost>, eg :

var finalQuery = query.WhereActive();
var posts=finalQuery.ToList();

Another, more cumbersome option is to construct the Expression<Func<>> call in code, and pass that to Where - essentially creating the WHERE condition dynamically. In this case it's not needed though.

EF Core 1.0 added a very unfortunate feature (more like a what-were-they-thinking! kind of feature), client-side evaluation. If something can't be translated, just load everything in memory and try to filter stuff without the benefit of indexing, execution plans, matching algorithms, RAM and CPUs found in a database server.

This may not be noticed if only 100 rows are loaded by only 1 client at a time, it's a perf-killer for any application with even small amounts of data and concurrent users.

In a web application, this translates to more servers to handle the same traffic.

That's why client-side evaluation was removed when EF 1.0 was introduced back in 2008.

Upvotes: 2

Neil
Neil

Reputation: 11919

Instead of using db.BlogPost as the base of the query, you can use a DbSet that already has that filter on it.

DbSet<BlogPost> _allBlogs {get;set;}

IQueryable<BlogPost> ActiveBlogs { get => _allBlogs.Where(b=> b.Status == (int)ItemStatus.Active); }

var blogs = from b in db.ActiveBlogs
        select b

Upvotes: 1

Related Questions