Linq query generating invalid result

Question

Consider the following linq query

 var result = from a in from b in filledTable join c in distinctList on b[0].SerialNumber equals c.Field("SERIAL NUMBER") select new { b, c } group a by new { ab[0].SerialNumber } into d select new { Id = d.Select(x => xb[0].Id), SerialNumber = d.Select(x => xb[0].SerialNumber), // This part is not producing the correct output. ImportTable = d.Select(w => wcTable .AsEnumerable() .GroupBy(y => y.Field("SERIAL NUMBER")) .Select(z => z.First()) .CopyToData‌​Table()) }; 

filledTable in my linq query there is a List<dynamic> , which is filled with the fact that the values ​​are returned from sproc and distinctList , is a List<DataRow> , which I distinguishes the values ​​coming from DataTable as as follows:

 List<DataRow> distinctList = dt.AsEnumerable().Distinct(DataRowComparer.Default).ToList(); 

My linq request calls the following JSON

 [ { "FilledTableList":[ [ { "Id":[ 2 ], "SerialNumber":[ "1073410" ], "ImportTable":[ [ { "SERIAL NUMBER":"1073410", "PRODUCT TYPE":"Product A" }, { "SERIAL NUMBER":"1073411", "PRODUCT TYPE":"Product B" } ] ] }, { "Id":[ -1 ], "SerialNumber":[ "1073411" ], "ImportTable":[ [ { "SERIAL NUMBER":"1073410", "PRODUCT TYPE":"Proeduct A" }, { "SERIAL NUMBER":"1073411", "PRODUCT TYPE":"Product B" } ] ] } ] ] }] 

But I would like the following JSON output

 [ { "FilledTableList":[ [ { "Id":[ 2 ], "SerialNumber":[ "1073410" ], "ImportTable":[ [ { "SERIAL NUMBER":"1073410", "PRODUCT TYPE":"Product A" } ] ] }, { "Id":[ -1 ], "SerialNumber":[ "1073411" ], "ImporTable":[ [ { "SERIAL NUMBER":"1073411", "PRODUCT TYPE":"Product B" } ] ] } ] ] }] 

Thus, the ImportTable node contains information corresponding to the serial number in the FilleTabledList node above. Everything else seems to work as expected using the linq . Can someone tell me where I'm wrong, please

Update:

My filledTable contains two elements:

 { Id = 2, SerialNumber = "1073410"} { Id = -1, SerialNumber = "1073411"} 

In the end, I will have more items in the list, but just to find out why more linq queries don't work, I narrowed it down only to points

Answer 1

I created a violin , which facilitates the transfer of available data and expected results.

When I understand correctly, you like to get a list of all the products listed in the completed table, and then find all the items with the same serial number from the dataTable.

If this is correct, then the LINQ query should be:

  var result = filledTable.GroupJoin(distinctList, product => product.SerialNumber, row => row.Field<string>("SERIAL NUMBER"), (Product, Rows) => new { Product, Rows }) .Select(group => new { Id = group.Product.Id, SerialNumber = group.Product.SerialNumber, ImportTable = group.Rows.CopyToDataTable() }); 

and the result will be

 [ { "Id": 2, "SerialNumber": "1073410", "ImportTable": [ { "SERIAL NUMBER": "1073410", "PRODUCT TYPE": "Product A" } ] }, { "Id": -1, "SerialNumber": "1073411", "ImportTable": [ { "SERIAL NUMBER": "1073411", "PRODUCT TYPE": "Product B" } ] } ] 

Answer 2

I'm not sure, but something like this work?

  var result = (from a in (from b in filledTable join c in distinctList on b[0].SerialNumber equals c.Field<string>("SERIAL NUMBER") select new { b, c }) group a by new { ab[0].SerialNumber } into d select new { Id = d.Select(x => xb[0].Id), SerialNumber = d.Select(x => xb[0].SerialNumber), ImportTable = d.Select(w => wcTable.AsEnumerable() .Where(y=>y.Field<string>("SERIAL NUMBER") == d.Key.ToString()) .GroupBy(y => y.Field<string>("SERIAL NUMBER")).Select(z => z.First()).CopyToData‌​Table()) }); 

Answer 3

Here is a simplified query that you can use:

 var result = from entry in filledTable join row in distinctList on entry[0].SerialNumber equals row.Field<string>("SERIAL NUMBER") group new { entry, row } by entry[0].SerialNumber into items select new { Id = items.Select(x => x.entry[0].Id), SerialNumber = new[] { items.Key }.AsEnumerable(), ImportTable = items.Select(x => x.row).CopyToDataTable() }; 

It should be equivalent to the desired result and deal with most of the strange data combinations that are processed by the original request.