Calling SQL scalar function from Linq Query

Question

There are many similar threads here, but none of them can't solve my problem.

As far as I know, there are two methods for calling SQL scalar function from linq.

1. method:

I've added my function to .edmx file using XML editor and my function is:

<Function Name="prisustvoPostotci" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <CommandText>
    select dbo.prisustvoPostotci(@matica_ID,@godina)
  </CommandText>
  <Parameter Name="matica_ID" Type="int" Mode="In" />
  <Parameter Name="godina" Type="int" Mode="In" />
</Function>

I went to model browser and double clicked on my function in Function Imports and changed return collection type to Int32. My function is returning integer.

Now I can call my function from linq using:

using (DB_Entities dm = new DB_Entities())
{
  dm.prisustvoPostotci(1, 2016).FirstOrDefault();
}

It returns valid integer value!

But if I call my function from Linq Query like this:

query = query.Where(x => x.date.Value.Year == max_year &&
                dm.prisustvoPostotci(x.ID, max_year).FirstOrDefault() >= 50);

It throws this error:

LINQ to Entities does not recognize the method 'System.Data.Entity.Core.Objects.ObjectResult1[System.Nullable1[System.Int32]] prisustvoPostotci(System.Nullable1[System.Int32], System.Nullable1[System.Int32])' method, and this method cannot be translated into a store expression.

2. method:

I've added my function to .edmx file using XML editor and my function is:

<Function Name="prisustvoPostotci" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <CommandText>
    select dbo.prisustvoPostotci(@matica_ID,@godina)
  </CommandText>
  <Parameter Name="matica_ID" Type="int" Mode="In" />
  <Parameter Name="godina" Type="int" Mode="In" />
</Function>

I went to model browser and double clicked on my function in Function Imports and changed return collection type to Int32. My function is returning integer.

Then I created a partial class and wrote this method:

public static class EntityFunctions
{
    [EdmFunction("Model.Store", "prisustvoPostotci")] 
    public static int prisustvoPostotci(int matica_ID, int godina) 
    {
        throw new NotSupportedException("Direct calls not supported"); 
    } 

}

"Model.Store" is the correct name of my Model Store read from my Schema Namespace inside .edmx file.

Now, if I call my function from linq using:

EntityFunctions.prisustvoPostotci(119, 2016).ToString()

It throws this error:

throw new NotSupportedException("Direct calls not supported");

Also, if I call my function from Linq Query like this:

query = query.Where(x => x.date.Value.Year == max_year &&
                EntityFunctions.prisustvoPostotci(x.ID, max_year) >= 50);

It throws this error:

The function or function import 'Model.Store.prisustvoPostotci' is not composable. A non-composable function or function import cannot be called in a query expression.

I tried to edit my .edmx file and change a property IsComposable="true", but it gives me this error:

Functions declaring command text cannot be composed.

Can You help me solve this problem!? Many thanks in advance !!

::cheers::

Josip

Answer 1

Thanks to Gerd Arnold I realize that scalar functions cannot be used inside query where statement.

Here is how I managed to filter my query by calling scalar function outside query:

var result = query.ToList();

for (int i = 0; i < result.Count; i++)
{
    // prisustvoPostotci(ID, year) is my scalar function
    if (dm.prisustvoPostotci(result[i].ID, max_year).FirstOrDefault() >= 50)
    {
        result.Remove(result[i]);
        i--;
    }
}   

This way calling scalar function will work and we can remove matching records from the result!

Hope this will help someone.

:: cheers ::

Josip