CODEWITHSUNDEEP
vb.netlinq-to-entitiesleft-join
Alastair W
Alastair W

Reputation: 101

Left outer join in Linq to Entities (asp.net vb)

I've been stuck on this problem for too long and I think it's only my inexperience that's in the way. I've looked into many similar articles but none seem to work when I apply it to my situation.

I'm using linq to entities with EF6.I have two simple tables 'LoanRepayment' and 'LoanReceipt'. They are linked with a foreign from LoanRepayment.LoanPaymentId to LoanReceipt.LoanPaymentId. There are 48 fixed LoanPayments of which 4 have Receipts. I simply want to create a table with 48 rows with all 48 rows from the LoanRepayment table and any receipt data (i.e. 4) that exists. The rest of the cells next to loan payments not yet made should be blank. 

Dim queryLoanRepayments = (From lp In proxifundContext.LoanRepayments Group 
Join lr In proxifundContext.LoanReceipts On lr.LoanPaymentId Equals
lp.LoanPaymentId Into paymentlist = Group From lr In 
paymentlist.DefaultIfEmpty() Select lp.InstallmentNo, lp.InstallmentAmount, 
lp.Penalty, lp.PaymentDate, If(lr.ReceiptDate Is Nothing, String.Empty, 
lr.ReceiptDate), If(lr.Amount Is Nothing, String.Empty, lr.Amount)).ToList()

When I run the query above intellisense underlines the two If statements with the message "Range variable name can be inferred only from a simple or qualified name with no arguments".

What am I doing wrong?

Upvotes: 0

Views: 61

Answers (2)

Alastair W
Alastair W

Reputation: 101

Thanks Gert Arnold. You pointed me in the right direction. The actual statement that worked was:

Dim queryLoanRepayments = (From lp In proxifundContext.LoanRepayments Group 
Join lr In proxifundContext.LoanReceipts On lr.LoanPaymentId Equals 
lp.LoanPaymentId Into paymentlist = Group From lr In 
paymentlist.DefaultIfEmpty() 
Select lp.LoanApplicationId, 
       lp.InstallmentNo, 
       lp.InstallmentAmount, 
       lp.Penalty, 
       lp.PaymentDate, 
       ReceiptDate = If(lr.ReceiptDate = Nothing, String.Empty, CStr(lr.ReceiptDate)), 
       Amount = If(lr.Amount = Nothing, String.Empty, CStr(lr.Amount))).ToList()

Upvotes: 0

Gert Arnold
Gert Arnold

Reputation: 109089

You should give the the calculated properties a name yourself, because VB can't infer them (as it does from a simple property):

...
Select lp.InstallmentNo, 
       lp.InstallmentAmount, 
       lp.Penalty,
       lp.PaymentDate,
       ReceiptDate = If(lr.ReceiptDate Is Nothing, String.Empty, 
       lr.ReceiptDate),
       Amount = If(lr.Amount Is Nothing, String.Empty, lr.Amount)

Upvotes: 1

Related Questions