When using an entity with property of a custom type, the type cannot be translated into SQL.
I have created an example to explain my approach to solve it:
A class takes place in a certain semester. The semester is stored as a DateTime
value in the database.
The semester itself is a custom type, with additional properties.
public class Semester
{
public enum HalfYear
{
First = 1,
Second = 7
}
DateTime _dateTime;
public Semester (HalfYear halfYear, int year)
{
_dateTime = new DateTime(year, (int) halfYear, 1)
}
public int Year => _dateTime.Year;
public HalfYear HalfYear => (HalfYear) _dateTime.Month;
public DateTime FirstDay => new DateTime(Year, _dateTime.Month, 1);
public DateTime LastDay => new DateTime(Year, _dateTime.Month + 5, DateTime.DaysInMonth(Year, _dateTime.Month + 5));
}
public class Class
{
int Id { get; set; }
string Title { get; set; }
Semester Semester { get; set; }
}
The Semester
type can be mapped to a DateTime
using value converters.
This does not work in Where
clause such as
db.Classes.Where(c = c.Semester.FirstDay <= DateTime.Now &&
c.Semester.LastDay >= DateTime.Now)
When Entity Framework Core tries to translate the expression tree to SQL, it does not know how to translate Semester.FirstDay
or Semester.LastDay
.
This is a known limitation of value conversions as the documentation states
Use of value conversions may impact the ability of EF Core to translate expressions to SQL. A warning will be logged for such cases. Removal of these limitations is being considered for a future release.
How to solve this issue?
EntityFrameworkCore has 3 extension points that can be used to translate custom types to SQL.
These translators and mapppings can be registered using the corresponding plugins:
The plugins are registered with a IDbContextOptionsExtension
The following example illustrates how I have implemented these interfaces to register the custom type Semester:
IMemberTranslator
public class SqlServerSemesterMemberTranslator : IMemberTranslator
{
public Expression Translate(MemberExpression memberExpression)
{
if (memberExpression.Member.DeclaringType != typeof(Semester)) {
return null;
}
var memberName = memberExpression.Member.Name;
if (memberName == nameof(Semester.FirstDay)) {
return new SqlFunctionExpression(
"DATEFROMPARTS",
typeof(DateTime),
new Expression[] {
new SqlFunctionExpression( "YEAR", typeof(int),new[] { memberExpression.Expression }),
new SqlFunctionExpression( "MONTH", typeof(int),new[] { memberExpression.Expression }),
Expression.Constant(1, typeof(int))
});
}
if (memberName == nameof(Semester.LastDay)) {
return new SqlFunctionExpression(
"EOMONTH",
typeof(DateTime),
new Expression[] {
memberExpression.Expression
});
}
if (memberName == nameof(Semester.HalfYear)) {
return Expression.Convert(
new SqlFunctionExpression(
"MONTH",
typeof(int),
new Expression[] {
memberExpression.Expression
}),
typeof(HalfYear));
}
if (memberName == nameof(Semester.Year)) {
return new SqlFunctionExpression(
"YEAR",
typeof(int),
new Expression[] {
memberExpression.Expression
});
}
return null;
}
}
IMethodCallTranslator
public class SqlServerSemesterMethodCallTranslator : IMethodCallTranslator
{
public Expression Translate(MethodCallExpression methodCallExpression)
{
if (methodCallExpression.Method.DeclaringType != typeof(Period)) {
return null;
}
var methodName = methodCallExpression.Method.Name;
// Implement your Method translations here
return null;
}
}
RelationalTypeMapping
public class SqlServerSemesterTypeMapping : DateTimeTypeMapping
{
public SqlServerSemesterTypeMapping(string storeType, DbType? dbType = null) :
base(storeType, dbType)
{
}
protected SqlServerSemesterTypeMapping(RelationalTypeMappingParameters parameters) : base(parameters)
{
}
protected override RelationalTypeMapping Clone(RelationalTypeMappingParameters parameters) => new SqlServerSemesterTypeMapping(parameters);
}
IMemberTranslatorPlugin
public class SqlServerCustomMemberTranslatorPlugin : IMemberTranslatorPlugin
{
public IEnumerable<IMemberTranslator> Translators => new IMemberTranslator[] { new SqlServerSemesterMemberTranslator() };
}
public class SqlServerCustomMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin
{
public IEnumerable<IMethodCallTranslator> Translators => new IMethodCallTranslator[] { new SqlServerSemesterMethodCallTranslator() };
}
IRelationalTypeMappingSourcePlugin
public class SqlServerCustomTypeMappingSourcePlugin : IRelationalTypeMappingSourcePlugin
{
public RelationalTypeMapping FindMapping(in RelationalTypeMappingInfo mappingInfo)
=> mappingInfo.ClrType == typeof(Semester) || (mappingInfo.StoreTypeName == nameof(DateTime))
? new SqlServerSemesterTypeMapping(mappingInfo.StoreTypeName ?? "datetime")
: null;
}
After you have defined and registered the translators, you have to confgure them in the DbContext.
IDbContextOptionsExtension
public class SqlServerCustomTypeOptionsExtension : IDbContextOptionsExtensionWithDebugInfo
{
public string LogFragment => "using CustomTypes";
public bool ApplyServices(IServiceCollection services)
{
services.AddEntityFrameworkSqlServerCustomTypes();
return false;
}
public long GetServiceProviderHashCode() => 0;
public void PopulateDebugInfo(IDictionary<string, string> debugInfo)
=> debugInfo["SqlServer:" + nameof(SqlServerCustomDbContextOptionsBuilderExtensions.UseCustomTypes)] = "1";
public void Validate(IDbContextOptions options)
{
}
}
Extension Methods
public static class SqlServerCustomDbContextOptionsBuilderExtensions
{
public static object UseCustomTypes(this SqlServerDbContextOptionsBuilder optionsBuilder)
{
if (optionsBuilder == null) throw new ArgumentNullException(nameof(optionsBuilder));
// Registere die SqlServerDiamantOptionsExtension.
var coreOptionsBuilder = ((IRelationalDbContextOptionsBuilderInfrastructure)optionsBuilder).OptionsBuilder;
var extension = coreOptionsBuilder.Options.FindExtension<SqlServerCustomTypeOptionsExtension>()
?? new SqlServerCustomTypeOptionsExtension();
((IDbContextOptionsBuilderInfrastructure)coreOptionsBuilder).AddOrUpdateExtension(extension);
// Configure Warnings
coreOptionsBuilder
.ConfigureWarnings(warnings => warnings
.Log(RelationalEventId.QueryClientEvaluationWarning) // Should be thrown to prevent only warnings if a query is not fully evaluated on the db
.Ignore(RelationalEventId.ValueConversionSqlLiteralWarning)); // Ignore warnings for types that are using a ValueConverter
return optionsBuilder;
}
}
public static class SqlServerServiceCollectionExtensions
{
public static IServiceCollection AddEntityFrameworkSqlServerCustomTypes(
this IServiceCollection serviceCollection)
{
if (serviceCollection == null) throw new ArgumentNullException(nameof(serviceCollection));
new EntityFrameworkRelationalServicesBuilder(serviceCollection)
.TryAddProviderSpecificServices(
x => x.TryAddSingletonEnumerable<IRelationalTypeMappingSourcePlugin, SqlServerCustomTypeMappingSourcePlugin>()
.TryAddSingletonEnumerable<IMemberTranslatorPlugin, SqlServerCustomTypeMemberTranslatorPlugin>()
.TryAddSingletonEnumerable<IMethodCallTranslatorPlugin, SqlServerCustomTypeMethodCallTranslatorPlugin>());
return serviceCollection;
}
}
Register the option in the DbContext
dbOptionsBuilder.UseSqlServer(connectionString, builder => builder.UseCustomTypes())