CODEWITHSUNDEEP
c#entity-framework-6linq-to-entitiesuser-defined-functionsedmx
jstuardo
jstuardo

Reputation: 4373

Call SQL Server UDF in LINQ query

I have this UDF in SQL Server: [dbo].[ObtieneEdad]

This definition in EDMX file:

<Function Name="ObtieneEdad" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" ReturnType="int">
  <Parameter Name="fechaNacimiento" Type="datetime" Mode="In" />
</Function>

This static method in a class:

    [EdmFunction("ControlVisitas3Model.Store", "ObtieneEdad")]
    public static int? ObtieneEdad(DateTime fechaNacimiento)
    {
        throw new NotSupportedException("Direct calls are not supported.");
    }

And finally, I am trying to use in this query:

personas = personas.Where(p => !p.PersonaFechaNacimiento.HasValue ? false : DataWare.Persona.ObtieneEdad(p.PersonaFechaNacimiento.Value) >= edadMinima && DataWare.Persona.ObtieneEdad(p.PersonaFechaNacimiento.Value) <= edadMaxima);

where personas is an IQueryable.

When that query is run, this exception is thrown:

LINQ to Entities does not recognize the method 'System.Nullable`1[System.Int32] ObtieneEdad(System.DateTime)' method, and this method cannot be translated into a store expression.

What may be missing here?

I have followed instructions from this web page:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/how-to-call-custom-database-functions

Upvotes: 2

Views: 712

Answers (1)

jstuardo
jstuardo

Reputation: 4373

I have solved this way:

  • Added this method to the context class (which inherits DbContext):

    [DbFunction("ControlVisitas3Model.Store", "ObtieneEdad")]
public int ObtieneEdad(DateTime fechaNacimiento)
{
    throw new NotImplementedException();
}

  • Called it this way:

    personas = personas.Where(p => p.PersonaFechaNacimiento.HasValue && db.ObtieneEdad(p.PersonaFechaNacimiento.Value) >= edadMinima && db.ObtieneEdad(p.PersonaFechaNacimiento.Value) <= edadMaxima);

Regards

Jaime

Upvotes: 0

Related Questions