0

How can I join two data tables using linq. I want to return all orders with canceled status flag.

Order Table

    ItemNum OrderNum
1   Item1   101
2   Item2   102

Order Log Table

    ItemNum OrderNum    IsCanceled
1   Item2   102         1

Final Result

    OrderNum    ItemNum  IsCanceled
1   101         Item1    0
2   102         Item2    1

Here is the demo;

https://rextester.com/RQQZJ4470

I have tried this so far with no luck;

 Dim result = From b In backOrders.AsEnumerable()
                         Join c In backOrdersCanceledItems.AsEnumerable() On
                    c.Field(Of Integer)("DistNum") Equals b.Field(Of Integer)("DistNum") And c.Field(Of String)("OriginalOrderNum") Equals b.Field(Of String)("OriginalOrderNum")
                         Select New With {.OriginalOrderNum = b.Field(Of String)("OriginalOrderNum"),
                    .DistNum = b.Field(Of Integer)("DistNum"), .ItemNum = b.Field(Of String)("ItemNum"),
                    .IsCanceled = c.Field(Of String)("IsCanceled")}
user1263981
  • 2,953
  • 8
  • 57
  • 98

1 Answers1

0

Sure, you just need to do a left join in LINQ:

Dim result = From b In backOrders.AsEnumerable()
             Group Join c In backOrdersCanceledItems.AsEnumerable() On
                c.Field(Of Integer)("DistNum") Equals b.Field(Of Integer)("DistNum") And c.Field(Of String)("OriginalOrderNum") Equals b.Field(Of String)("OriginalOrderNum") Into cj = Group
             From c In cj.DefaultIfEmpty
             Select New With {
                .OriginalOrderNum = b.Field(Of String)("OriginalOrderNum"),
                .DistNum = b.Field(Of Integer)("DistNum"),
                .ItemNum = b.Field(Of String)("ItemNum"),
                .IsCanceled = If(c?.Field(Of String)("IsCanceled"), "0") }

See my SQL to LINQ Recipe for C# versions.

NetMage
  • 26,163
  • 3
  • 34
  • 55