Doing a LINQ function that can be called in an EF query

Question

I have the following expression:

Where(cd => cd.Modal == modal)
    .Select(s => s.Volume)
    .DefaultIfEmpty(0)
    .Sum()

what use to make a same calculation several times within a few querys through LINQ and tried to create the following Extension method, but when executing my query it gives the following error message:

LINQ to Entities does not recognize the method 'system decimal Calculatevolume'

Method:

public static decimal CalculateVolume(this IEnumerable<MyEntity> query, Modal modal)
{
    return query.Where(cd => cd.Modal == modal)
              .Select(s => s.Volume)
              .DefaultIfEmpty(0)
              .Sum();
}

Here’s an example of how I tried to execute the command:

var modal = 1;
_context.EntityTeste
    .Select(c => new
    {
        Current = c.MyEntity.CalculateVolume(modal)
    })

The entity MyEntity is a list within the EntityTeste.

How can I make it so that I can transform this chunk of code that is repeated several times into an instruction that LINQ understands and executes in the database?

Answer 1

I will not state that it is not possible to do in SQL Server that supports creating function with . NET, but the process is not so trivial, and I’m not saying it gets good or even gives itself. In other databases it certainly does not. To run in the database this has to support this, there is no way an algorithm written in C# magically go to the database and run there.

Any solution you use will run in memory. If you are using a IQueryable there will be a conversion of what you give to a query, possibly SQL because it must be using a DB like this), which will be sent to the database and return the filtered data, but without the filter part that it does not know how to convert, then the data that come will run in memory making the filter of your method generating the final result the way you want.

I’m not saying you can’t do this query give the same result totally in SQL by writing LINQ otherwise, but it seems that.

In general, anything that comes out of the trivial is better to pass a query SQL and avoid LINQ.

The issue of non-recognition must be something that is not in the code presented, it may be that the method is not available in this scope even. But it doesn’t matter because you won’t do what you want.

Answer 2

I did some research and found snippets saying you should use List<> as a function of working with rotating objects.

So try to add the method Tolist() before the .Where:

public static System.Linq.Expressions.Expression<Func<decimal>> CalculateVolume(this IEnumerable<MyEntity> query, Modal modal)
{
    return () => { return query.ToList().Where(cd => cd.Modal == modal).Select(s => s.Volume).DefaultIfEmpty(0).Sum(); };
}

This will load your table into memory, so be careful with large searches.

Also, I passed your code to a System.Linq.Expressions.Expression<Func<>> and used lambda to return your code.

I looked it up here.