Mapping SQL Functions to Entity Framework with Constant Parameters
I have code that is highly dependent on the weeks of the ISO calendar; its persistence layer is done using Entity Framework 6.
In C #, I added an extension method for 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 this extension method to work in both IQueryable
, and map to a SQL Server feature DATEPART(iso_week, self)
.
This means that I would like to call DATEPART
with the first parameter, which is always 'iso_week'
. The second parameter must be DateTime
passed to the C # method. From what I understand, DbFunctionAttribute
only works if both parameters are function parameters.
Is it possible to do such a mapping, or do I need to create a stored procedure that invokes DATEPART(iso_week, @param)
and invokes this procedure?
source to share
I couldn't find a way to do what I wanted, so I did the following:
Create scalar value 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 EDMX (just calling update from the 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 feature with DbFunctionAttribute
.
[DbFunction("Some.Namespace", "IsoWeek")]
public static int IsoWeek(this DateTime self) {
…
}
I can now use dateTime.IsoWeek () in C # code as well as Entity-Famework-Linq-Queries.
source to share