LINQ连接查询返回匹配的记录

Question

class loan

{

  public money Value{set; get;}

  public int id{set; get;}

  public int CustomerId{set; get;}

}

class loanPayment

{

  public int id{set; get;} 

  public int LoanId{set; get;}

  public money Value{set; get;}

}

我尝试了这个 LINQ 代码：

//_CustomerId is a parameter

 DatabaseContext context = new DatabaseContext();

  var query =

           (from l in context.Loan

            where l.CustomerId == _CustomerId

            join lp in context.LoanPayment on l.Id equals lp.LoanId into j1

            from Payments in j1

            select Payments 

            ).ToList();

现在我的问题是，此代码将仅返回loan至少有一项的记录payment，但我希望记录与其付款相关联，并且我还想要尚未付款的贷款，我该如何执行此操作？

像这样的东西：

第一笔贷款有付款，但第二笔没有。

Answer 1

您确实不需要在 Linq 中加入任何关系来建立关系。您只需要在模型中定义您的关系（生成器会自动为您执行此操作，前提是您已在数据库本身中设置了关系 - 或者您直接在模型中执行此操作，例如首先使用代码）。以下示例展示了为什么不需要它，以及如何使用导航属性（基于 MS SQL Northwind 示例数据库）：string defaultConString = @"server=.\SQLExpress2012;Database=Northwind;Trusted_Connection=yes;";void Main(){&nbsp; &nbsp; var ctx = new MyContext(defaultConString);&nbsp; &nbsp; var customerId = "FISSA";&nbsp; &nbsp; var customer = ctx.Customers&nbsp; &nbsp; &nbsp; &nbsp; .Include(c => c.Orders)&nbsp; &nbsp; &nbsp; &nbsp; .SingleOrDefault(c => c.CustomerId == customerId);&nbsp; &nbsp; if (customer != null)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; Console.WriteLine($"{customer.CompanyName}, Orders: {customer.Orders.Count()}");&nbsp; &nbsp; &nbsp; &nbsp; //&nbsp; &nbsp; &nbsp; var frmMusteriSiparisleri = new Form { Text = string.Format("[{0}] - Siparisler", customerId) };&nbsp; &nbsp; &nbsp; &nbsp; //&nbsp; &nbsp; &nbsp; var dgvSiparisler = new DataGridView { Dock = DockStyle.Fill, ReadOnly = true, DataSource = customer.Orders.ToList() };&nbsp; &nbsp; &nbsp; &nbsp; //&nbsp; &nbsp; &nbsp; frmMusteriSiparisleri.Controls.Add(dgvSiparisler);&nbsp; &nbsp; &nbsp; &nbsp; //&nbsp; &nbsp; &nbsp; frmMusteriSiparisleri.ShowDialog();&nbsp; &nbsp; }}public class MyContext : DbContext{&nbsp; &nbsp; public MyContext(string connectionString)&nbsp; &nbsp; &nbsp; &nbsp;: base(connectionString)&nbsp; &nbsp; { }&nbsp; &nbsp; public DbSet<Customer> Customers { get; set; }&nbsp; &nbsp; public DbSet<Order> Orders { get; set; }&nbsp; &nbsp; public DbSet<OrderDetail> OrderDetails { get; set; }&nbsp; &nbsp; public DbSet<Product> Products { get; set; }}public class Customer{&nbsp; &nbsp; [Key]&nbsp; &nbsp; public string CustomerId { get; set; }&nbsp; &nbsp; public string CompanyName { get; set; }&nbsp; &nbsp; public string ContactName { get; set; }&nbsp; &nbsp; // ...&nbsp; &nbsp; public virtual List<Order> Orders { get; set; }}public class Order{&nbsp; &nbsp; [Key]&nbsp; &nbsp; public int OrderId { get; set; }&nbsp; &nbsp; public string CustomerId { get; set; }&nbsp; &nbsp; public DateTime OrderDate { get; set; }&nbsp; &nbsp; public DateTime? ShippedDate { get; set; }&nbsp; &nbsp; [ForeignKey("CustomerId")]&nbsp; &nbsp; public Customer Customer { get; set; }&nbsp; &nbsp; public virtual List<OrderDetail> OrderDetails { get; set; }}[Table("Order Details")]public class OrderDetail{&nbsp; &nbsp; [Key]&nbsp; &nbsp; [Column(Order = 1)]&nbsp; &nbsp; public int OrderId { get; set; }&nbsp; &nbsp; [Key]&nbsp; &nbsp; [Column(Order = 2)]&nbsp; &nbsp; public int ProductId { get; set; }&nbsp; &nbsp; public decimal UnitPrice { get; set; }&nbsp; &nbsp; public Int16 Quantity { get; set; }&nbsp; &nbsp; [ForeignKey("ProductId")]&nbsp; &nbsp; public Product Product { get; set; }&nbsp; &nbsp; [ForeignKey("OrderId")]&nbsp; &nbsp; public Order Order { get; set; }}public class Product{&nbsp; &nbsp; public int ProductId { get; set; }&nbsp; &nbsp; public string ProductName { get; set; }&nbsp; &nbsp; // ...}

Answer 2

您需要左连接和DefaultIfEmpty方法，如下所示：var query = from l in context.Loan            join lp in context.LoanPayment on l.Id equals lp.LoanId into j1            from lp in j1.DefaultIfEmpty()            select new { Loan = l, LoanPayment = lp == null ? "(No LoanPayment)"            : lp.Id.ToString() };或者，如果您想选择 的整个对象LoanPayment：select new { Loan = l, LoanPayment = lp ?? new loanPayment() };