Join two datatables

[AndyAndromeda]

Hi everyone, i'm trying to join two datatables, everything i can find online suggests using LINQ, which is fine but i find it's confusing to understand what it is that the examples are actually doing.

I have one datatable that contains "Inventory" and is named as such, and another datatable that has "CountVarience" named the same as well.

Both datatables have a column with "product code", i need to add the "product descriptions" column from the "Inventory" table to the "CountVarience" table where the product codes are the same. The end result can be an entirely new datatable that i will then bind to a datagridview.

I do need to add more columns then just this one, but i figured if i could just get the one working i should be able to repeat it for all the other columns i need to join in the tables.

Any guidance on how to achieve this correctly would be great

[jmcilhinney]

Any particular reason that you can just do the join in the database and populate a DataTable with the result? There may well be a reason but I want to check that you have one first. If you don't then you're just making life more complicated for yourself for no reason. That said, a LINQ query would be very similar to a SQL query if you're simply joining two lists, so if you can do one then you should at least be able to make a start on the other.

[AndyAndromeda]

Hi Jmcilhinney,

one set of data comes from a .MDB file and the other set of data is from a .CSV export, so i have moved both sets into separate datatables, and would like to join the two. I don't have a way outside of the application im writing to be able to modify the .csv or the .mdb. if that makes sense. so my application must join them for the user.

[jmcilhinney]

E.g.

Dim parentTable As New DataTable
 
With parentTable.Columns
    .Add("ParentId", GetType(Integer))
    .Add("ParentName", GetType(String))
End With
 
With parentTable.Rows
    .Add(1, "Parent 1")
    .Add(2, "Parent 2")
    .Add(3, "Parent 3")
End With
 
Dim childTable As New DataTable
 
With childTable.Columns
    .Add("ChildId", GetType(Integer))
    .Add("ChildName", GetType(String))
    .Add("ParentId", GetType(Integer))
End With
 
With childTable.Rows
    .Add(1, "Child 1.1", 1)
    .Add(2, "Child 1.2", 1)
    .Add(3, "Child 1.3", 1)
    .Add(4, "Child 2.1", 2)
    .Add(5, "Child 2.2", 2)
    .Add(6, "Child 2.3", 2)
    .Add(7, "Child 3.1", 3)
    .Add(8, "Child 3.2", 3)
    .Add(9, "Child 3.3", 3)
End With
 
'Create a deferred query.
Dim query = From parentRow In parentTable.AsEnumerable()
            Join childRow In childTable.AsEnumerable()
            On parentRow.Field(Of Integer)("ParentId") Equals childRow.Field(Of Integer)("ParentId")
            Select New With {.ParentId = parentRow.Field(Of Integer)("ParentId"),
                             .ParentName = parentRow.Field(Of String)("ParentName"),
                             .ChildId = parentRow.Field(Of Integer)("ChildId"),
                             .ChildName = parentRow.Field(Of String)("ChildName")}
 
'Create a generic List of an anonymous type.
Dim list = query.ToList()

The demonstrates a basic join and shows how it's very similar to a SQL join. This example creates a List(Of T) where T is an anonymous type. You can bind that list directly to a grid or whatever you like. You could also define your own type if you need to be able to access its members outside the code that creates the instances, which you can't really do with an anonymous type. If you want a DataTable then you'll have to create one yourself. There is a CopyToDataTable method that is part of LINQ to DataSet but you need DataRows to begin with in order to call that, and you can't have DataRows without a DataTable.

[AndyAndromeda]

so i have just tired to implement this for my case,

but i'm unclear on how i bind this to my datagridview correctly. I can use, DGV.Datasource = list

but then the datagridview does not let me sort any of the columns as i would normally be able to with data from my datatables.

[jmcilhinney]

A List(Of T) has a Sort method. If you want to sort the grid with a bound List(Of T) then you need to do the sorting programmatically and call that method appropriately. If you want the automatic sorting built into the DataView class then you'll need to populate a DataTable instead of creating a List(Of T). I'm not going to go into details of programmatic sorting here because this thread is about joining DataTables, which is a completely separate topic.

[AndyAndromeda]

So you answer sort of works for me, this is the code i have created:

 Dim query = From CountedRow In acc.GetDataTable("STOCK").AsEnumerable()
                        Join InventoryRow In acc.GetDataTable("INVXLS").AsEnumerable()
            On CountedRow.Field(Of String)("Product Code") Equals InventoryRow.Field(Of String)("Product Code")
                        Select New With {.ProductCode = CountedRow.Field(Of String)("Product Code"),
                                        .Model = InventoryRow.Field(Of String)("Model"),
                                        .FullDescription = InventoryRow.Field(Of String)("Description"),
                                        .Class = CountedRow.Field(Of String)("CLASS"),
                                        .BatchNumber = CountedRow.Field(Of String)("Batch Number"),
                                        .OriginalCost = CountedRow.Field(Of String)("Original Cost"),
                                        .CountedCost = CountedRow.Field(Of String)("Counted Cost"),
                                        .CostVarience = CountedRow.Field(Of String)("Cost Variance"),
                                        .OriginalSOH = CountedRow.Field(Of String)("Original SOH"),
                                        .Counted = CountedRow.Field(Of String)("Counted"),
                                        .Variance = CountedRow.Field(Of String)("Variance")}

This will populate the list, and it will pull in the Model, and Inventory Names from the , where the product codes match.

the only problem i have, is that if on my STOCK datatable a product code appeared 7 times, because that product has 7 different "batches"
the output list is also gives me each single batch, seven times over. so i end up with 7 x 7 for my output list.
v
is it the type of JOIN in using or something in the query itself that is causing that? From my understanding in SQL terms its a LEFT JOIN i want to perform, where i get all the results from STOCK datatable and matching records from INVENTORY.

i appreciate your help

[jmcilhinney]

This code is performing an inner join. The point of an inner join is to provide one record in the output for every pair of records in the two inputs that match on the criteria you specify. Doing an outer join instead is not going to help you because outer joins generate at least as many records in the output as inner joins and possibly more. Your problem is that you're not defining a parent/child relationship between the two inputs. What exactly do you expect the output to look like? If each of the 7 records in one input is only supposed to match 1 record in the other output rather than all 7 with the same Product Code then obviously you haven't specified sufficient criteria in your On clause. If you expect the data to be matched by more than just the Product Code then you have to specify that in your query, because it won't happen due to wishful thinking. What criteria do you actually want to match the data on?

[jmcilhinney]

BTW, I know I said I wouldn't but here's an example of programmatic sorting:

Private items As New List(Of Tuple(Of String, Integer))
Private ReadOnly sortOrderByColumnIndex As New Dictionary(Of Integer, SortOrder)
 
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    items.AddRange({Tuple.Create("A", 2),
                    Tuple.Create("B", 4),
                    Tuple.Create("C", 1),
                    Tuple.Create("D", 3)})
 
    BindingSource1.DataSource = items
    DataGridView1.DataSource = BindingSource1
 
    For Each column As DataGridViewColumn In DataGridView1.Columns
        column.SortMode = DataGridViewColumnSortMode.Programmatic
        sortOrderByColumnIndex.Add(column.Index, SortOrder.None)
    Next
End Sub
 
Private Sub DataGridView1_ColumnHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.ColumnHeaderMouseClick
    Dim columnIndex = e.ColumnIndex
    Dim sortOrder = sortOrderByColumnIndex(columnIndex)
 
    Select Case columnIndex
        Case 0
            If sortOrder = SortOrder.Ascending Then
                items.Sort(Function(x, y) y.Item1.CompareTo(x.Item1))
                sortOrderByColumnIndex(columnIndex) = SortOrder.Descending
            Else
                items.Sort(Function(x, y) x.Item1.CompareTo(y.Item1))
                sortOrderByColumnIndex(columnIndex) = SortOrder.Ascending
            End If
        Case 1
            If sortOrder = SortOrder.Ascending Then
                items.Sort(Function(x, y) y.Item2.CompareTo(x.Item2))
                sortOrderByColumnIndex(columnIndex) = SortOrder.Descending
            Else
                items.Sort(Function(x, y) x.Item2.CompareTo(y.Item2))
                sortOrderByColumnIndex(columnIndex) = SortOrder.Ascending
            End If
    End Select
 
    BindingSource1.ResetBindings(False)
End Sub

Note that you can't use an anonymous type unless you want to use Reflection in the comparisons. You also need to bind via a BindingSource, because the change in the underlying List won't be reflected in the grid automatically. Finally, this code does not display the sorting glyphs on the column headers. I'll leave that to you.

[AndyAndromeda]

hi jmcilhinney

i would only be able to use the product code as the thing to match the two, as its the only field the is the same between them.

but if the STOCK.csv file has list of product codes, and those product codes appear more then once due to their batches

for example

+---------------+-------+---------+--------------+
| Product Code  | Batch | Counted | Original SOH |
+---------------+-------+---------+--------------+
| Apples        |  2252 |      10 |           10 |
| Apples        |  2253 |       4 |            4 |
| Apples        |  2265 |       0 |            2 |
+---------------+-------+---------+--------------+

So in the CSV file the row for apples appears 3 times.

i want the out out to pull the model name and description from the inventory file.

+--------------+-------------+---------------------+-------+---------+--------------+
| Product Code |    Model    |    Description      | Batch | Counted | Original SOH |
+--------------+-------------+---------------------+-------+---------+--------------+
| Apples       | Red Apples  | Grown In Australia  |  2252 |      10 |           10 |
| Apples       | Red Apples  | Grown In Australia  |  2253 |       4 |            4 |
| Apples       | Red Apples  | Grown In Australia  |  2265 |       0 |            2 |
+--------------+-------------+---------------------+-------+---------+--------------+

there would still only be the 3 rows, but model and description would now be added.

Is it possible to do a "SELECT TOP" like in SQL? and just pull the TOP model and description and match that to my rows in the STOCK datatable?

[jmcilhinney]

In that case, it's not the inventory table you want to join with at all. What you want is to create a new list/table that contains distinct Product Code/Model/Description combinations first, then join that with the stock table. Without having tested, I believe that you should be able to change this:

Dim query = From CountedRow In acc.GetDataTable("STOCK").AsEnumerable()
            Join InventoryRow In acc.GetDataTable("INVXLS").AsEnumerable()
            On CountedRow.Field(Of String)("Product Code") Equals InventoryRow.Field(Of String)("Product Code")
            Select New With {.ProductCode = CountedRow.Field(Of String)("Product Code"),
                             .Model = InventoryRow.Field(Of String)("Model"),
                             .FullDescription = InventoryRow.Field(Of String)("Description"),
                             .Class = CountedRow.Field(Of String)("CLASS"),
                             .BatchNumber = CountedRow.Field(Of String)("Batch Number"),
                             .OriginalCost = CountedRow.Field(Of String)("Original Cost"),
                             .CountedCost = CountedRow.Field(Of String)("Counted Cost"),
                             .CostVarience = CountedRow.Field(Of String)("Cost Variance"),
                             .OriginalSOH = CountedRow.Field(Of String)("Original SOH"),
                             .Counted = CountedRow.Field(Of String)("Counted"),
                             .Variance = CountedRow.Field(Of String)("Variance")}

to this:

Dim distinctInventory = From InventoryRow In acc.GetDataTable("INVXLS").AsEnumerable()
                        Select New With {.ProductCode = InventoryRow.Field(Of String)("Product Code"),
                                         .Model = InventoryRow.Field(Of String)("Model"),
                                         .FullDescription = InventoryRow.Field(Of String)("Description")}
                        Distinct
Dim query = From CountedRow In acc.GetDataTable("STOCK").AsEnumerable()
            Join Inventory In distinctInventory
            On CountedRow.Field(Of String)("Product Code") Equals Inventory.ProductCode
            Select New With {.ProductCode = CountedRow.Field(Of String)("Product Code"),
                             .Model = Inventory.Model,
                             .FullDescription = Inventory.FullDescription,
                             .Class = CountedRow.Field(Of String)("CLASS"),
                             .BatchNumber = CountedRow.Field(Of String)("Batch Number"),
                             .OriginalCost = CountedRow.Field(Of String)("Original Cost"),
                             .CountedCost = CountedRow.Field(Of String)("Counted Cost"),
                             .CostVarience = CountedRow.Field(Of String)("Cost Variance"),
                             .OriginalSOH = CountedRow.Field(Of String)("Original SOH"),
                             .Counted = CountedRow.Field(Of String)("Counted"),
                             .Variance = CountedRow.Field(Of String)("Variance")}

[AndyAndromeda]

I gave this a test. but this still gives me the duplicates.

[jmcilhinney]

I wasn't previously aware of this difference (or hadn't considered it sufficiently, at least) but, while C# will compare each property of an anonymous type to determine uniqueness, VB will only compare those properties declared as key properties. That means that you need to declare each property as key in the first query:

Dim distinctInventory = From InventoryRow In acc.GetDataTable("INVXLS").AsEnumerable()
                        Select New With {Key .ProductCode = InventoryRow.Field(Of String)("Product Code"),
                                         Key .Model = InventoryRow.Field(Of String)("Model"),
                                         Key .FullDescription = InventoryRow.Field(Of String)("Description")}
                        Distinct

[AndyAndromeda]

BY GEORGE jmcilhinney you have done it!!!

your a genius, its working now

thank you for all your help today!

[jmcilhinney]

Note that you can't use an anonymous type unless you want to use Reflection in the comparisons.

Another option would be to use late binding. This is one of the few situations in which I'd somewhat approve of that. You should still have Option Strict On at the project level and then you would create a partial class in a file with Option Strict Off. For example, you could do this:

Private items As New List(Of Object)
Private ReadOnly sortOrderByColumnIndex As New Dictionary(Of Integer, SortOrder)
 
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    items.AddRange({New With {.Letter = "A", .Number = 2},
                    New With {.Letter = "B", .Number = 4},
                    New With {.Letter = "C", .Number = 1},
                    New With {.Letter = "D", .Number = 3}})
 
    BindingSource1.DataSource = items
    DataGridView1.DataSource = BindingSource1
 
    For Each column As DataGridViewColumn In DataGridView1.Columns
        column.SortMode = DataGridViewColumnSortMode.Programmatic
        sortOrderByColumnIndex.Add(column.Index, SortOrder.None)
    Next
End Sub

and create, for example, Form1.StrictOff.vb containing this code:

Option Strict Off
 
Partial Public Class Form1
 
    Private Sub DataGridView1_ColumnHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.ColumnHeaderMouseClick
        Dim columnIndex = e.ColumnIndex
        Dim sortOrder = sortOrderByColumnIndex(columnIndex)
 
        Select Case columnIndex
            Case 0
                If sortOrder = SortOrder.Ascending Then
                    items.Sort(Function(x, y) y.Letter.CompareTo(x.Letter))
                    sortOrderByColumnIndex(columnIndex) = SortOrder.Descending
                Else
                    items.Sort(Function(x, y) x.Letter.CompareTo(y.Letter))
                    sortOrderByColumnIndex(columnIndex) = SortOrder.Ascending
                End If
            Case 1
                If sortOrder = SortOrder.Ascending Then
                    items.Sort(Function(x, y) y.Number.CompareTo(x.Number))
                    sortOrderByColumnIndex(columnIndex) = SortOrder.Descending
                Else
                    items.Sort(Function(x, y) x.Number.CompareTo(y.Number))
                    sortOrderByColumnIndex(columnIndex) = SortOrder.Ascending
                End If
        End Select
 
        BindingSource1.ResetBindings(False)
    End Sub
 
End Class

It's still part of the same class so the code still has access to the same members of that class, but late binding is allowed so those Letter and Number properties will not be rejected by the compiler.

That said, if you're going to do this anyway, I'd be tempted to just declare a dedicated class and not use an anonymous type in the first place.

[AndyAndromeda]

if i needed to take the query a step further and match two columns: i currently have:

 Dim distinctInventory = From InventoryRow In acc.GetDataTable("INVXLS").AsEnumerable()
                                    Select New With {Key .ProductCode = InventoryRow.Field(Of String)("Product Code"),
                                         Key .Model = InventoryRow.Field(Of String)("Model"),
                                         Key .FullDescription = InventoryRow.Field(Of String)("Description"),
                                         Key .ON_INVOICE = InventoryRow.Field(Of Integer?)("ON INVOICE"),
                                         Key .BatchNumber = InventoryRow.Field(Of String)("Batch_Number")}
                                    Distinct

            Dim query = From CountedRow In acc.GetDataTable("STOCK").AsEnumerable()
                        Join Inventory In distinctInventory
            On CountedRow.Field(Of String)("Product Code") Equals Inventory.ProductCode
                        Order By CountedRow.Field(Of String)("Class")
                        Select New With {.Class = CountedRow.Field(Of String)("Class"),
                             .ProductCode = CountedRow.Field(Of String)("Product Code"),
                             .Model = Inventory.Model,
                             .Description = Inventory.FullDescription,
                             .Batch_Number = CountedRow.Field(Of String)("Batch Number"),
                             .Original_Cost = CountedRow.Field(Of String)("Original Cost"),
                             .Counted_Cost = CountedRow.Field(Of String)("Counted Cost"),
                             .Cost_Varience = CountedRow.Field(Of String)("Cost Variance"),
                             .Original_SOH = CountedRow.Field(Of String)("Original SOH"),
                             .Counted = CountedRow.Field(Of String)("Counted"),
                             .Variance = CountedRow.Field(Of String)("Variance"),
                             .On_Invoice = Inventory.ON_INVOICE,
                             .INV_Batch_Number = Inventory.BatchNumber}

But my STOCK datatable has a column "Batch Number" where some batch numbers start with "0000" and as such they have been dropped of in the export of the csv.
On the up side the Inventory datatable contains the full batch number,

is there a way to take the batch number column and say where is ends LIKE the one in the inventory file then add that batch number to a new column in the query?

for the sake of checking that the output is correct for now i'd like to keep the existing batch number column and add a new column called "FULL BATCH NUMBER" with the matched up batch number. much the same way the model and description columns were added in.

[jmcilhinney]

You can't have different numbers of columns in each row, but you can decide what goes into each column for a row so it could be NULL or it could be a value. As for a Where clause, its a Where clause. You shouldn't need me to explain how to write a simple Where clause. Not before at least trying for yourself, anyway.

[AndyAndromeda]

hey mate, i think maybe i havnt explained my self very well.

I did try to add a where, but im not sure if im adding this to the top half of the statment with the DISTINCT or the lower half.

I am also not sure if i should be preforming the LINQ statement you helped with first, and then preform another one that just solves the "0000" number problem with the batch column .

for better understanding here is two tables of what i would like to acheive.

CURRENT TABLE

+---------------+-------+---------+--------------+
| Product Code  | Batch | Counted | Original SOH |
+---------------+-------+---------+--------------+
| Apples        |  2252 |      10 |           10 |
| Apples        |  2253 |       4 |            4 |
| Apples        |  2265 |       0 |            2 |
+---------------+-------+---------+--------------+

FINAL OUTPUT TABLE

+---------------+-------+---------+--------------+------------+
| Product Code  | Batch | Counted | Original SOH | Full Batch |
+---------------+-------+---------+--------------+------------+
| Apples        |  2252 |      10 |           10 | 00002252   |
| Apples        |  2253 |       4 |            4 | 00002253   |
| Apples        |  2265 |       0 |            2 | 00002265   |
+---------------+-------+---------+--------------+------------+

the column FULL BATCH is from the "Inventory" table, and WHERE its LIKE the BATCH in the table "stock", it gets added to the FULL BATCH coluumn.

im very much self taught when it comes to SQL, and while the LINQ is very similar the differences are a little confusing, especially with more complex things like the DISTINCT statement.

[jmcilhinney]

I did try to add a where, but im not sure if im adding this to the top half of the statment with the DISTINCT or the lower half.

ALWAYS show us what you tried. We only know what you tell us so, if you don't tell us you've done something, you've done nothing as far as we're concerned. ALWAYS provide a FULL and CLEAR explanation of the problem: what you're trying to do, how you're trying to do it and what happened when you tried.

As for where to put it, as always, think about the logic first and then write the code to implement the logic. The first query gives you a list of distinct inventory records to join to the stock records. Do you want to filter that inventory list? If so then you can put the filter on the first query. If not then you can't. I say that "can" and "can't" because you can filter the inventory in either query - both queries involve inventory - but you can only filter the stock in the second.

[AndyAndromeda]

I appreciate your help, it was late last night when i posted and i was a bit fried from trying to get other various things working when i decided to come back to this.

I did manage last night to add in the Full Batch Coloumn, but it didnt always allign to the same batch that was in that particular row. So i know i need to add the LIKE operator in. and have them match up like that.

I will give it anothr go once i get home from work today

[jmcilhinney]

I've done very few joins in LINQ myself but, without having tested, I think that the Join clause only supports the Equals operator. If you want to use other operators then I think you'd have to use the Where clause. The Where clause accepts any Boolean expression though, so you can use anything that you could use in an If statement, e.g.

Where item1.TextProperty.TrimStart("0"c) = item2.TextProperty

That will include joined items with a similar value in TextProperty, even if item1 has some leading zeroes. That seems to be the sort of thing you're talking about.

[jmcilhinney]

It occurred to me after writing that that it does test for equality so you may be able to include it in the Join clause after all.

[ChrisE]

So you answer sort of works for me, this is the code i have created:

 Dim query = From CountedRow In acc.GetDataTable("STOCK").AsEnumerable()
                        Join InventoryRow In acc.GetDataTable("INVXLS").AsEnumerable()
            On CountedRow.Field(Of String)("Product Code") Equals InventoryRow.Field(Of String)("Product Code")
                        Select New With {.ProductCode = CountedRow.Field(Of String)("Product Code"),
                                        .Model = InventoryRow.Field(Of String)("Model"),
                                        .FullDescription = InventoryRow.Field(Of String)("Description"),
                                        .Class = CountedRow.Field(Of String)("CLASS"),
                                        .BatchNumber = CountedRow.Field(Of String)("Batch Number"),
                                        .OriginalCost = CountedRow.Field(Of String)("Original Cost"),
                                        .CountedCost = CountedRow.Field(Of String)("Counted Cost"),
                                        .CostVarience = CountedRow.Field(Of String)("Cost Variance"),
                                        .OriginalSOH = CountedRow.Field(Of String)("Original SOH"),
                                        .Counted = CountedRow.Field(Of String)("Counted"),
                                        .Variance = CountedRow.Field(Of String)("Variance")}

This will populate the list, and it will pull in the Model, and Inventory Names from the , where the product codes match.

the only problem i have, is that if on my STOCK datatable a product code appeared 7 times, because that product has 7 different "batches"
the output list is also gives me each single batch, seven times over. so i end up with 7 x 7 for my output list.
v
is it the type of JOIN in using or something in the query itself that is causing that? From my understanding in SQL terms its a LEFT JOIN i want to perform, where i get all the results from STOCK datatable and matching records from INVENTORY.

i appreciate your help

I think you have to add Group Join

Dim query = From CountedRow In acc.GetDataTable("STOCK").AsEnumerable()
                     Group Join InventoryRow In acc.GetDataTable("INVXLS").AsEnumerable()
'...more code

something like

  Dim Query = From t1 In datatable1.AsEnumerable() 
      Group Join t2 In datatable2.AsEnumerable()
'...more code

I personally would add the CSV to the .mdb and perform the SQL there

[AndyAndromeda]

Dim distinctInventory = From InventoryRow In acc.GetDataTable("INVXLS").AsEnumerable()
                                    Select New With {Key .ProductCode = InventoryRow.Field(Of String)("Product Code"),
                                         Key .Model = InventoryRow.Field(Of String)("Model"),
                                         Key .FullDescription = InventoryRow.Field(Of String)("Description"),
                                         Key .ON_INVOICE = InventoryRow.Field(Of Integer?)("ON INVOICE"),
                                         Key .BatchNumber = InventoryRow.Field(Of String)("Batch_Number")} 'this batch number just adds the rows in. 
                                    Distinct

            Dim query = From CountedRow In acc.GetDataTable("STOCK").AsEnumerable()
                        Join Inventory In distinctInventory
                        On CountedRow.Field(Of String)("Product Code") Equals Inventory.ProductCode
                        Where Inventory.BatchNumber.TrimStart("0"c) = CountedRow.Field(Of String)("Batch Number")
                        Order By CountedRow.Field(Of String)("Class")
                        Select New With {.Class = CountedRow.Field(Of String)("Class"),
                             .ProductCode = CountedRow.Field(Of String)("Product Code"),
                             .Model = Inventory.Model,
                             .Description = Inventory.FullDescription,
                             .Batch_Number = CountedRow.Field(Of String)("Batch Number"),
                             .Original_Cost = CountedRow.Field(Of String)("Original Cost"),
                             .Counted_Cost = CountedRow.Field(Of String)("Counted Cost"),
                             .Cost_Varience = CountedRow.Field(Of String)("Cost Varience"),
                             .Original_SOH = CountedRow.Field(Of String)("Original SOH"),
                             .Counted = CountedRow.Field(Of String)("Counted"),
                             .Varience = CountedRow.Field(Of String)("Varience"),
                             .On_Invoice = Inventory.ON_INVOICE,
                             .INV_Batch_Number = Inventory.BatchNumber}

i have tried the above and moved the where statement to other places in the LINQ query but i can't get it to work, also i feel the WHERE statement needs something more added to it, in my mind i'm just saying where two things match, i'm not currently saying then add this column in. but i'm not sure how that syntax would look.

[jmcilhinney]

i feel the WHERE statement needs something more added to it, in my mind i'm just saying where two things match, i'm not currently saying then add this column in. but i'm not sure how that syntax would look.

No, that's nothing to do with the Where clause. As I already told you, you can't have different columns in different records in the output. If any record needs a column then every record has to have that column. It's when you set the value of that column, which is actually a property in this case, that you decide what that value should be, e.g.

.SomeProperty = If(row.Field(Of String)("SomeColumn") = someValue, row.Field(Of String)("SomeOtherColumn"), (string)null)