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)); }