C#linqjoin回傳多個結果

我有這個查詢，模型可以有多個 B 和 C。模型有 2 個 C，這導致回傳 2 個模型。它應該只回傳帶有 1 個 A 和 B 和 C 串列的 1 個模型 是否可以在 linq 中實作它？

模型表有 ModelId、Name

B 表有 BId、ModelId、Name

C表有CId、ModelId、Name

var x = 
    (from model in this.GetDefaultQuery()
    join a in _context.a on model.modelId equals a.modelId
    join b in _context.b on model.modelId equals b.modelId //this should be a list of b
    join c in _context.c on model.modelId equals c.modelId //this should be a distinc list of c
    select new
    {
        M = model,
        A = A,
        B = B, //this should be a list of B's Name
        C = C, //this should be a distinc list of C's Name
    })
    .Where(x => x.model.modelId == modelId);

uj5u.com熱心網友回復：

為什么需要JOIN一定是第一個問題。有了適當的關系，你甚至不需要它（也不應該）。

你所描述的看起來像：

var result = 
    (from model in this.GetDefaultQuery()
    select new
    {
        A = (from a in _context.a where model.aId == a.aId select a).ToList(),
        B = (from b in _context.b where model.bId == b.bId select b).ToList(),
        C = (from c in _context.c where model.cId == c.cId select c).Distinct().ToList()
    })
    .Where(x => x.modelId == modelId);

編輯：很難用理解語法撰寫。在方法語法中，它更容易：

var result = this.GetDefaultQuery()
      .Where(x => x.modelId == modelId)
      .Select(model => new {
          A = _context.a.Where(x => x.aId == model.aId).ToList(),
          B = _context.b.Where(x => x.bId == model.bId).Distinct().ToList(),
          C = _context.c.Where(x => x.cId == model.cId).ToList()
});

PS：生成的 SQL 可能會非常低效。

編輯：作業樣本。請注意，連接字串是我所擁有的，而且您的模型并沒有真正具有您建議的導航屬性，這里我有導航屬性但不用于模擬您的情況。否則那些 ctx.Customers.Where(...) 將只是 o.Customer 作為示例。

string defaultConString = @"server=.\SQLExpress2012;Database=Northwind;Trusted_Connection=yes;";

void Main()
{
    var ctx = new MyContext(defaultConString);

    var result = ctx.Orders
        .Where(o => o.OrderId == 10249)
        .Select(o => new
        {
            A = ctx.Customers.Where(c => c.CustomerId == o.CustomerId).ToList(), // Single in fact. Could be SingleOrDefault()
            B = ctx.Employees.Where(e => e.EmployeeId == o.EmployeeId).ToList(),
            C = ctx.OrderDetails.Where(od => od.OrderId == o.OrderId).ToList()

        }

        );

    // Check result - Linqpad sample
    //result.Dump(); 
}


public class MyContext : DbContext
{
    public MyContext(string connectionString)
       : base(connectionString)
    { }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderDetail> OrderDetails { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<Employee> Employees { get; set; }
}



public class Customer
{
    [Key]
    public string CustomerId { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    // ...
    //public virtual List<Order> Orders { get; set; }
}

public class Order
{
    [Key]
    public int OrderId { get; set; }
    public string CustomerId { get; set; }
    public int EmployeeId { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime? ShippedDate { get; set; }
    [ForeignKey("CustomerId")]
    public Customer Customer { get; set; }
    [ForeignKey("EmployeeId")]
    public Employee Employee { get; set; }
    public virtual List<OrderDetail> OrderDetails { get; set; }
}

[Table("Order Details")]
public class OrderDetail
{
    [Key]
    [Column(Order = 1)]
    public int OrderId { get; set; }
    [Key]
    [Column(Order = 2)]
    public int ProductId { get; set; }
    public decimal UnitPrice { get; set; }
    public Int16 Quantity { get; set; }
    [ForeignKey("ProductId")]
    public Product Product { get; set; }
    [ForeignKey("OrderId")]
    public Order Order { get; set; }
}

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    // ...
}

public class Employee
{
    [Key]
    public int EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

標籤：C＃ 林克

上一篇：如何從物件集合中獲取具有第二個最新日期的物件

下一篇：如何分組多列并計數？