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.