EF Core >= 7 : composite unique Index involving nested objects

582 Views Asked by At

I would like to add a composite unique index like this on Town having Zip as a ValueObject and Country as a navigation property whose Id is not explicitly specified :

x.HasIndex(t => new { t.Zip.Value, t.Country.Id })

but this fails with the following message:

Unable to create a 'DbContext' of type ''.
The exception 'The expression 'e => new <>f__AnonymousType0`2(Value = e.Zip.Value, Id = e.Country.Id)' is not a valid member access expression.
The expression should represent a simple property or field access:
't => t.MyProperty'.
When specifying multiple properties or fields, use an anonymous type:
't => new { t.MyProperty, t.MyField }'.

(Parameter 'memberAccessExpression')' was thrown while attempting to create an instance.

Thanks in advance,

public class Town
{
    public Town() {}
    
    public Guid Id { get; set; }
    public Zip Zip { get; set; }
    public string Name { get; set; }
    public virtual Country { get; set; }
}

public class Zip 
{
    public Zip() {}

    public string Value { get; set; }
    public string CountryCode { get; set; }
}

public class Country 
{
    public Country() {}

    public Guid Id { get; set; }
    public string Code { get;  set; }
    public string Name { get;  set; }
    public int CallingCode { get;  set; }
}

public class MyDbContext : DbContext
{
    public DbSet<Town> Towns { get; set; }
    public DbSet<Country> Countries { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Town>(x =>
        {
            x.ToTable(nameof(Town), schema);
            x.HasKey(x => x.Id);
            x.Property(x => x.Name);
            x.OwnsOne(x => x.Zip, x =>
            {
                x.Property(x => x.Value).HasColumnName("ZipValue").IsRequired(true);
                x.Property(x => x.CountryCode).HasColumnName("ZipCountryCode").IsRequired(true);
            });
            x.HasOne(x => x.Country).WithMany(); 
        });
    }
}

public class MyDbContext : DbContext
{
    public DbSet<Town> Towns { get; set; }
    public DbSet<Country> Countries { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Town>(x =>
        {
            x.ToTable(nameof(Town), schema);
            x.HasKey(x => x.Id);
            x.Property(x => x.Name);
            x.OwnsOne(x => x.Zip, x =>
            {
                x.Property(x => x.Value).HasColumnName("ZipValue").IsRequired(true);
                x.Property(x => x.CountryCode).HasColumnName("ZipCountryCode").IsRequired(true);
            });
            x.HasOne(x => x.Country).WithMany();

            // Add unique index on Zip and CountryId
            x.HasIndex(x => new { x.Zip.Value, x.Zip.CountryCode, x.Country.Id }).IsUnique();
        });
    }
}

I tried all the possible syntax and overloads of HasIndex with shadow properties, this is accepted but it creates new columns that are always NULL :

modelBuilder.Entity<Agridea.Acorda.Farm.Domain.Register.RegisterShared.ReferenceData.Town.Town>(x =>
{
   x.Property<string>("ZipValue");
   x.HasIndex("ZipValue", "CountryId").IsUnique();
});
1

There are 1 best solutions below

0
ThierryCattel On

After lot of rearrangements, I found this (.HasColumnName("ZipValue")):

modelBuilder.Entity<Agridea.Acorda.Farm.Domain.Register.RegisterShared.Referen>ceData.Town.Town>(x =>
{
   x.Property<string>("ZipValue").HasColumnName("ZipValue");
   x.HasIndex("ZipValue", "CountryId").IsUnique();
});

Which generates the apparently correct migration and with no extra Town_ZipValue Column in the db