用linq到ef连接4个表

Question

我有四个表/类

public Class Brands
{
  public int Id {get;set;}
  public string Brand {get;set;}
  public String BrandType {get;set;}
}
public Class ManufactureA
{
  public int Id {get;set;}
  public int BrandsId {get;set;}
  public string Product {get;set;} 
  public int Distributor {get;set;}
}
public Class ManufactureB
{
  public int Id {get;set;}
  public int BrandsId {get;set;}
  public string Product {get;set;}
  public int Distributor {get;set;}
}
public Class ManufactureC
{
  public int Id {get;set}
  public int BrandsId {get;set;}
  public string Product {get;set;}
  public int Distributor {get;set;}
}
public Class ManufactureD
{
  public int Id {get;set;}
  public int BrandsId {get;set;}
  public string product {get;set;}
  public int Distributor {get;set;}
}

我正试图制作一个表格，显示品牌和相关制造商的信息。例如:

Brand1:

有的时候,DistributorA

Brand2:

ProductB, DistibutorB

Brand3:

ProductC, DistributorC

Brand4:

ProductD, DistributorD

所以我从这段代码开始，但在决定如何实际分组或投影时感到困惑:

var allBrandsManufactures = from brand in Brands
                            join factoryA in ManufactureA on factoryA.BrandsId equals brand.Id
                            join factoryB in ManufactureB on factoryB.BrandsId equals brand.Id
                            join factoryC in ManufactureC on factoryC.BrandsId equals brand.Id
                            join factoryD in ManufactureD on factoryD.BrandsId equals brand.Id

Answer 1

首先，如果可能的话，您应该认真考虑重新设计数据库。您的设计当前在表名中包含信息。您应该能够将所有的Manufacture表合并为一个可能应该称为Products的表。然后应该有一个额外的列来指示它是哪个制造商。这样的。

public class Products
{
  public int Id {get;set;}
  public int BrandsId {get;set;}
  public string ProductName {get;set;} 
  public int Distributor {get;set;}
  public string Manufacturer {get;set;}
}

或者您可以创建一个单独的Manufacturer表，并通过外键将Product表链接到它，但是只有当您想要将额外的Manufacturer数据放入DB中时才真正需要这样做。然后，如果得到一个新的Manufacturer，就不需要创建新表了。它也使你的查询更容易。

现在，如果您坚持这种设计，那么您将希望使用联合而不是连接。最好的方法是单独执行每个制造商查询，然后使用Concat将它们组合起来。

var brandA = from brand in Brands
         join factoryA in ManufactureA on brand.Id equals factoryA.BrandsId
         select new { 
            Brand = brand.Brand, 
            Product = factoryA.Product, 
            Distributor = factoryA.Distributor, 
            Manufacturer = "A"};
var brandB = from brand in Brands
         join factoryB in ManufactureA on brand.Id equals factoryB.BrandsId
         select new { 
            Brand = brand.Brand, 
            Product = factoryB.Product, 
            Distributor = factoryB.Distributor, 
            Manufacturer = "B"};
var brandC = from brand in Brands
         join factoryC in ManufactureA on brand.Id equals factoryC.BrandsId
         select new { 
            Brand = brand.Brand, 
            Product = factoryC.Product, 
            Distributor = factoryC.Distributor, 
            Manufacturer = "C"};
var brandD = from brand in Brands
         join factoryD in ManufactureA on brand.Id equals factoryD.BrandsId
         select new { 
            Brand = brand.Brand, 
            Product = factoryD.Product, 
            Distributor = factoryD.Distributor, 
            Manufacturer = "D"};
var result = brandA.Concat(brandB).Concat(brandC).Concat(brandD);

你当然可以选择任何你想要的，但你必须在每个查询中选择相同的东西，并为属性使用相同的名称。