Laziale Laziale - 2 months ago 8
C# Question

Join with inner list

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 thats 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.

Do you guys think the query is correct?

Thanks, Laziale

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