Linq query producing incorrect 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 { a.b[0].SerialNumber } into d
             select new
             {
               Id = d.Select(x => x.b[0].Id),
               SerialNumber = d.Select(x => x.b[0].SerialNumber),
               // This part is not producing the correct output.
               ImportTable = d.Select(w => w.c.Table
                                              .AsEnumerable()
                                              .GroupBy(y => y.Field("SERIAL NUMBER"))
                                              .Select(z => z.First())
                                              .CopyToData‌​Table())
             };

filledTable in my linq query is a List<dynamic> which is populated by what the values are returned from a sproc and distinctList is a List<DataRow> which I distinct the values coming from the DataTable as follows:

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

My Linq query produces 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"
          }
        ]
      ]
    }
  ]
]
}]

So the ImportTable node only contains the information matching to the serial number in the above FilleTabledList node. Everything else seems to work as expected by the Linq query apart from this. Can someone tell me where I'm going wrong please

Update:

My filledTable contains two items as follows:

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

Eventually I will have more items in the list but just to figure out why more linq query isn't working I have narrowed it down to just to items

Answer 1

I created a fiddle, which makes it easier to communicate the available data and the expected results.

When I understood it correctly you like to get a list of all products, listed in the filledTable and then find all elements with the same serial number from the dataTable.

If this is correct, than the LINQ query has to 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 am not really sure, but would 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 { a.b[0].SerialNumber } into d
                      select new
                      {
                          Id = d.Select(x => x.b[0].Id),
                          SerialNumber = d.Select(x => x.b[0].SerialNumber),
                          ImportTable = d.Select(w => w.c.Table.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 can be used:

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 output and deal with most strange data combinations that are handled by the original query.