protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// IsDate is a built-in SQL Server function, that in the base class is mapped as built-in, which means we
// don't get any quotes. We remap it as non-built-in by including a (null) schema.
var isDateMethodInfo = typeof(UDFSqlContext).GetMethod(nameof(IsDateStatic));
modelBuilder.HasDbFunction(isDateMethodInfo)
.HasTranslation(args => SqlFunctionExpression.Create((string)null, "IsDate", args, isDateMethodInfo.ReturnType, null));
var isDateMethodInfo2 = typeof(UDFSqlContext).GetMethod(nameof(IsDateInstance));
modelBuilder.HasDbFunction(isDateMethodInfo2)
.HasTranslation(args => SqlFunctionExpression.Create((string)null, "IsDate", args, isDateMethodInfo2.ReturnType, null));
// Base class maps to len(), but in PostgreSQL it's called length()
var methodInfo = typeof(UDFSqlContext).GetMethod(nameof(MyCustomLengthStatic));
modelBuilder.HasDbFunction(methodInfo)
.HasTranslation(args => SqlFunctionExpression.Create("length", args, methodInfo.ReturnType, null));
var methodInfo2 = typeof(UDFSqlContext).GetMethod(nameof(MyCustomLengthInstance));
modelBuilder.HasDbFunction(methodInfo2)
.HasTranslation(args => SqlFunctionExpression.Create("length", args, methodInfo2.ReturnType, null));
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity <TableA>();
modelBuilder.HasDbFunction(typeof(TestDataBaseContext).GetMethod(nameof(JsonValue)))
.HasTranslation(args => SqlFunctionExpression.Create("JSON_VALUE", args, typeof(string), null));
}
public static SqlFunctionExpression Function <TRet>(string schema, string name, IEnumerable <SqlExpression> arguments)
{
var returnType = typeof(TRet);
var typeMapping = GetTypeMapping(returnType);
return(SqlFunctionExpression.Create(schema, name, arguments, returnType, typeMapping));
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//TODO: Map Composite Keys Entities Here
//TODO: Set the .HasNoKey() to views and stored procedures
//This lines map a DB funcion so we can call it inside the linq or lambda function that executes a query to the DB
modelBuilder.HasDbFunction(typeof(SampleDataContextFunctions).GetMethod(nameof(SampleDataContextFunctions.CalcuateAgeInYearsMonths)))
.HasTranslation(args => SqlFunctionExpression.Create("dbo",
"f_CalcuateAgeInYearsMonths",
args,
typeof(decimal?),
null));
//Disable the cascade delete behavior
var cascadeFKs = modelBuilder.Model.GetEntityTypes().SelectMany(t => t.GetForeignKeys())
.Where(fk => !fk.IsOwnership && fk.DeleteBehavior == DeleteBehavior.Cascade);
foreach (var fk in cascadeFKs)
{
fk.DeleteBehavior = DeleteBehavior.Restrict;
}
modelBuilder.Ignore <ExtensionDataObject>();
base.OnModelCreating(modelBuilder);
}
//public DbSet<DbUnit> Units { get; set; }
//public DbSet<DbRefinery> Refineries { get; set; }
//public DbSet<DbUpgrade> Upgrades { get; set; }
/*
* protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
* {
* if (String.IsNullOrEmpty(DSdata.ServerConfig.DBConnectionString))
* throw new NotSupportedException();
*
* optionsBuilder
* //.UseSqlServer(sc2dsstatslib.Config.DBConnectionString)
* .UseMySql(DSdata.ServerConfig.DBConnectionString, mySqlOptions => mySqlOptions
* .ServerVersion(new ServerVersion(new Version(5, 7, 29), ServerType.MySql)))
* //.ServerVersion(new ServerVersion(new Version(8, 0, 17), ServerType.MySql)))
* //.UseLoggerFactory(_loggerFactory)
* ;
* }
*/
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder
.HasDbFunction(typeof(DBFunctions).GetMethod(nameof(DBFunctions.GetOpp)))
.HasTranslation(args => SqlFunctionExpression.Create("GetOpp", args, typeof(int), null));
modelBuilder
.HasDbFunction(typeof(DBFunctions).GetMethod(nameof(DBFunctions.GetPl)))
.HasTranslation(args => SqlFunctionExpression.Create("GetPl", args, typeof(int), null));
modelBuilder.Entity <DSReplay>(entity =>
{
entity.HasKey(e => e.ID);
entity.Property(b => b.HASH)
.HasMaxLength(32)
.IsFixedLength();
entity.HasIndex(b => b.HASH);
// .IsUnique();
entity.HasIndex(b => b.REPLAY);
});
modelBuilder.Entity <DbMiddle>(entity =>
{
entity.HasKey(e => e.ID);
entity.HasOne(p => p.Replay)
.WithMany(d => d.Middle);
});
modelBuilder.Entity <DSPlayer>(entity =>
{
entity.HasKey(e => e.ID);
entity.Property(p => p.NAME)
.HasMaxLength(64);
entity.Property(p => p.RACE)
.HasMaxLength(64);
entity.Property(p => p.OPPRACE)
.HasMaxLength(64);
entity.HasOne(d => d.DSReplay)
.WithMany(p => p.DSPlayer);
entity.HasIndex(b => b.RACE);
entity.HasIndex(p => new { p.RACE, p.OPPRACE });
});
modelBuilder.Entity <DbBreakpoint>(entity =>
{
entity.HasKey(p => p.ID);
entity.HasOne(p => p.Player)
.WithMany(d => d.Breakpoints);
});
modelBuilder.Entity <DSUnit>(entity =>
{
entity.HasKey(k => k.ID);
entity.HasOne(p => p.DSPlayer)
.WithMany(d => d.DSUnit);
});
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
foreach (var relacao in modelBuilder.Model.GetEntityTypes().SelectMany(ent => ent.GetForeignKeys()))
{
relacao.DeleteBehavior = DeleteBehavior.Restrict;
}
modelBuilder.HasDbFunction(typeof(Context).GetMethod(nameof(SoundsLike)))
.HasTranslation(args => SqlFunctionExpression.Create("SOUNDEX", args, typeof(string), null));
}
/// <summary>
/// Applies configuration so that we can user <see cref="JsonFunctions.JsonValue(string, string)"/>
/// </summary>
/// <param name="modelBuilder"></param>
/// <returns></returns>
public static DbFunctionBuilder ApplyJsonValue(this ModelBuilder modelBuilder)
{
if (modelBuilder is null)
{
throw new ArgumentNullException(nameof(modelBuilder));
}
return(modelBuilder.HasDbFunction(typeof(JsonFunctions).GetMethod(nameof(JsonFunctions.JsonValue)))
.HasTranslation(args => {
return SqlFunctionExpression.Create("JSON_VALUE", args, typeof(string), null);
})
.HasSchema(string.Empty));
}
protected override void OnModelCreating(ModelBuilder modelBuilder, DbContext context)
{
base.OnModelCreating(modelBuilder, context);
modelBuilder.HasDbFunction(
typeof(GeoExtensions).GetMethod(nameof(GeoExtensions.Distance)),
b => b.HasTranslation(
e => SqlFunctionExpression.Create(
"Distance",
e,
typeof(double),
null)));
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfiguration(new DoctorConfiguration());
modelBuilder.HasDbFunction(FunctionsSQLServer.FSGetExpertiseMethodInfo())
.HasTranslation((args) =>
{
var arguments = args.ToList();
arguments[1] = new
SqlFragmentExpression(
(string)((SqlConstantExpression)arguments.Last()).Value
);
return(SqlFunctionExpression.Create("dbo.FSGetExpertise", arguments, typeof(int), null));
});
modelBuilder.HasDbFunction(FunctionsSQLServer.CharIndexToMethodInfo())
.HasTranslation((args) =>
{
var arguments = args.ToList();
arguments[1] = new
SqlFragmentExpression(
(string)((SqlConstantExpression)arguments.Last()).Value
);
return(SqlFunctionExpression.Create("CHARINDEX", arguments, typeof(int), null));
});
# region FunctionSQLServer
//SET ANSI_NULLS ON
//GO
//SET QUOTED_IDENTIFIER ON
//GO
//-- =============================================
//--Author: < Fúlvio Cezar Canducci Dias >
//--Create date: < 04 / 09 / 2020 >
//--Description: < Expertise >
//-- =============================================
//ALTER FUNCTION dbo.FSGetExpertise
//(
// @Value nvarchar(450),
// @Field nvarchar(450)
//)
//RETURNS INT
//AS
//BEGIN
// RETURN CHARINDEX(@Value, @Field COLLATE Latin1_General_CI_AS);
//END
//GO
#endregion
}
/// <summary>
/// Applies configuration so that we can user <see cref="JsonFunctions.CastToDouble(string)"/>
/// </summary>
/// <param name="modelBuilder"></param>
/// <returns></returns>
public static DbFunctionBuilder ApplyJsonCastToDouble(this ModelBuilder modelBuilder)
{
if (modelBuilder is null)
{
throw new ArgumentNullException(nameof(modelBuilder));
}
return(modelBuilder.HasDbFunction(typeof(JsonFunctions).GetMethod(nameof(JsonFunctions.CastToDouble)))
.HasTranslation(args => {
var float32 = new SqlFragmentExpression("float");
var convertArgs = new SqlExpression[] { float32 }.Concat(args);
return SqlFunctionExpression.Create("Convert", convertArgs, typeof(double?), null);
})
.HasSchema(string.Empty));
}
public static void ConfigureDbFunctions(this ModelBuilder modelBuilder)
{
var mi = typeof(DbFunctionExtensions).GetMethod(nameof(DatePart));
modelBuilder.HasDbFunction(mi, b => b.HasTranslation(e =>
{
var ea = e.ToArray();
var args = new[]
{
new SqlFragmentExpression((ea[0] as SqlConstantExpression).Value.ToString()),
ea[1]
};
return(SqlFunctionExpression.Create(nameof(DatePart), args, typeof(int?), null));
}));
}
protected override Expression VisitSqlFunction(SqlFunctionExpression sqlFunctionExpression)
{
if (!sqlFunctionExpression.IsBuiltIn &&
string.IsNullOrEmpty(sqlFunctionExpression.Schema))
{
sqlFunctionExpression = SqlFunctionExpression.Create(
schema: "dbo",
sqlFunctionExpression.Name,
sqlFunctionExpression.Arguments,
sqlFunctionExpression.Type,
sqlFunctionExpression.TypeMapping);
}
return(base.VisitSqlFunction(sqlFunctionExpression));
}
protected override void OnModelCreating(ModelBuilder modelBuilder, DbContext context)
{
base.OnModelCreating(modelBuilder, context);
modelBuilder.HasDbFunction(
typeof(GeoExtensions).GetMethod(nameof(GeoExtensions.Distance)),
b => b.HasTranslation(
e => SqlFunctionExpression.Create(
"Distance",
arguments: e,
nullable: true,
argumentsPropagateNullability: e.Select(a => true).ToList(),
typeof(double),
null)));
}
public static void ConfigureDbFunctions(this ModelBuilder builder)
{
var mi = typeof(DateTimeExtensions).GetMethod(nameof(DateTimeExtensions.ToCustomString));
builder.HasDbFunction(mi, b => b.HasTranslation(e =>
{
var args = new[]
{
e.First(),
new SqlFragmentExpression("'d MMM yyyy'"),
new SqlFragmentExpression("'iv'")
};
return(SqlFunctionExpression.Create("FORMAT", args, typeof(string), null));
}));
}
/// <summary>
/// Applies configuration so that we can user <see cref="JsonFunctions.JsonValue(string, string)"/>
/// </summary>
/// <param name="modelBuilder"></param>
/// <returns></returns>
public static DbFunctionBuilder ApplyJsonValue(this ModelBuilder modelBuilder)
{
if (modelBuilder is null)
{
throw new ArgumentNullException(nameof(modelBuilder));
}
return(modelBuilder.HasDbFunction(typeof(JsonFunctions).GetMethod(nameof(JsonFunctions.JsonValue)))
.HasTranslation(args => {
#if NET5_0_OR_GREATER
return new SqlFunctionExpression("JSON_VALUE", args, nullable: true, argumentsPropagateNullability: args.Select(x => true), typeof(string), null);
#else
return SqlFunctionExpression.Create("JSON_VALUE", args, typeof(string), null);
#endif
})
.HasSchema(string.Empty));
}
protected override void OnModelCreating(ModelBuilder modelBuilder, DbContext context)
{
base.OnModelCreating(modelBuilder, context);
modelBuilder.HasDbFunction(
typeof(GeoExtensions).GetMethod(nameof(GeoExtensions.Distance)),
b => b.HasTranslation(
e => SqlFunctionExpression.Create(
instance: e.First(),
"STDistance",
arguments: e.Skip(1),
nullResultAllowed: true,
instancPropagatesNullability: true,
argumentsPropagateNullability: e.Skip(1).Select(a => true),
typeof(double),
null)));
}
private static void registerEndsWithAI(ModelBuilder modelBuilder)
{
modelBuilder.HasDbFunction(typeof(StringFuncs).GetMethod(nameof(StringFuncs.EndsWithAI)))
.HasTranslation(e => new LikeExpression(
new CollateExpression(e.First(), "latin1_general_ci_ai"),
new CollateExpression(
SqlFunctionExpression.Create("CONCAT",
new SqlExpression[] {
new SqlFragmentExpression("'%'"),
e.Skip(1).First()
}, typeof(string), null),
"latin1_general_ci_ai"),
new SqlFragmentExpression("'\\'"),
null
)
);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//Static
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(CustomerOrderCountStatic))).HasName("CustomerOrderCount");
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(CustomerOrderCountWithClientStatic)))
.HasName("CustomerOrderCount");
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(StarValueStatic))).HasName("StarValue");
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(IsTopCustomerStatic))).HasName("IsTopCustomer");
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(GetCustomerWithMostOrdersAfterDateStatic)))
.HasName("GetCustomerWithMostOrdersAfterDate");
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(GetReportingPeriodStartDateStatic)))
.HasName("GetReportingPeriodStartDate");
var isDateMethodInfo = typeof(UDFSqlContext).GetMethod(nameof(IsDateStatic));
modelBuilder.HasDbFunction(isDateMethodInfo)
.HasTranslation(args => SqlFunctionExpression.Create("IsDate", args, isDateMethodInfo.ReturnType, null));
var methodInfo = typeof(UDFSqlContext).GetMethod(nameof(MyCustomLengthStatic));
modelBuilder.HasDbFunction(methodInfo)
.HasTranslation(args => SqlFunctionExpression.Create("len", args, methodInfo.ReturnType, null));
//Instance
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(CustomerOrderCountInstance)))
.HasName("CustomerOrderCount");
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(CustomerOrderCountWithClientInstance)))
.HasName("CustomerOrderCount");
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(StarValueInstance))).HasName("StarValue");
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(IsTopCustomerInstance))).HasName("IsTopCustomer");
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(GetCustomerWithMostOrdersAfterDateInstance)))
.HasName("GetCustomerWithMostOrdersAfterDate");
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(GetReportingPeriodStartDateInstance)))
.HasName("GetReportingPeriodStartDate");
var isDateMethodInfo2 = typeof(UDFSqlContext).GetMethod(nameof(IsDateInstance));
modelBuilder.HasDbFunction(isDateMethodInfo2)
.HasTranslation(args => SqlFunctionExpression.Create("IsDate", args, isDateMethodInfo2.ReturnType, null));
modelBuilder.HasDbFunction(typeof(UDFSqlContext).GetMethod(nameof(DollarValueInstance))).HasName("DollarValue");
var methodInfo2 = typeof(UDFSqlContext).GetMethod(nameof(MyCustomLengthInstance));
modelBuilder.HasDbFunction(methodInfo2)
.HasTranslation(args => SqlFunctionExpression.Create("len", args, methodInfo2.ReturnType, null));
}
/// <summary>
/// Applies configuration so that we can user <see cref="JsonFunctions.CastToDouble(string)"/>
/// </summary>
/// <param name="modelBuilder"></param>
/// <returns></returns>
public static DbFunctionBuilder ApplyJsonCastToDouble(this ModelBuilder modelBuilder)
{
if (modelBuilder is null)
{
throw new ArgumentNullException(nameof(modelBuilder));
}
return(modelBuilder.HasDbFunction(typeof(JsonFunctions).GetMethod(nameof(JsonFunctions.CastToDouble)))
.HasTranslation(args => {
var float32 = new SqlFragmentExpression("float");
var convertArgs = new SqlExpression[] { float32 }.Concat(args);
#if NET5_0_OR_GREATER
return new SqlFunctionExpression("Convert", convertArgs, nullable: true, argumentsPropagateNullability: convertArgs.Select(x => true), typeof(double?), null);
#else
return SqlFunctionExpression.Create("Convert", convertArgs, typeof(double?), null);
#endif
})
.HasSchema(string.Empty));
}
/// <summary>
/// This is an internal API that supports the Entity Framework Core infrastructure and not subject to
/// the same compatibility standards as public APIs. It may be changed or removed without notice in
/// any release. You should only use it directly in your code with extreme caution and knowing that
/// doing so can result in application failures when updating to a new Entity Framework Core release.
/// </summary>
protected override Expression VisitSqlFunction(SqlFunctionExpression sqlFunctionExpression)
{
Check.NotNull(sqlFunctionExpression, nameof(sqlFunctionExpression));
if (!sqlFunctionExpression.IsBuiltIn &&
string.IsNullOrEmpty(sqlFunctionExpression.Schema))
{
sqlFunctionExpression = SqlFunctionExpression.Create(
schema: "dbo",
sqlFunctionExpression.Name,
sqlFunctionExpression.Arguments,
sqlFunctionExpression.IsNullable,
sqlFunctionExpression.ArgumentsPropagateNullability,
sqlFunctionExpression.Type,
sqlFunctionExpression.TypeMapping);
}
return(base.VisitSqlFunction(sqlFunctionExpression));
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder
.HasDbFunction(typeof(Funcoes)
.GetMethod(nameof(Funcoes.ContainsIn)))
.HasTranslation(args =>
{
var arguments = args.ToList();
var a = (string)((SqlConstantExpression)arguments[0]).Value;
arguments[0] = new SqlFragmentExpression(a);
return(SqlFunctionExpression.Create("CONTAINS", arguments, typeof(bool), null));
});
modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
}
public static void Register(ModelBuilder modelBuider)
{
var convertMethodInfo = GetMethodInfoForConvert();
modelBuider
.HasDbFunction(convertMethodInfo)
.HasTranslation(args =>
{
var arguments = args.ToArray();
var sqlTypeName = (string)((SqlConstantExpression)arguments[1]).Value;
return(SqlFunctionExpression.Create(
convertMethodInfo.Name,
new[]
{
new SqlFragmentExpression($"{sqlTypeName}(max)"),
arguments[0],
arguments[2]
},
convertMethodInfo.ReturnType,
new StringTypeMapping(sqlTypeName, DbType.String)));
});
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Since the core implementation does not support weekDay or hour, we have teo bind a method to
// the sql function directly on model creation. doing so allows us to perfrom a week day
// query dirctly on the database instead of loading the entire data locally first.
MethodInfo methodInfoWeekDay = typeof(DatabaseContext).GetRuntimeMethod(nameof(WeekDay), new[] { typeof(DateTimeOffset) });
modelBuilder.HasDbFunction(methodInfoWeekDay, b => b.HasTranslation(e =>
{
SqlExpression[] arguments = new[] { e.ElementAt(0) };
return(SqlFunctionExpression.Create(nameof(WeekDay), arguments, typeof(int?), null));
}));
MethodInfo methodInfoHour = typeof(DatabaseContext).GetRuntimeMethod(nameof(Hour), new[] { typeof(DateTimeOffset) });
modelBuilder.HasDbFunction(methodInfoHour, b => b.HasTranslation(e =>
{
SqlExpression[] arguments = new[] { e.ElementAt(0) };
return(SqlFunctionExpression.Create(nameof(Hour), arguments, typeof(int?), null));
}));
SeedData.PopulateTestData(modelBuilder);
}
public static void AddDbFunction(this ModelBuilder modelBuilder)
{
modelBuilder
.HasDbFunction(typeof(JsonExtensions).GetMethod(nameof(JsonExtensions.JsonValue)))
.HasTranslation(e =>
{
var res = new List <SqlExpression>();
if (e is List <SqlExpression> list)
{
if (list[0] is SqlConstantExpression key)
{
res.Add(new SqlFragmentExpression($"`{key.Value}`"));
}
if (list[1] is SqlConstantExpression val)
{
res.Add(new SqlConstantExpression(Expression.Constant($"$.{val.Value}"), val.TypeMapping));
}
}
return(SqlFunctionExpression.Create("JSON_EXTRACT", res, typeof(string), null));
});
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity <Jogador>(entity =>
{
entity.Property(e => e.Foto).HasColumnType("image");
entity.Property(e => e.Informacoes)
.IsRequired()
.HasColumnType("text");
entity.Property(e => e.Nascimento).HasColumnType("datetime");
entity.Property(e => e.Nome)
.IsRequired()
.HasMaxLength(255)
.IsUnicode(false);
entity.Property(e => e.Posicao)
.IsRequired()
.HasMaxLength(255)
.IsUnicode(false);
entity.Property(e => e.QtdecartoesAmarelo).HasColumnName("QTDECartoesAmarelo");
entity.Property(e => e.QtdecartoesVermelho).HasColumnName("QTDECartoesVermelho");
entity.Property(e => e.Qtdefaltas).HasColumnName("QTDEFaltas");
entity.Property(e => e.Qtdegols).HasColumnName("QTDEGols");
entity.Property(e => e.SelecaoId).HasColumnName("SelecaoID");
entity.HasOne(d => d.Selecao)
.WithMany(p => p.Jogador)
.HasForeignKey(d => d.SelecaoId)
.HasConstraintName("FK__Jogador__Selecao__2A4B4B5E");
});
modelBuilder.Entity <Jogo>(entity =>
{
entity.Property(e => e.Data).HasColumnType("datetime");
entity.Property(e => e.Estadio)
.IsRequired()
.HasMaxLength(255)
.IsUnicode(false);
entity.HasOne(d => d.SelecaoCasaNavigation)
.WithMany(p => p.JogoSelecaoCasaNavigation)
.HasForeignKey(d => d.SelecaoCasa)
.HasConstraintName("FK__Jogo__SelecaoCas__2D27B809");
entity.HasOne(d => d.SelecaoVisitanteNavigation)
.WithMany(p => p.JogoSelecaoVisitanteNavigation)
.HasForeignKey(d => d.SelecaoVisitante)
.HasConstraintName("FK__Jogo__SelecaoVis__2E1BDC42");
});
modelBuilder.Entity <Selecao>(entity =>
{
entity.Property(e => e.Bandeira).HasColumnType("image");
entity.Property(e => e.Escalacao)
.HasMaxLength(10)
.IsUnicode(false);
entity.Property(e => e.Nome)
.IsRequired()
.HasMaxLength(255)
.IsUnicode(false);
entity.Property(e => e.Uniforme).HasColumnType("image");
});
modelBuilder.Entity <Usuario>(entity =>
{
entity.HasIndex(e => e.Apelido)
.HasName("UQ__Usuario__571DBAE602818710")
.IsUnique();
entity.HasIndex(e => e.Email)
.HasName("UQ__Usuario__A9D10534C254DE1E")
.IsUnique();
entity.Property(e => e.Apelido)
.IsRequired()
.HasMaxLength(255)
.IsUnicode(false);
entity.Property(e => e.Email)
.IsRequired()
.HasMaxLength(255)
.IsUnicode(false);
entity.Property(e => e.Foto).HasColumnType("image");
entity.Property(e => e.Nome)
.IsRequired()
.HasMaxLength(255)
.IsUnicode(false);
entity.Property(e => e.Senha)
.IsRequired()
.HasMaxLength(255)
.IsUnicode(false);
});
OnModelCreatingPartial(modelBuilder);
modelBuilder.HasDbFunction(typeof(WebApiBDContext).GetMethod(nameof(SoundsLike)))
.HasTranslation(args => SqlFunctionExpression.Create("SOUNDEX", args, typeof(string), null));
}
public static void AddCustomSqlFunctions(this ModelBuilder modelBuilder)
{
modelBuilder.HasDbFunction(_sqlReplicateMethodInfo)
.HasTranslation(args =>
{
return(SqlFunctionExpression.Create("REPLICATE",
args,
_sqlReplicateMethodInfo.ReturnType,
typeMapping: null));
});
modelBuilder.HasDbFunction(_sqlRoundMethodInfo)
.HasTranslation(args =>
{
return(SqlFunctionExpression.Create("ROUND",
args,
_sqlRoundMethodInfo.ReturnType,
typeMapping: null));
});
modelBuilder.HasDbFunction(_sqlDayMethodInfo)
.HasTranslation(args => SqlFunctionExpression.Create(
"DAY",
args,
_sqlDayMethodInfo.ReturnType,
typeMapping: null));
modelBuilder.HasDbFunction(_sqlMonthMethodInfo)
.HasTranslation(args => SqlFunctionExpression.Create(
"MONTH",
args,
_sqlMonthMethodInfo.ReturnType,
typeMapping: null));
modelBuilder.HasDbFunction(_sqlYearMethodInfo)
.HasTranslation(args => SqlFunctionExpression.Create(
"YEAR",
args,
_sqlYearMethodInfo.ReturnType,
typeMapping: null));
modelBuilder.HasDbFunction(_sqlGetDateMethodInfo)
.HasTranslation(args => SqlFunctionExpression.Create(
"GETDATE",
args,
_sqlGetDateMethodInfo.ReturnType,
typeMapping: null));
modelBuilder.HasDbFunction(_sqlShortDateMethodInfo)
.HasTranslation(args => SqlFunctionExpression.Create(
"DBO.SHORTDATE",
args,
_sqlShortDateMethodInfo.ReturnType,
typeMapping: null));
modelBuilder.HasDbFunction(_sqlDateDiffMethodInfo)
.HasTranslation(args =>
{
var parameters = args.ToArray();
var param0 = ((SqlConstantExpression)parameters[0]).Value.ToString();
return(SqlFunctionExpression.Create("DATEDIFF",
new[]
{
new SqlFragmentExpression(param0), // It should be written as DateDiff(day, ...) and not DateDiff(N'day', ...) .
parameters[1],
parameters[2]
},
_sqlDateDiffMethodInfo.ReturnType,
typeMapping: null));
});
modelBuilder.HasDbFunction(_sqlDatePartMethodInfo)
.HasTranslation(args =>
{
var parameters = args.ToArray();
var param0 = ((SqlConstantExpression)parameters[0]).Value.ToString();
return(SqlFunctionExpression.Create("DATEPART",
new[]
{
new SqlFragmentExpression(param0), // It should be written as DATEPART(day, ...) and not DATEPART(N'day', ...) .
parameters[1]
},
_sqlDatePartMethodInfo.ReturnType,
typeMapping: null));
});
}
public static void ConfigureDynamicEntity(
this ModelBuilder builder,
Action <DynamicEntityModelBuilderConfigurationOptions> optionsAction = null)
{
Check.NotNull(builder, nameof(builder));
var options = new DynamicEntityModelBuilderConfigurationOptions(
DynamicEntityDbProperties.DbTablePrefix,
DynamicEntityDbProperties.DbSchema
);
optionsAction?.Invoke(options);
builder.Entity <FieldDefinition>(b =>
{
b.ToTable(options.TablePrefix + "FieldDefinitions", options.Schema);
b.ConfigureByConvention();
b.Property(x => x.Name).IsRequired().HasMaxLength(FieldDefinitionConsts.MaxNameLength);
b.Property(x => x.DisplayName).IsRequired().HasMaxLength(FieldDefinitionConsts.MaxDisplayNameLength);
b.Property(x => x.Type).IsRequired().HasMaxLength(FieldDefinitionConsts.MaxTypeLength);
b.HasIndex(x => x.Name);
});
builder.Entity <ModelDefinition>(b =>
{
b.ToTable(options.TablePrefix + "ModelDefinitions", options.Schema);
b.ConfigureByConvention();
b.Property(x => x.Name).IsRequired().HasMaxLength(ModelDefinitionConsts.MaxNameLength);
b.Property(x => x.DisplayName).IsRequired().HasMaxLength(ModelDefinitionConsts.MaxDisplayNameLength);
b.Property(x => x.Type).IsRequired().HasMaxLength(ModelDefinitionConsts.MaxTypeLength);
b.HasMany(x => x.Fields)
.WithOne()
.HasForeignKey(x => x.ModelDefinitionId)
;
b.HasIndex(x => x.Name);
});
builder.Entity <ModelField>(b =>
{
b.ToTable(options.TablePrefix + "ModelFields", options.Schema);
b.ConfigureByConvention();
b.HasKey(x => new { x.FieldDefinitionId, x.ModelDefinitionId });
b.HasOne(x => x.FieldDefinition)
.WithMany()
.HasForeignKey(x => x.FieldDefinitionId);
b.HasOne(x => x.ModelDefinition)
.WithMany(x => x.Fields)
.HasForeignKey(x => x.ModelDefinitionId)
;
});
builder.Entity <DynamicEntities.DynamicEntity>(b =>
{
b.ToTable(options.TablePrefix + "DynamicEntities", options.Schema);
b.ConfigureByConvention();
b.ConfigureDynamicEntityModel();
b.HasIndex(x => x.ExtraProperties);
});
// register JSON function to EF
string jsonFunction;
if (builder.IsUsingSqlite())
{
jsonFunction = "JSON_EXTRACT";
}
else
{
jsonFunction = "JSON_VALUE";
}
builder.HasDbFunction(typeof(DbFunctions).GetMethod(nameof(DbFunctions.JsonValue)) !)
.HasTranslation(e => SqlFunctionExpression.Create(
jsonFunction, new SqlExpression[]
{
new SqlFragmentExpression("ExtraProperties"),
e.ToArray()[0]
}, typeof(string), null));
}
public static SqlFunctionExpression Function(string name, IEnumerable <SqlExpression> arguments, Type returnType)
{
var typeMapping = GetTypeMapping(returnType);
return(SqlFunctionExpression.Create(name, arguments, returnType, typeMapping));
}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity <Asset>()
.HasIndex(a => new { a.Name, a.Version });
builder.Entity <Channel>().HasIndex(c => c.Name).IsUnique();
builder.Entity <BuildChannel>()
.HasKey(
bc => new
{
bc.BuildId,
bc.ChannelId
});
builder.Entity <BuildChannel>()
.HasOne(bc => bc.Build)
.WithMany(b => b.BuildChannels)
.HasForeignKey(bc => bc.BuildId);
builder.Entity <BuildChannel>()
.HasOne(bc => bc.Channel)
.WithMany(c => c.BuildChannels)
.HasForeignKey(bc => bc.ChannelId);
builder.Entity <BuildDependency>()
.HasKey(d => new { d.BuildId, d.DependentBuildId });
builder.Entity <BuildDependency>()
.HasOne(d => d.Build)
.WithMany()
.HasForeignKey(d => d.BuildId)
.OnDelete(DeleteBehavior.Restrict);
builder.Entity <BuildDependency>()
.HasOne(d => d.DependentBuild)
.WithMany()
.HasForeignKey(d => d.DependentBuildId)
.OnDelete(DeleteBehavior.Restrict);
builder.Entity <ApplicationUserPersonalAccessToken>()
.HasIndex(
t => new
{
t.ApplicationUserId,
t.Name
})
.IsUnique();
builder.Entity <DefaultChannel>()
.HasIndex(
dc => new
{
dc.Repository,
dc.Branch,
dc.ChannelId
})
.IsUnique();
builder.Entity <SubscriptionUpdate>()
.HasOne(su => su.Subscription)
.WithOne()
.HasForeignKey <SubscriptionUpdate>(su => su.SubscriptionId)
.OnDelete(DeleteBehavior.Restrict);
builder.ForSqlServerIsSystemVersioned <SubscriptionUpdate, SubscriptionUpdateHistory>("6 MONTH");
builder.Entity <SubscriptionUpdateHistory>().HasIndex("SubscriptionId", "SysEndTime", "SysStartTime");
builder.Entity <Repository>().HasKey(r => new { r.RepositoryName });
builder.Entity <RepositoryBranch>()
.HasKey(
rb => new
{
rb.RepositoryName,
rb.BranchName
});
builder.Entity <RepositoryBranch>()
.HasOne(rb => rb.Repository)
.WithMany(r => r.Branches)
.HasForeignKey(rb => new { rb.RepositoryName });
builder.Entity <RepositoryBranchUpdate>()
.HasKey(
ru => new
{
ru.RepositoryName,
ru.BranchName
});
builder.Entity <RepositoryBranchUpdate>()
.HasOne(ru => ru.RepositoryBranch)
.WithOne()
.HasForeignKey <RepositoryBranchUpdate>(
ru => new
{
ru.RepositoryName,
ru.BranchName
})
.OnDelete(DeleteBehavior.Restrict);
builder.Entity <GoalTime>()
.HasKey(
gt => new
{
gt.DefinitionId,
gt.ChannelId
});
builder.Entity <GoalTime>()
.HasOne(gt => gt.Channel)
.WithMany()
.HasForeignKey(gt => gt.ChannelId);
builder.ForSqlServerIsSystemVersioned <RepositoryBranchUpdate, RepositoryBranchUpdateHistory>("6 MONTH");
builder.Entity <RepositoryBranchUpdateHistory>()
.HasKey(
ru => new
{
ru.RepositoryName,
ru.BranchName
});
builder.Entity <RepositoryBranchUpdateHistory>()
.HasIndex("RepositoryName", "BranchName", "SysEndTime", "SysStartTime");
builder.HasDbFunction(() => JsonExtensions.JsonValue("", ""))
.HasTranslation(args => SqlFunctionExpression.Create("JSON_VALUE", args, typeof(string), null));
}
