The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to GroupJoin

[dotnet95]

select a.LoanNum, PromiseAmt, isnull(SUM(PdAmt), 0) as PostedAmt
from LoanPromises a with(nolock)
left outer join LoanPayment lp with(nolock) on a.LoanNum = lp.LoanNum and a.PromiseDt < lp.PmtPostDt
group by a.LoanNum, PromiseAmt
having isnull(Sum(PdAmt),0) < PromiseAmt

Here is my linq query

var query = from pmts in oLoan.LoanPayments
join promise in oLoan.LoanPromiseToPays
on new { p1 = pmts.LoanNum, p2 = pmts.PmtPostDt} equals new {LoanNum = promise.LoanNum, PmtPostDt = promise.PromiseDt } into outer
from defaulted in outer.DefaultIfEmpty()
group defaulted by promise.LoanNum && promise.PromiseAmt into grouped
select new
{
LoanNum = grouped.Key,
Sum = grouped.Sum(t => t.PdAmt < t.PromiseAmt),
PmtPostDt = promise.PromiseDt ?? default(DateTime)
}

[Richard Deeming]

Try taking out the p1 = and p2 = from the LHS of your join. At the moment, you have:

on new { p1 = ..., p2 = ...} equals new { LoanNum = ..., PmtPostDt = ... }

The compiler has no way to map one key to the other, since the property names are different. Also, your LINQ join won't match your SQL join. The SQL has promise.PromiseDate < payment.PmtPostDt, but your LINQ query has promise.PromiseDate = payment.PmtPostDt instead.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer