Using many methods in queries in EF 6.4.4

Question

I have extension methods that I am using in the query. Here are the methods:

public static class UOMExtensions
    {
        public static UOM GetSourceUOM(this UOM uOM)
        {
            //Calculate something
            return uOM;
        }

        public static UOM GetParentUOM(this UOM uOM)
        {
            var sourceUOM = uOM.GetSourceUOM();
            //Do something recoursively
            return sourceUOM;
        }

        public static bool IsCorrectUOM(this UOM uOM)
        {
            var parent = uOM.GetParentUOM();
            //Do something
            return true;
        }
    }

Here is my query:

 using (Context context = new Context())
        {
            var uoms = context.Set<UOM>().Where(a => a.IsCorrectUOM()).ToList();
        }

I got this exception:

System.NotSupportedException: 'LINQ to Entities does not recognize the method 'Boolean >IsCorrectUOM(ConsoleApp1.UOM)' method, and this method cannot be translated into a store expression.'

After that, I tried to create an expression for that using NJection.LambdaConverter:

public static bool IsCorrectUOMMethod(UOM uOM)
    {
        var parent = uOM.GetParentUOM();
        //Do something
        return true;
    }

    public static Expression<Func<UOM, bool>> IsCorrectUOMExression()
    {
        var lambda = Lambda.TransformMethodTo<Func<UOM, bool>>()
                       .From(() => IsCorrectUOMMethod)
                       .ToLambda();
        return lambda;
    }

My query became to:

using (Context context = new Context())
        {
            var uoms = context.Set<UOM>().Where(UOMExtensions.IsCorrectUOMExression()).ToList();
        }

After that I got this exception:

System.NotSupportedException: 'Unknown LINQ expression of type 'Block'.'

How can I execute my query successfully? I cannot change or remove my methods because they do complex things and have a lot of usages.

possible duplicate : https://stackoverflow.com/questions/20022250/c-sharp-linq-to-entities-does-not-recognize-the-method-boolean — iSR5, May 14 '20 at 04:29

score 2 · Accepted Answer · answered May 14 '20 at 07:47

You have to be aware of the difference between an IEnumerable and an IQueryable.

An object that implements IEnumerable represents a sequence: you can get the first element of the sequence, and once you got an element, you can ask for the next element in the sequence.

At its lowest level this is done using GetEnumerator(), and repeatedly MoveNext() / Current. foreach, ToList(), Any(), Count(), etc will deep inside use these methods.

An IQueryable however, does not represent the sequence itself, it represents the potential to create a sequence

To do this, the IQueryable holds an Expression and a Provider. The Expression is a general description of what data must be set. The Provider knows who must provide the data (usually a database management system), and what language is used to communicate with the DBMS (usually SQL).

As long as you concatenate LINQ statements that return IQueryable<...>, you are only changing the Expression. Only if you start enumerating, either by using GetEnumerator / MoveNext, or higher level: foreach, ToList, etc, the Expression is sent to the Provider, who will translate it into SQL and execute the query. The returned data is represented as an enumerable sequence.

The problem is that the Provider does not know your methods GetSourceUOM. GetParentUOM, etc. Therefore it can't translate them into SQL. In fact, there several LINQ methods that also can't be used. See Supported and Unsupported LINQ methods.

Back to your question

What you can do, is convert your extension methods such that it extends IQueryable<UOM>.

    public static IQueryable<UOM> WhereCorrectUOM(this IQueryable<UOM> source)
    {
        IQueryable<UOM> parents = source.ToParentUoms();
        // Do something with parents to decide whether to return true or false
        // for example:
        return parents.Where(parent => parent.IsCorrect).Any();
    }

    public static IQueryable<UOM> ToParentUoms(this IQueryable<UOM> source)
    {
        IQueryable<UOM> parentUoms = source.Select(item => ...)
        return parentUoms;
    }

    public static IQueryable<UOM> ToSourceUoms(this IQueryable<UOM> source)
    {
        //Calculate something
        IQueryable<UOM> sourceUoms = source.Select(item => ...)
        return sourceUoms;
    }

Alas, you forgot to mention your actual code, so you'll have to fill this in yourself. Important is that you only call supported LINQ and .NET methods, or your own extension methods for IQueryable<UOM>.

Using many methods in queries in EF 6.4.4

1 Answers1

Back to your question