Quite a simple question, I hope: I want to use built-in MySQL functions just as YEARWEEK or INSERT in Entity Framework 6 (similar to the System.Data.Entity.DbFunctions
namespace). Is there a way to add a mapping to those functions?
I already tried to add them via the edmx file, but that didn't work quite right.
<!-- edmx:ConceptualModels -->
<Function Name="YearWeek" ReturnType="String">
<Parameter Name="date" Type="DateTime" />
<DefiningExpression>
YEARWEEK(date, 3)
</DefiningExpression>
</Function>
<!-- edmx:StorageModels -->
<Function Name="YEARWEEK" IsComposable="true" ReturnType="varchar" BuiltIn="true" Aggregate="false" NiladicFunction="false" ParameterTypeSemantics="AllowImplicitConversion">
<Parameter Name="date" Type="datetime" Mode="In" />
<Parameter Name="mode" Type="int" Mode="In" />
</Function>
And in my c# code:
[System.Data.Entity.DbFunction("otrsModel", "YearWeek")]
public static string YearWeek(DateTime date) {
throw new NotSupportedException("Direct calls are not supported.");
}
This, right now, throws me a System.Data.Entity.Core.EntityCommandCompilationException
. The inner exception is: "'YEARWEEK' cannot be resolved into a valid type or function."
However, calling the following code on that same database works just fine:
var week = db.Database.SqlQuery<dynamic>("SELECT INSERT(YEARWEEK(create_time, 3), 5, 0, '/'), ticket.* AS a FROM ticket").ToList();
Any idea what's wrong here?
I finally solved the problem, and the solution is quite simple: Adding a definition to edmx:ConceptualModels
is unnecessary. You just have to add the edmx:StorageModels
definition and call it correctly. Here's my modified code with exemplary implementations of MySQL's built-in functions INSERT and YEARWEEK:
<!-- edmx:StorageModels -->
<Function Name="YEARWEEK" IsComposable="true" ReturnType="varchar" BuiltIn="true" Aggregate="false" NiladicFunction="false" ParameterTypeSemantics="AllowImplicitConversion">
<Parameter Name="date" Type="datetime" Mode="In" />
<Parameter Name="mode" Type="int" Mode="In" />
</Function>
<Function Name="INSERT" IsComposable="true" ReturnType="varchar" BuiltIn="true" Aggregate="false" NiladicFunction="false" ParameterTypeSemantics="AllowImplicitConversion">
<Parameter Name="str" Type="varchar" Mode="In" />
<Parameter Name="position" Type="int" Mode="In" />
<Parameter Name="number" Type="int" Mode="In" />
<Parameter Name="substr" Type="varchar" Mode="In" />
</Function>
And the corresponding c# code:
namespace MySQL_3 {
class Program {
static void Main(string[] args) {
var db = new myEntities();
var test = db.ticket.Select(t => t.change_time.YearWeek(3).Insert(5, 0, "/"));
var test2 = test.ToList();
Console.Read();
}
}
public static class BuiltInFunctions {
[DbFunction("myModel.Store", "YEARWEEK")]
public static string YearWeek(this DateTime date, Int32 mode) => throw new NotSupportedException("Direct calls are not supported.");
[DbFunction("myModel.Store", "INSERT")]
public static string Insert(this string str, int position, int number, string substr) => throw new NotSupportedException("Direct calls are not supported.");
}
}