LINQ- join with subqueries

Question

I am trying to implement the following query in LINQ

SELECT [flh].[InterestRate], [fld].[RegularPaymentAmount]
FROM [FactLoanDetail] AS [fld]
INNER JOIN [FactLoanHistory] AS [flh] ON [fld].[LoanKey] = [flh].[LoanKey]
 LEFT OUTER JOIN [FactLoanPayment] AS [flp]  ON ([flh].[LoanKey] = [flp].[LoanKey])
       AND flp.PostedDateKey = ( SELECT MAX(PostedDateKey) FROM FactLoanPayment 
                                 WHERE LoanKey = flh.LoanKey )
       AND flp.PaymentSequenceNumber = ( SELECT MAX(PaymentSequenceNumber) 
                                FROM FactLoanPayment WHERE LoanKey = flh.LoanKey )
WHERE [flh].[AsOfDateKey]  =  20200415;

This is for DataWarehouse and FactLoanPayment table does not have PK, and can have multiple records for each LoanKey and each PostedDate. I have tried

var query = from fld in _dbContext.FactLoanDetail
    join flh in _dbContext.FactLoanHistory on fld.LoanKey equals flh.LoanKey
    join flp in _dbContext.FactLoanPayment on fld.LoanKey equals flp.LoanKey into lp
      from flp in lp.OrderByDescending(p => p.PostedDateKey)
                    .ThenByDescending(p => p.PaymentSequenceNumber)
                    .Take(1)
    where flh.AsOfDateKey == 20200415
    select new {flh.InterestRate, fld].[RegularPaymentAmount}

It compiles fine, but at runtime gives me a warning

orderby [p].PostedDateKey desc, [p].PaymentSequenceNumber desc' could not be translated and will be evaluated locally.

and attempts to return all of the records for each loan from the server, not just the latest ones.

I also tried

var query = from fld in _dbContext.FactLoanDetail
   join flh in _dbContext.FactLoanHistory on fld.LoanKey equals flh.LoanKey
   join flp in _dbContext.FactLoanPayment on fld.LoanKey equals flp.LoanKey into lp
       from flp in lp.OrderByDescending(p => p.PostedDateKey)
               .ThenByDescending(p => p.PaymentSequenceNumber).Take(1).DefaultIfEmpty()
   join dpm in _dbContext.DimPaymentMethod on flp.PaymentMethodKey equals dpm.PaymentMethodKey
   where flh.AsOfDateKey == asOfDateKey &&
         flp.PostedDateKey == _dbContext.FactLoanPayment.Where(p => p.LoanKey == flp.LoanKey).Max(m => m.PostedDateKey) &&
         flp.PaymentSequenceNumber == _dbContext.FactLoanPayment.Where(p => p.LoanKey == flp.LoanKey).Max(m => m.PaymentSequenceNumber)

which also retruns all the records per loan first.

Is there better way to handle this?

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Apr 22 '20 at 19:30

score 0 · Answer 1 · answered Apr 24 '20 at 03:55

the solution was that to use another table (dimLoan) that has a reference to FLP table as virtual property, that allowed EF to resolve relation properly

var query = from fld in _dbContext.FactLoanDetail
  join dimLoan in _dbContext.DimLoan on flh.LoanKey equals dimLoan.LoanKey
  join flh in _dbContext.FactLoanHistory on fld.LoanKey equals flh.LoanKey
  join flp in _dbContext.FactLoanPayment on dimLoan.LoanKey equals flp.LoanKey
 where flh.AsOfDateKey == asOfDateKey &&
       flp.PostedDateKey == (
           dimLoan.FactLoanPayment
              .Where(m => m.LoanKey == flp.LoanKey)
              .Max(x => x.PostedDateKey)) &&
      flp.PaymentSequenceNumber == (
          dimLoan.FactLoanPayment
              .Where(m => m.LoanKey == flp.LoanKey)
              .Max(x => x.PaymentSequenceNumber))

LINQ- join with subqueries

1 Answers1