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()

{

    var ctx = new MyContext(defaultConString);

    var customerId = "FISSA";

    var customer = ctx.Customers

        .Include(c => c.Orders)

        .SingleOrDefault(c => c.CustomerId == customerId);

    if (customer != null)

    {

        Console.WriteLine($"{customer.CompanyName}, Orders: {customer.Orders.Count()}");

        //      var frmMusteriSiparisleri = new Form { Text = string.Format("[{0}] - Siparisler", customerId) };

        //      var dgvSiparisler = new DataGridView { Dock = DockStyle.Fill, ReadOnly = true, DataSource = customer.Orders.ToList() };

        //      frmMusteriSiparisleri.Controls.Add(dgvSiparisler);

        //      frmMusteriSiparisleri.ShowDialog();

    }

}

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 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 DateTime OrderDate { get; set; }

    public DateTime? ShippedDate { get; set; }

    [ForeignKey("CustomerId")]

    public Customer Customer { 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; }

    // ...

}

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() };