linq to entities doesn't recognize a method

Question

I have those methods:

   public int count(
        Guid companyId, Expression<Func<T, bool>> isMatch)
    {
        var filters = new Expression<Func<T, bool>>[]{
            x => x.PriceDefinition.CompanyId == companyId,
            isMatch
        };

        return GetCount(filters);
    }

public virtual int GetCount(
            IEnumerable<Expression<Func<T, bool>>> filters)
        {
            IQueryable<T> _query = ObjectSet;

            if (filters != null)
            {
                foreach (var filter in filters)
                {
                    _query = _query.Where(filter);
                }
            }

           return _query.Count();
        }

When using:

count(some_guid, x => x.IsMatch(entityId, inviterId, routeId, luggageTypeId));

I get the following exception:

LINQ to Entities does not recognize the method 'Boolean IsMatch(System.Nullable`1[System.Int64], System.Nullable`1[System.Int64], System.Nullable`1[System.Int64], System.Nullable`1[System.Int64])' method, and this method cannot be translated into a store expression.

What is the reason for this?
How can I solve it?

Answer 1

When using linq-to-entities you cannot use arbitrary .NET methods in query. Each method used in the query must be translatable to SQL. It will not help you to return Expession<Func<entityType, bool>> because where condition must be evaluated for each record on the database server.

For EF your code means something like:

SELECT COUNT(*)
FROM ...
LEFT JOIN ...
WHERE IsMatch(....) 

Because EF validates function names passed to the query it will throw exception because it doesn't know IsMatch equivalent on SQL server.

The only possible functions which can be used in Linq-to-entities are:

• Cannonical functions with predefined mapping to SQL equivalent
• EdmFunctions

EdmFunctions are methods marked with EdmFunctionAttribute which maps .NET function to SQL counterpart. Those functions usually cannot be executed in common .NET code because they do nothing or throw exception. They are only function place holder for Linq-to-entities. Available EdmFunctions are:

• Predefined EdmFunctions in System.Data.Objects.EntityFunctions
• Predefined EdmFunctions for SQL Server (not compact) in System.Data.Objects.SqlClient.SqlFunctions
• Custom mapped SQL functions - import wizard in Entity designer allows you import SQL functions (except table valued functions). You can after that write custom static .NET function and map it by EdmFunction attribute to the SQL function imported to designer.
• Custom model defined functions - this is special function written manually in EDMX file (opened as XML). It is custom reusable part of Entity SQL.

I have already described how to create model defined function in another answer. Creating mapped SQL function is pretty similar. Instead of manually creating Function element in EDMX you will map EdmFunctionAttribute properties to imported SQL function.

Answer 2

You're passing an expression that calls a function named IsMatch.

LINQ to Entities doesn't know what to do with this function.

Answer 3

I was dealing with similar problem. Working solution was using .AsEnumerable() before trying to use my custom method. You can take a look at it here.

Answer 4

Actualy, what you are passing to count look like this function:

bool anonymous_delagate#123(T entity)
{
    return entity.IsMatch(a,b,c,d)
}

But, this would require EF to know, what really method IsMatch, that is called on this entity, means.

Only thing I can think about recomending now is to use some kind of dynamic expression-forging to create this query dynamicaly. Or rework your design to somethign different.

Actualy, there is easier and normal method, that requires few steps to acomplish.

1. Make method IsMatch static.
2. Return Expression<{your entity here}, bool> directly from IsMatch.
3. Pass it like : ({your entity here}.IsMatch({parameters}))

Rest can remain same as you have now.

Edit: Example This will work with specific entity, so I will asume your entity is Order. Substitute your own entity.

public static Expression<Func<Order, bool>> IsMatch(int id, ...) // static method, that returns filtering expression
{
     return i => i.Id == id; // create the filtering criteria
}

Then call it like:

count(some_guid, Order.IsMatch(entityId, inviterId, routeId, luggageTypeId));