I am working on a query in Linq that essentially connects two databases and multiple tables. The first query works fine and is then created as a List. I then join this list to the second database using Linq which works once I do not try to access any fields in the second database table.
The working example allows me to use the group in an expression but always return true.
Working:
Dim Transactions = (From ft In folioTransactions
Group Join t1 In (
From t In kdb.TaggedTransactions
Where t.FolioNumber = Me.FolioNumber
) On ft.TrnNo Equals t1.TrnNo Into g = Group
Select New grid_Transaction With {
.TrnNo = ft.TrnNo,
.PostingDate = ft.PostingDate,
.Name = ft.Name,
.Reference = ft.Reference,
.Comment = ft.Comment,
.AmountPurMemo = ft.AmountPurMemo,
.SubFolioNo = ft.SubFolioNo,
.IsTagged = If(g IsNot Nothing, True, False)
}).ToList
Once I try to access a field by extending that expression it throws a Null Exception.
Not Working:
Dim Transactions = (From ft In folioTransactions
Group Join t1 In (
From t In kdb.TaggedTransactions
Where t.FolioNumber = Me.FolioNumber
) On ft.TrnNo Equals t1.TrnNo Into g = Group
Select New grid_Transaction With {
.TrnNo = ft.TrnNo,
.PostingDate = ft.PostingDate,
.Name = ft.Name,
.Reference = ft.Reference,
.Comment = ft.Comment,
.AmountPurMemo = ft.AmountPurMemo,
.SubFolioNo = ft.SubFolioNo,
.IsTagged = If(g IsNot Nothing, If(g.FirstOrDefault.KTBarcode IsNot Nothing, True, False), False)
}).ToList
Not all the fields need to be evaluated and I tried accessing by simply using FirstOrDefault and SingleOrDefault and still does not allow me to access the group fields without throwing a Null Exception
Not Working:
Dim Transactions = (From ft In folioTransactions
Group Join t1 In (
From t In kdb.TaggedTransactions
Where t.FolioNumber = Me.FolioNumber
) On ft.TrnNo Equals t1.TrnNo Into g = Group
Select New grid_Transaction With {
.TrnNo = ft.TrnNo,
.PostingDate = ft.PostingDate,
.Name = ft.Name,
.Reference = ft.Reference,
.Comment = ft.Comment,
.AmountPurMemo = ft.AmountPurMemo,
.SubFolioNo = ft.SubFolioNo,
.KTBarcode = If(g IsNot Nothing, g.FirstOrDefault.KTBarcode, Nothing)
}).ToList
If anyone can point me in the right direction would be great. I am fairly new to VB but not sure if this is specific to VB or how linq works.