CODEWITHSUNDEEP
c#entity-frameworkentity-framework-core
Haus
Haus

Reputation: 1492

Entity Framework Core: Restricting foreign key columns from being equal to each other

I am using Entity Framework Core 2.2 to manage a SQL Server database of traded currencies. There are two entities in the model. The first is Currency, which specifies a trade-able currency, and the other is CurrencyPair, which specifies a pair of currencies that can be exchanged for one another.

public class Currency
{
    public ulong Id { get; set; }
    public string Name {get; set; }

    [NotMapped]
    public IEnumerable<CurrencyPair> Pairs
    {
        get { PairsAsBase?.Concat( PairsAsQuote ?? new CurrencyPair[0] ); }
    }

    public virtual IEnumerable<CurrencyPair> PairsAsBase { get; set; }
    public virtual IEnumerable<CurrencyPair> PairsAsQuote { get; set; }
}

public class CurrencyPair
{
    public ulong Id { get; set; }
    public string Name { get; set; }
    public ulong BaseCurrencyId { get; set; }
    public ulong QuoteCurrencyId { get; set; }

    public virtual Currency BaseCurrency { get; set; }
    public virtual Currency QuoteCurrency { get; set; }
}

I would like to constrain the CurrencyPair table to disallow rows from having the same Currency for both BaseCurrency and QuoteCurrency fields. That is, if a specific currency has Id = 1, then a currency pair specifying BaseCurrencyId = 1 and QuoteCurrencyId = 1 would not be allowed.

Here is my DbContext.OnModelCreating implementation:

protected override void OnModelCreating( ModelBuilder modelBuilder )
{
    modelBuilder.Entity<Currency>().HasKey(x => x.Id);
    modelBuilder.Entity<Currency>().HasAlternateKey(x => x.Name);
    modelBuilder.Entity<Currency>()
                .HasMany(x => x.PairsAsBase)
                .WithOne(x => x.BaseCurrency)
                .HasForeignKey(x => x.BaseCurrencyId);
    modelBuilder.Entity<Currency>()
                .HasMany(x => x.PairsAsQuote)
                .WithOne(x => x.QuoteCurrency)
                .HasForeignKey(x => x.QuoteCurrencyId);

    modelBuilder.Entity<CurrencyPair>().HasKey(x => x.Id);
    modelBuilder.Entity<CurrencyPair>()
                .HasOne(x => x.BaseCurrency)
                .WithMany(x => x.PairsAsBase)
                .HasForeignKey(x => x.BaseCurrencyId);
    modelBuilder.Entity<CurrencyPair>()
                .HasOne(x => x.QuoteCurrency)
                .WithMany(x => x.PairsAsQuote)
                .HasForeignKey(x => x.QuoteCurrencyId);
}

TL;DR: How can I ensure that two foreign key columns in a table do not both reference the same entity (using Entity Framework Core 2.2)?

Upvotes: 0

Views: 694

Answers (2)

Jota.Toledo
Jota.Toledo

Reputation: 28434

AFAIK there is no good way to enforce your rule at the model builder lvl. The next best thing would be to intercept SQL commands that could generate faulty data through an ef context, but the API isnt mature enough to make this an easy option.

In my opinion, the only options that you have left have no relation whatsoever to EF:

  • Constrain: Enforce the rule on your DB schema, eg. through a CHECK constraint
  • Validate: Enforce the rule at domain model level, eg. by intercepting the setters for both properties in the class and validating their values.

Upvotes: 1

Asakuraa Ranger
Asakuraa Ranger

Reputation: 617

Did you try Global query filters, this should help you protect some unwanted to be show up when you query

modelBuilder.Entity<CurrencyPair>().HasQueryFilter(p => p.BaseCurrency != p.QuoteCurrency);

Data will still store in your table but it not show up when you using it.

Upvotes: 1

Related Questions