Entity Framework 6 Stored Function in Select Statement

Question

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;

Yes, it is a SqlServer Provider. It is setted in web.config: — slavayancheg, Oct 28 '16 at 09:59

score 0 · Accepted Answer · edited May 23 '17 at 11:48

0

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

edited May 23 '17 at 11:48

Community

1
1

answered Nov 04 '16 at 15:05

slavayancheg

59
2
12

Abinash · Answer 2 · 2016-10-28T11:57:30.677

-1

Instead of SqlQuery try this CreateQuery some thing like

    Field3 =  Db.Database.CreateQuery<string>("SELECT Db.Database.f_MyFunction(@someParameter1) FROM {1}", new ObjectParameter("someParameter", someParameter)).FirstOrDefault();

edited Oct 28 '16 at 11:57

answered Oct 28 '16 at 10:54

Abinash

471
3
13

There is no method CreateQuery in Db.Database property. https://msdn.microsoft.com/ru-ru/library/system.data.entity.database(v=vs.113).aspx – slavayancheg Nov 02 '16 at 08:37
Check this https://msdn.microsoft.com/en-us/library/bb339670(v=vs.110).aspx – Abinash Nov 03 '16 at 09:14
It's didn't help me. The same error, but the method in error is CreateQuery – slavayancheg Nov 04 '16 at 14:56

Entity Framework 6 Stored Function in Select Statement

2 Answers2