c# - 一对多 (EF Core) 的建议

Question

我试图找出定义与客户和地址有关的一对多关系表的最佳方法。每个客户可以有多个地址（邮寄、账单、交付等）。地址的类型存储在一个单独的表 (AddressType) 中。

这是我所拥有的：

public class Company
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Address> Addresses { get; set; }
    }

  public class Address
    {
        public int Id { get; set; }
        public int AddressTypeId { get; set; }
        public AddressType AddressType { get; set; }
        public string Street1 { get; set; }
        public string Street2 { get; set; }
        public string City { get; set; }
        public int StateId { get; set; }
        public State State { get; set; }
        public string PostalCode { get; set; }
        public int CountryId { get; set; }
        public Country Country { get; set; }
        public bool Active { get; set; }
        public int CompanyId { get; set; }
    }

    public class AddressType
    {
        public int Id { get; set; }
        public string Display { get; set; }
        public bool Active { get; set; }
    }

几个问题...

1. 我上面的内容会被认为是好的做法吗？如果不是，你会如何定义它？
2. 假设 AddressType 表包含 Mailing、Billing 和 Delivery，我将如何发出 Linq 查询，而我只想提取 Mailing Address？

谢谢一堆。

--- 值

标签： c#entity-frameworklinqasp.net-core

Answer 1

我建议一个“基本”地址类。Mailing、Billing、Delivery 等将从这个基类继承。

public class Address
{
    public string Street { get; set; }

    public int HouseNumber { get; set; }

    public string HouseNumberAddition { get; set; }

    public string City { get; set; }

    public string Country { get; set; }
}

在送货的情况下，您可能需要打印带有送货司机地址标签的送货单。但是在基地址类中包含 DeliveryNote 是没有意义的，因为账单地址什么时候需要送货单？

因此，您从基地址类继承来创建特定的地址类型。

例如：

public class DeliveryAddress : Address
{
    public string DeliveryNote { get; set; } = "Please don't ring the bell after 10pm."
}

假设您使用 EF Code First，实体框架会创建一个Address包含一discriminator列的表。保存新地址时，该discriminator列定义地址类型。

var googleCompany = new Company
{
    DeliveryAddress = new DeliveryAddress
    {
        Street = "Google Street",
        HouseNumber = 1,
        DeliveryNote = "Watch out for the dog."
    },
    CompanyAddress = new CompanyAddress()
};

var microsoftCompany = new Company
{
    DeliveryAddress = new DeliveryAddress
    {
        Street = "Microsoft Street",
        HouseNumber = 2,
        DeliveryNote = "Don't bring an Apple device on the premise."
    },
    CompanyAddress = new CompanyAddress()
};


_context.Companies.Add(googleCompany);
_context.Companies.Add(microsoftCompany);
_context.SaveChanges();

现在要查询公司并指定您需要的地址类型，您只需调用include并让 EF Core 包含地址。

var companiesWithBothDeliveryAddress = 
    _context.Companies.Include(x => x.CompanyAddress)
                      .Include(x => x.DeliveryAddress).ToList();

var companiesWithOnlyDeliveryAddress =
    _context.Companies.Include(x => x.DeliveryAddress).ToList();

EF Fluent API 配置应该是这样的：

        protected override void OnModelCreating(ModelBuilder builder)
        {
            // Company to CompanyAddress, without inverse property on CompanyAddress.
            builder.Entity<Company>()
                .HasOne(x => x.CompanyAddress)
                .WithOne()
                .HasForeignKey<Company>(x => x.CompanyAddressId)
                .IsRequired(false)
                .OnDelete(DeleteBehavior.NoAction);

            // Company to DeliveryAddress, without inverse property on DeliveryAddress.
            builder.Entity<Company>()
                .HasOne(x => x.DeliveryAddress)
                .WithOne()
                .HasForeignKey<Company>(x => x.DeliveryAddressId)
                .IsRequired(false)
                .OnDelete(DeleteBehavior.NoAction);

            // We let all the Address types share the 'CompanyId' column, 
            // otherwise, EF would create a seperate CompanyId column for all of them.
            builder.Entity<Address>()
                .Property(x => x.CompanyId)
                .HasColumnName(nameof(Address.CompanyId));

            builder.Entity<CompanyAddress>()
                .Property(x => x.CompanyId)
                .HasColumnName(nameof(Address.CompanyId));            
            
            builder.Entity<DeliveryAddress>()
                .Property(x => x.CompanyId)
                .HasColumnName(nameof(Address.CompanyId));

            base.OnModelCreating(builder);
        }

        public DbSet<Address> Addresses { get; set; }

        public DbSet<DeliveryAddress> DeliveryAddresses { get; set; }

        public DbSet<CompanyAddress> CompanyAddresses { get; set; }
        

结果将如下所示：

地址表（为了简洁，我省略了一些列）

公司表（为简洁起见，我省略了一些列）