Mapping SQL functions in Entity Framework with constant parameters

Question

I have code that heavily depends on ISO calendar weeks; its persistence layer is done using the Entity Framework 6.

In C#, I have added an extension method to DateTime:

// From https://stackoverflow.com/a/11155102/112964
public static int IsoWeek(this DateTime self) {
    var day = CultureInfo.InvariantCulture.Calendar.GetDayOfWeek(self);
    if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
    {
        self = self.AddDays(3);
    }

    // Return the week of our adjusted day
    return CultureInfo.InvariantCulture.Calendar.GetWeekOfYear(self, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
}

I would like for this extension method to work in IQueryables as well, and map it to the SQL Server function DATEPART(iso_week, self).

This means that I would like to call DATEPART with a first parameter that is always 'iso_week'. The second parameter should be the DateTime passed into the C# method. From what I understand, DbFunctionAttribute only works if both parameters are parameters of the function.

Is it possible to perform such a mapping or do I need to create a stored procedure that calls DATEPART(iso_week, @param) and call that procedure?

not sure I understand the part about the constant parameter. Could this help ? https://stackoverflow.com/a/29539227/1236044 — jbl, Jul 04 '17 at 12:59
@jbl The answer you linked is how to use the `DbFunctionAttribute` with a function where all arguments are passed into SQL. I don't see that it added a fixed parameter to the SQL function like I want to. — nd., Jul 05 '17 at 07:29

score 0 · Accepted Answer · answered Jul 07 '17 at 12:27

I could not find a way to do what I wanted, so I did the following:

Create a scalar valued function in SQL server

CREATE FUNCTION IsoWeek(@date DATETIME)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
  RETURN datepart(ISO_WEEK, @date);
END

Import the function into the EDMX (simply by calling Update from Database)

<Schema Namespace="Some.Namespace" …>
… 
    <Function Name="IsoWeek" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" ReturnType="int">
      <Parameter Name="date" Type="datetime" Mode="In" />
    </Function>

Update the IsoWeek extension function with a DbFunctionAttribute.

[DbFunction("Some.Namespace", "IsoWeek")]
public static int IsoWeek(this DateTime self) {
    …
}

Now I can use dateTime.IsoWeek() in C# code as well as in Entity-Famework-Linq-Queries.

Mapping SQL functions in Entity Framework with constant parameters

1 Answers1