private static void AddSQLiteCompatibilityFeatures(ModelBuilder modelBuilder) { // SQLite does not have proper support for DateTimeOffset via Entity Framework Core, see the limitations // here: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations. // To work around this, when the Sqlite database provider is used, all model properties of type DateTimeOffset // use the DateTimeOffsetToBinaryConverter // Based on: https://github.com/aspnet/EntityFrameworkCore/issues/10784#issuecomment-415769754 // This only supports millisecond precision, but should be sufficient for most use cases. // Credits to https://blog.dangl.me/archive/handling-datetimeoffset-in-sqlite-with-entity-framework-core/. foreach (var entityType in modelBuilder.Model.GetEntityTypes()) { var properties = entityType .ClrType .GetProperties() .Where(p => p.PropertyType == typeof(DateTimeOffset) || p.PropertyType == typeof(DateTimeOffset?)); var dateTimeOffsetConverter = new DateTimeOffsetToBinaryConverter(); foreach (var property in properties) { modelBuilder .Entity(entityType.Name) .Property(property.Name) .HasConversion(dateTimeOffsetConverter); } } // The SQLite database provider does not properly implement concurrency tokens. Therefore, a custom // SQLite value converter and comparer must be used for concurrency tokens. // More info: https://entityframeworkcore.com/knowledge-base/52684458/updating-entity-in-ef-core-application-with-sqlite-gives-dbupdateconcurrencyexception. var concurrencyTokens = modelBuilder.Model .GetEntityTypes() .SelectMany(e => e.GetProperties()) .Where(p => p.ClrType == typeof(byte[]) && p.ValueGenerated == ValueGenerated.OnAddOrUpdate && p.IsConcurrencyToken); var concurrencyTokenConverter = new SQLiteConcurrencyTokenConverter(); var concurrencyTokenComparer = new SQLiteConcurrencyTokenComparer(); foreach (var property in concurrencyTokens) { property.SetValueConverter(concurrencyTokenConverter); property.SetValueComparer(concurrencyTokenComparer); property.SetDefaultValueSql(SQLiteTimestampDefaultValue); } }
protected override void OnModelCreating(ModelBuilder modelBuilder) { var dtoConverter = new DateTimeOffsetToBinaryConverter(); modelBuilder.Entity <GuildPrefixes>( model => { model.HasIndex(prefixes => prefixes.GuildId).IsUnique(); model.Property(prefixes => prefixes.GuildId).ValueGeneratedNever(); if (Database.IsNpgsql()) { model.Property(prefixes => prefixes.Values).HasConversion( prefixes => prefixes.Select(x => x.ToString()).ToArray(), arr => new HashSet <IPrefix>(arr.Select(ParseStringAsPrefix))); } else { model.Ignore(prefixes => prefixes.Values); } }); modelBuilder.Entity <UserLocalisation>( model => { model.HasKey( localisation => new { localisation.GuildId, localisation.UserId }); model.Property(localisation => localisation.GuildId).ValueGeneratedNever(); model.Property(localisation => localisation.UserId).ValueGeneratedNever(); model.Property(localisation => localisation.Value) .HasConversion(new EnumToNumberConverter <Language, int>()); }); modelBuilder.Entity <UserReminder>( model => { model.HasKey(reminder => reminder.Id); model.Property(reminder => reminder.Id).ValueGeneratedOnAdd(); model.Property(reminder => reminder.Value).ValueGeneratedNever(); model.Property(reminder => reminder.ChannelId).ValueGeneratedNever(); model.Property(reminder => reminder.TriggerAt).ValueGeneratedNever(); model.Property(reminder => reminder.UserId).ValueGeneratedNever(); model.Property(reminder => reminder.ReminderMessageId).ValueGeneratedNever(); model.Property(reminder => reminder.TriggerAt).HasConversion(dtoConverter); }); modelBuilder.Entity <Tag>( model => { model.HasKey(tag => tag.Id); model.Property(tag => tag.Id).ValueGeneratedOnAdd(); model.Property(tag => tag.Key).ValueGeneratedNever(); model.Property(tag => tag.Uses).ValueGeneratedNever(); model.Property(tag => tag.Value).ValueGeneratedNever(); model.Property(tag => tag.CreateAt).ValueGeneratedNever(); model.Property(tag => tag.CreateAt).HasConversion(dtoConverter); }); modelBuilder.Entity <GuildTags>( model => { model.HasIndex(tags => tags.GuildId).IsUnique(); model.Property(tags => tags.GuildId).ValueGeneratedNever(); model.HasMany(tags => tags.Values).WithOne(tag => tag.GuildTags).OnDelete(DeleteBehavior.Cascade); }); modelBuilder.Entity <GuildTag>( model => { model.HasOne(tag => tag.GuildTags).WithMany(tags => tags.Values).HasForeignKey(tag => tag.GuildId); model.Property(tag => tag.GuildId).ValueGeneratedNever(); model.Property(tag => tag.CreatorId).ValueGeneratedNever(); model.Property(tag => tag.OwnerId).ValueGeneratedNever(); }); //ef core won't properly discriminate subclasses without this existing modelBuilder.Entity <GlobalTag>(model => { }); }
protected override void OnModelCreating(ModelBuilder builder) { var datetimeConverter = new DateTimeOffsetToBinaryConverter(); builder.Entity <Category>(e => { e.ToTable("ArtikelSoorten"); e.HasKey(q => q.Id); e.Property(q => q.Id) .HasColumnName("ArtikelID") .IsRequired(); e.Property(q => q.Name) .HasColumnName("ArtikelNaam") .HasMaxLength(80) .IsRequired(); }); builder.Entity <ProfileData>(e => { e.ToTable("ProfileData"); e.HasKey(q => q.Id); e.Property(q => q.Id) .HasColumnName("Id") .IsRequired(); e.Property(q => q.Email) .HasColumnName("Email") .HasMaxLength(50) .IsRequired(false); e.Property(q => q.FirstName) .HasColumnName("Voornaam") .HasMaxLength(50) .IsRequired(false); e.Property(q => q.LastName) .HasColumnName("Achternaam") .HasMaxLength(50) .IsRequired(false); e.Property(q => q.Balans) .HasDefaultValue(0) .HasColumnName("Balans") .IsRequired(); e.Property(q => q.AccountTypeId) .HasColumnName("AccountType") .IsRequired(false); e.Property(q => q.MemberCardId) .HasColumnName("Ledenpas") .IsRequired(false); e.Property(q => q.Street) .HasColumnName("Straat") .HasMaxLength(50) .IsRequired(false); e.Property(q => q.HouseNumber) .HasColumnName("Huisnummer") .HasMaxLength(10) .IsRequired(false); e.Property(q => q.City) .HasColumnName("Woonplaats") .HasMaxLength(50) .IsRequired(false); e.Property(q => q.PostalCode) .HasColumnName("Postcode") .HasMaxLength(10) .IsRequired(false); e.Property(q => q.AccountCreated) .HasColumnName("DateCreated") .HasConversion(datetimeConverter) .IsRequired(false); e.Property(q => q.DateOfBirth) .HasColumnName("Geboortedatum") .HasConversion(datetimeConverter) .IsRequired(false); }); builder.Entity <Product>(e => { e.ToTable("Artikelen"); e.HasKey(q => q.Id); e.Property(q => q.Id) .HasColumnName("ArtikelID") .IsRequired(); e.Property(q => q.Name) .HasColumnName("ArtikelNaam") .HasMaxLength(50) .IsRequired(false); e.Property(q => q.PointsWorth) .HasColumnName("ArtikelPunten") .IsRequired(); e.Property(q => q.CategorieId) .HasColumnName("ArtikelSoortID") .IsRequired(); e.Property(q => q.SerialNumber) .HasColumnName("Serienummer") .HasMaxLength(50) .IsRequired(false); e.HasOne(q => q.Categorie) .WithMany(q => q.Products) .HasForeignKey(q => q.CategorieId); }); builder.Entity <Transaction>(e => { e.ToTable("Transacties"); e.HasKey(q => q.TransactionId); e.Property(q => q.TransactionId) .HasColumnName("TransactieID") .IsRequired(); e.Property(q => q.IsLoan) .HasColumnName("Lening") .IsRequired(); e.Property(q => q.Date) .HasColumnName("Datum") .HasConversion(datetimeConverter) .HasDefaultValue(DateTimeOffset.MinValue) .IsRequired(); e.Property(q => q.ProfileId) .HasColumnName("ProfileId") .IsRequired(); e.Property(q => q.IsDonation) .HasColumnName("Donatie") .IsRequired(); e.Property(q => q.SerialNumber) .HasColumnName("Serienummer") .HasDefaultValue(null) .HasMaxLength(50) .IsRequired(false); e.HasOne(q => q.Customer) .WithMany(q => q.Transactions) .HasForeignKey(q => q.ProfileId); }); builder.Entity <TransactionProduct>(e => { e.ToTable("TransactieArtikelen"); e.HasKey(q => q.TransactionProductId); e.Property(q => q.TransactionId) .HasColumnName("TransactieID") .IsRequired(); e.Property(q => q.ProductId) .HasColumnName("ArtikelID") .HasMaxLength(24) .IsRequired(); e.Property(q => q.IsForSell) .HasColumnName("IsVerkoop") .HasConversion(new BoolToZeroOneConverter <byte>()) .IsRequired(); e.Property(q => q.Points) .HasColumnName("Punten") .IsRequired(); e.Property(q => q.NumberOfProduct) .HasColumnName("Aantal") .IsRequired(); e.Property(q => q.TransactionProductId) .HasColumnName("TransactieArtikelId") .IsRequired(); e.HasOne(q => q.Transaction) .WithMany(q => q.TransactionProducts) .HasForeignKey(q => q.TransactionId); e.HasOne(q => q.Product) .WithMany(q => q.TransactionProducts) .HasForeignKey(q => q.ProductId); }); }