slavayancheg slavayancheg - 1 month ago 19
C# Question

Entity Framework 6 Stored Function in Select Statement

I have a joined query in LINQ to SQL like this:

from t in d.MainTable
join t1 in Db.JoinedTable1 on t.jid1 equals t1.Id into t11
from t12 in t11.DefaultIfEmpty()
join t2 in Db.JoinedTable2 on t.jid2 equals t2.Id into t21
from t22 in t21.DefaultIfEmpty()
select new Dto
{
Field1 = t.Field1,
Field11 = t12.Name,
Field2 = t.Field2,
Field3 = Db.Database.SqlQuery<string>("select dbo.f_MyFunction('val', 4)").FirstOrDefault()
};


but I get an error:


LINQ to Entities does not recognize the method 'System.Data.Entity.Infrastructure.DbRawSqlQuery`1[System.String] SqlQuery[String](System.String, System.Object[])' method, and this method cannot be translated into a store expression.


It is possible to use stored function in select statement? Or how can I use a class where a property mapped to stored function?

Edited. Namespaces usages in my DbContext class:

using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.SqlClient;
using System.Linq;

Answer

In this answer I found solution. I use CodeFirstStoreFunctions nuget package with FunctionsConvention.