EF 6.1 select scalar function result

Question

How to select database scalar function result as column using Entity Framework 6.1 Code First? This is pure SQL query:

SELECT
Id AS Id
, Name AS Name
, dbo.GetAdditionalInfo(Id) AS AdditionalInfo
FROM Users
WHERE Status = 1

Database function dbo.GetAdditionalInfo(INT Id) collects info from other tables and returns VARCHAR result. How to do that using EF 6.1? I need something like this:

var usersList = ctx.Users
.Where(n=>n.Status == 1)
.Select(n=>new{
    Id = n.Id
    , Name = n.Name
    , AdditionalInfo = ctx.DataBase.GetScalarFunctionResult<string>("dbo.GetAdditionalInfo (@)", n.Id)
}).ToList();

Of course,

ctx.DataBase.GetScalarFunctionResult<T>(string ScalarFunctionName, params object args)

doesn't exists and that's not working. But imho it could be very useful and easy to use, if implemented.

where is this function `GetScalarFunctionResult`? I can't find it. Is it something you've made? an extension method for `Database` property? — Saeed Neamati, Mar 13 '17 at 13:25
As I mentioned, function 'GetScalarFunctionResult' doesn't exists. I used it as an example to explain what I want to do. @kiziu suggested using external library (maybe it will work), but I don't like such methods. I used native SQL query, that works fine. Hope that future versions of EF will have support of calling sql functions. — Spark13, Mar 30 '17 at 13:24
How did you solve it? Any good way? I also have a similar problem, sql like this: select id, dbo.fnDecrypt(seller, 'key123') as seller from table where dbo.fnDecrypt(seller, 'key123') like '%seller name%' — Net205, Jul 09 '18 at 17:34
@Net205 Unfortunately, I didn't find easy solution, at this moment I continue using pure sql with manual data binding in some rare cases. As I now, DB functions are supported in EF Core 2.0+, but I didn't try that yet. — Spark13, Jul 27 '18 at 06:21

Sampath · Answer 1 · 2016-09-08T11:32:28.370

1

You can try as shown below.

Namespace : using System.Data.Entity.SqlServer

 var usersList = ctx.Users
     .Where(n=>n.Status == 1)
     .Select(n=>new{
       Id = n.Id, Name = n.Name,
       AdditionalInfo = ctx.Database.SqlQuery<string>(yourQuery, parameters).FirstOrDefault() 
       }).ToList();

Reference : Execute Native SQL Query

edited Sep 08 '16 at 11:32

answered Sep 08 '16 at 09:43

Sampath

63,341
64
307
441

How will work your example? There will be the same AdditionalInfo in each object in list.I know, I can iterate through each element of List and fill AdditionalInfo by executing native SQL, but that will have performance issue. Real case is more complicated than I described in example. – Spark13 Sep 08 '16 at 10:07
Shows 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." Tried to replace calling function to simple "SELECT '1'" - doesn't work, too. – Spark13 Sep 08 '16 at 11:24
Namespace is present. It seems to me that EF doesn't support such subqueries. – Spark13 Sep 08 '16 at 11:55
did you try it without as a sub-query ? is that working then ? – Sampath Sep 08 '16 at 12:07
Yes. If I comment line "AdditionalInfo = ctx.Database.SqlQuery(yourQuery, parameters).FirstOrDefault()" all works fine – Spark13 Sep 08 '16 at 13:30
no,I mean can you run like this alone `ctx.Database.SqlQuery(yourQuery, parameters).FirstOrDefault()` ? what is the result ? is it working then ? – Sampath Sep 08 '16 at 13:32
Very sorry for delayed reply. Yes, separate query works fine. Also, I tried to replace query to simple "SELECT 'sss'" (without calling func) - result is the same: separate query (var x = ctx.Database.SqlQuery( "SELECT 'sss'").First();) works, but inside object initializer doesn't work. – Spark13 Sep 12 '16 at 07:33

score 1 · Answer 2 · edited May 23 '17 at 11:54

1

It seems, that what you want to achieve is described here Entity Framework 6 Code First Custom Functions. It will require an additional library, because it seems that EF6 does not support calling UDFs with Code-first out-of-the-box (Calling a SQL User-defined function in a LINQ query).

edited May 23 '17 at 11:54

Community

1
1

answered Sep 08 '16 at 10:07

kiziu

1,111
1
11
15

score 1 · Answer 3 · edited Aug 21 '17 at 22:18

1

You can try this (assuming the class inherits from DBContext):

String decryptedValue = String.Empty;
try {
   List<SqlParameter> parametros = new List<SqlParameter>();
   parametros.Add((new SqlParameter("@Data", value) { 
      SqlDbType = System.Data.SqlDbType.VarBinary }));
   parametros.Add((new SqlParameter("@KeyValue", key) { 
      SqlDbType = System.Data.SqlDbType.VarChar }));

   String v = null;
   v = this.Database.SqlQuery<String>(
      "SELECT SEGURIDAD.FN_Get_DecryptionData(@Data,@KeyValue)", parametros.ToArray()).FirstOrDefault();
   decryptedValue = v;
} catch(Exception ex) {

}

edited Aug 21 '17 at 22:18

DPM

1,960
3
26
49

answered Aug 21 '17 at 20:31

Alberto Molina

11
1

Thanks for reply, but question was how to use SQL function result as a column (property in returned object) in complicated LINQ query. Your example is calling pure sql and retrieving simple string result. I use similar method with pure sql, but there is no any benefits from entity framework. E.g. if I change function name, function parameters or switch to another DB type my solution compiles successfully without errors. I have to search by text and edit the text of sql query. That's acceptable, but not good. – Spark13 Aug 22 '17 at 05:10

EF 6.1 select scalar function result

3 Answers3