Join with inner list

Question

I have this linq query:

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

The viewmodel:

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

What is happening is once I'm executing the query I'm getting 125 records, and that's the number of the StatementLines for that investor. So I'm getting 125 same records but I'm expecting one result which will have 125 statement lines in the inner list.

Is this query correct?

Answer 1

1. Use GroupJoin instead of 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()
                           };
   

   Also instead of performing the sub-query just use the data from the join you just performed.

2. A better option, using entity framework, is using navigation properties and then you do not need to perform a join but you just have InvestorStatementLines as a property of your investor.

   To set the navigation properties:

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

   And the query will be as simple as:

   var investorData = from investor in db.Investors
                      where investor.UserId == userId
                      select new InvestorViewModel()
                      {
                          InvestorId = investor.InvestorID,
                          ....
                          StatementLines = investor.InvestorStatementLines.Select(....)
                      };