c#linq entity-framework-core filtering asenumerable

EF Core - Filtering query on C# method (buffering vs streaming)

Scenario

I have this code (simplified):

var query = _context.Products
                    .Where(x => x.IsActive)
                    .Select(x => new ProductDto 
                                 {
                                     Id = x.Id,
                                     Name = x.Name,
                                     Description = GetDescription(x)
                                 });

// Building predicates (using LinqKit)
var predicate = PredicateBuilder.New<T>();

query = query.Where(predicate);

var count = await query.CountAsync();
var list = await query.ToListAsync();

And it works when I filter by Id, Name. The issue arises when I want to filter by Description, eg:

query = query.Where(x => x.Description.Contains("winter"));

Here an exception is thrown - Linq cannot translate this into SQL - understandable.

Question

How do you think I could handle this? This is how I have built my filtering/sorting, etc., and I want to keep it this way. How can I filter/sort by properties that use the C# method that cannot be translated into SQL?

What I tried

After reading this Efficient Querying EF Core I got an idea that maybe this is the way to go.

I tried to do it this way:

var predicate = PredicateBuilder.New<T>();

var enumerable = query.AsEnumerable();

enumerable = enumerable.Where(predicate);

// I convert it back to Queryable to use things such as 
// Take() and Skip() hoping it will be translated into SQL
query = enumerable.AsQueryable();

// Here I lost the ability to use "Async"
var count = query.Count();
var list = query.ToList();

And it seems to kind of work. I lost the ability to filter on the SQL level (but I may be able to separate which filters to apply at what stage), but I'm not sure how it works.

I think I understand the difference between buffering and streaming - here I do not load all the results to the memory - I expect streaming to happen. When inspecting DB calls - only single calls are made (with .Count() and .ToList()) which sounds good.

Is it a valid way to approach the problem? How exactly does filtering work after .AsEnumerable()? How does Linq know what to do with the GetDescription(x) if it neither loads it into memory nor sends a request to the database?

// Edit for more context:
I might have simplified too much, sorry about that. My GetDescription method looks like this:

public static string GetDescription(int productType)
{
    switch (productType)
    {
        case 0:
            return ProductTypeEnum.Value1.GetDescription();
        case 1:
            return ProductTypeEnum.Value2.GetDescription();
    }

    return string.Empty;
}

and I use it in the query in such a way:

Description  = x.ProductType != null ? GetDescription(x.ProductType) : string.Empty

Post-thoughts after resolving the issue
I misunderstood how queries are translated. The fact misled me, that without filtering by problematic property - everything worked fine. I thought it meant that it is evaluated on the server side and translated into SQL. What was happening was that this property was marked to be evaluated on the client side, once the records were fetched.

If your query cannot be translated into SQL - first make sure the query is well written, cause it's probably not.

Solution

I think I understand the difference between buffering and streaming

Difference here is between client-side evaluation and server-side evaluation.

Is it a valid way to approach the problem?

Arguably - no.

How exactly does filtering work after .AsEnumerable()?

Everything "before" will be converted to SQL and performed on the database side, everything after will be handled on the client side (in-memory) i.e. for Count all matching rows will be fetched into memory, GetDescription will be called on them and then filtering will happen and then items will be counted. Then you will fetch and filter all the same items again for ToList. Note that EF Core can call custom functions in final projection (i.e. last Select) without moving to much to client side. EF Core also can handle filtering on the projection properties if they were created via translatable code (i.e. one that does not use client-side functions like GetDescription). Also note that GetDescription(x) should result in fetching all the columns of the table.

How does Linq know what to do with the GetDescription(x) if it neither loads it into memory nor sends a request to the database?

LINQ-to-Objects will be used which will just call the method

When inspecting DB calls - only single calls are made

I wonder how you did that and what queries you have seen. Try enabling simple logging for EF Core and checking the logs.

What you can do depends heavily on the GetDescription. You can provide a user defined function mapping which should make it translatable. Or do something like:

var query = _context.Products
   .Where(x => x.IsActive)
   .Where(x => x.DescriptionPartColumn1.Contains("winter") 
       || x.DescriptionPartColumn1.Contains("winter"))
   .Select(x => new ProductDto ...)

Also I recommend to check out:

UPD

Just inline the GetDescription function:

var query = _context.Products
   .Where(x => x.IsActive)
   .Select(x => new ProductDto
   {
       //...
       Description = x.ProductType == 0
           ? "This is one Desc"
           :  x.ProductType == 1 
                 ? "This is two Desc" 
                 : string.Empty
   })