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