与内部列表连接

本文关键字：连接 列表 内部 | 更新日期: 2023-09-27 18:11:45

我有这样的linq查询:

var investorData = from investor in db.Investors
                        join investorLine in db.InvestorStatementLines
                            on investor.InvestorID equals investorLine.InvestorID
                        where investor.UserId == userId
                        select new InvestorViewModel()
                        {
                            InvestorId = investor.InvestorID,
                            InvestorName = investor.Name,
                            FundingDate = investor.FundingDate,
                            DueDate = investor.DueDate,
                            FundsCommitted = investor.FundsCommitted,
                            FundsInvested = investor.FundsInvested,
                            StatementLines =
                                db.InvestorStatementLines.Where(s => s.InvestorID == investor.InvestorID)
                                    .Select(t => new InvestorStatementLineVM
                                    {
                                        Balance = t.Balance,
                                        Credit = t.Credit,
                                        Debit = t.Debit,
                                        InvestorStatementLineDetails = t.Details,
                                        Date = t.Date
                                    }).ToList()
                        };

视图模型:

public class InvestorViewModel
{
    public int InvestorId { get; set; }
    public string InvestorName { get; set; }
    public DateTime FundingDate { get; set; }
    public DateTime? DueDate { get; set; }
    public Decimal? FundsCommitted { get; set; }
    public Decimal? FundsInvested { get; set; }
    public List<InvestorStatementLineVM>  StatementLines { get; set; }
}

所发生的是，一旦我执行查询，我将得到125条记录，这是该投资者的StatementLines的数量。所以我得到125个相同的记录，但我期待一个结果，将有125个语句行在内部列表。

这个查询正确吗?

与内部列表连接

这就是如何使用导航属性

var investorData = from investor in db.Investors
                   where investor.UserId == userId
                   select new InvestorViewModel()
                   {
                       InvestorId = investor.InvestorID,
                       InvestorName = investor.Name,
                       FundingDate = investor.FundingDate,
                       DueDate = investor.DueDate,
                       FundsCommitted = investor.FundsCommitted,
                       FundsInvested = investor.FundsInvested,
                       StatementLines = investor.InvestorStatementLines
                           .Select(t => new InvestorStatementLineVM
                           {
                               Balance = t.Balance,
                               Credit = t.Credit,
                               Debit = t.Debit,
                               InvestorStatementLineDetails = t.Details,
                               Date = t.Date
                           }).ToList()
                   };

  1. 使用GroupJoin代替Join: (_join x in y on x.a equals y.a into z_)

    var investorData = from investor in db.Investors
                        join investorLine in db.InvestorStatementLines
                        on investor.InvestorID equals investorLine.InvestorID
                        into investorLine
                        where investor.UserId == userId
                        select new InvestorViewModel()
                        {
                            InvestorId = investor.InvestorID,
                            InvestorName = investor.Name,
                            FundingDate = investor.FundingDate,
                            DueDate = investor.DueDate,
                            FundsCommitted = investor.FundsCommitted,
                            FundsInvested = investor.FundsInvested,
                            StatementLines = investorLine
                                .Select(t => new InvestorStatementLineVM
                                {
                                    Balance = t.Balance,
                                    Credit = t.Credit,
                                    Debit = t.Debit,
                                    InvestorStatementLineDetails = t.Details,
                                    Date = t.Date
                                }).ToList()
                        };

    不执行子查询，只使用刚刚执行的连接中的数据。

  2. 一个更好的选择，使用实体框架，是使用导航属性，然后你不需要执行连接，但你只是有InvestorStatementLines作为你的investor的属性

    设置导航属性:

    public class InvestorViewModel
{
    public int InvestorId { get; set; }
    public string InvestorName { get; set; }
    public DateTime FundingDate { get; set; }
    public DateTime? DueDate { get; set; }
    public Decimal? FundsCommitted { get; set; }
    public Decimal? FundsInvested { get; set; }
    public virtual ICollection<InvestorStatementLineVM>  StatementLines { get; set; }
}

    查询将像

    一样简单:
    var investorData = from investor in db.Investors
                   where investor.UserId == userId
                   select new InvestorViewModel()
                   {
                       InvestorId = investor.InvestorID,
                       ....
                       StatementLines = investor.InvestorStatementLines.Select(....)
                   };
相关文章：