EntityCommandCompilationException specified method not supported Entity Framework

Question

I am new to Entity Framework and I keep getting the EntityCommandCompilationException specified method not supported in Entity Framework. I can't figure out why this exception is being raised.

I have created a custom UDF aggregate function my_Func() for my installation of MySQL server 5.7 using the guidelines posted here. It works just like any ordinary aggregate function e.g. Sum() would work. i.e. I can execute the statement select my_Func(Column4) from db.table and it returns the desired result as a double. I have tested it and it works in MySQL server. I want to be able to use this method in a linq to entities query and in order to do this I have done the following.

using (var context = new dbEntities())
        {
            var results = from items in context.table
                          group items by new
                          { items.Column1, items.Column2 } into groupingItem
                          select new OutputType()
                          {
                              GroupedResult = groupingItem.OrderBy(x => x.Column3).Select(x => x.Column4).my_Func()
                          };
        }

I created a static class which contains the method.

public static class ModelDefinedFunctions
    {
        [DbFunction("dbModel.Store", "my_Func")]
        public static double my_Func(this IEnumerable<double> items)
        {
            throw new NotSupportedException("Direct calls are not supported.");
        }

    }

in the .edmx file I have added the following tag manually

<Function Name="my_Func" ReturnType="double" Aggregate="true" 
            BuiltIn="false" NiladicFunction="false" 
            IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="db">
            <Parameter Name="value" Type="Collection(double)" Mode="In" />
        </Function>

Is it resolve your problem http://stackoverflow.com/a/27029331/6733826 ? — Mathieu, Aug 24 '16 at 13:37
No it doesn't. I don't have commandtext and iscomposable is set to true in my code. — Alfred Waligo, Aug 24 '16 at 14:20
Maybe I'm a fool, but does database scheme is really "db", not "dbo" ? — Mathieu, Aug 24 '16 at 14:31
I'm open to other suggestions or workarounds that involve entity framework; — Alfred Waligo, Aug 25 '16 at 12:47
I prefer the way of Code first, fluent api,... It's been a while now that I don't use edmx any more, so I can't give you advice on it sorry — Mathieu, Aug 25 '16 at 13:05
*in the .edmx file I have added the following tag manually* - Was that really required, in DB first approach, you should make necessary changes in database and then update your edmx (right click-> update model from database), select the updated/newly added items in the pop up and click OK. Once this is done, rebuild your project / right click on .edmx file in solution explorer -> Run custom tool. This should automatically bind the function in the context. May be when you set it manually is there a chnace that something might have gone wrong? — Developer, Aug 30 '16 at 05:24

score 0 · Answer 1 · edited May 23 '17 at 12:01

Use the following code to see the SQL that Entity Framework creates and then try to run it directly on your database.

IQueryable query = from x in appEntities
         where x.id = 32
         select x;

var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();

or in EF6:

var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query)
        .ToTraceString();

(stolen from How do I view the SQL generated by the Entity Framework?)

Most likely EF is not generating the query you think it is. You might consider building the query in a StringBuilder and executing it via EF like this:

using (var db = new DbEntity())
{
    var sqlStatement = new StringBuilder();

    sqlStatement.AppendFormat("SELECT * FROM TABLENAME WHERE ID = '{0}' ", id);

    var result = db.ExecuteStoreQuery<MyTableObject>(sqlStatement.ToString()).ToList();
}

This approach is vulnerable to an injection attack. Use prepared statements with parameters instead of interpolated strings. — Jacob Brewer, Feb 09 '17 at 16:50

EntityCommandCompilationException specified method not supported Entity Framework

1 Answers1