0

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.

B Days
  • 342
  • 3
  • 15
  • 1
    `FirstOrDefault` only prevents an exception while trying to access the first item, so where you use `g.FirstOrDefault.KTBarcode` it can still be `Nothing` and the attempted access of `KTBarcode` will throw the exception. – A Friend Oct 04 '17 at 15:56
  • 1
    If(g IsNot Nothing AndAlso g.FirstOrDefault IsNotNothing, ..... – NoAlias Oct 04 '17 at 15:58
  • Have you try something like: https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/queries/group-join-clause – Alex Alvarez Sep 06 '18 at 00:09

0 Answers0