Create a one to many collection from linq result setC#

Question

I have a database table which contains one to many data collection. From that table I need to make a dictionary which represents the data. the key will be the master Id and the values will be the ids associated to that master id. my dataset is like the following

and the expected dictionary should be like the following

I tried the following code but it didnt work as expected. Instead of Dictionary<int,List> it returned Dictionary<key,groupbydata collection>.

 Table.GroupBy(item => item.MasterId).ToDictionary(item => item.Key);

Any thoughts?

Answer 1

I tried the following code but it didnt work as expected. Instead of Dictionary<int,List> it returned Dictionary<key,groupbydata collection>.

There's a basic misunderstanding of LINQ GroupBy here

The output from a GroupBy is an enumeration of IGrouping, which is something like a list of lists. It is not, however, exactly a List<List<something>>

An IGrouping is something that has a property Key, and an associated collection of values. Whatever source collection drove the creation of the grouping, all the values within it are created from some operation on the source object in conjunction with some operation that created the key

In the simplest case, the one you have used, you told GroupBy how to generate a key as a pure single simple value, MasterId which is a property of all the objects in Table. You didn't specify a custom operation to generate a value from the item so the whole item is used as the value

For the example posted your items are a pair of integers, perhaps we could model them like:

record Thing(int MasterId, int AssociatedId);

Grouping by just the MasterId:

GroupBy(t => t.MasterId)

Means you get a result that is like (JSON-esque representation)

  [
    {
      Key: 584753,
      this: [ { MasterId: 584753, AssociatedId: 5 },{ MasterId: 584753, AssociatedId: 4 },{ MasterId: 584753, AssociatedId: 3 } ]
    },

   {
      Key: 584754,
      this: [ { MasterId: 584754, AssociatedId: 4 },{ MasterId: 584754, AssociatedId: 3 } ]
    },
    ...

I say json-esque because json can't really represent something that is an array of objects that also has a property that isn't one of the objects. The closest I can get is to ask you to imagine an object with a default property this that is the array

---

What GroupBy has produced is not a Dictionary<int, List<int>>, it's a "list of IGrouping objects that have a Key property and are also a list of Thing objects that are whole items from the table, both the master id and the associated id" - an IGrouping has a Key and it has items inside, just like an array has a Length and has items inside. We can turn it into a Dictionary but more work is needed first.

The fact that GroupBy is outputting the whole Thing item as the value is a problem because you don't want a Dictionary<int, List<Thing>>

GroupBy has another form, where you can supply a second argument to derive a value from the Thing, instead of using the whole Thing

Table.GroupBy(t => t.MasterId, t => t.AssociatedId);

This time only the AssociatedId is taken for the value of your items in your IGrouping. In json-esque it looks like:

  [
    {
      Key: 584753,
      this: [ 5, 4, 3 ] 
    },

   {
      Key: 584754,
      this: [ 4, 3 ]
    },
    ...

This is much closer to what you want, it's just not a Dictionary, it's a list of IGrouping , and an IGrouping isn't a List

Enter the use of ToDictionary

If you use the single argument form of ToDictionary:

GroupBy(...).ToDictionary(g => g.Key)

you will get a Dictionary<int, IGrouping> - the int comes from the Key being an int, a decision that was made during the grouping operation. The value is an IGrouping because this form of ToDictionary just uses the whole item that was fed in, as a value. The whole item was an IGrouping.

ToDictionary has another form, which takes some code that generates the value as well as the key. You can use this form to turn the IGrouping into a List. Remember that you already have an IGrouping that is full of ints so it's a simple case of

GroupBy(...).ToDictionary(g => g.Key, g => g.ToList());

Giving you an entire expression of:

Table
  .GroupBy(t => t.MasterId, t => t.AssociatedId)
  .ToDictionary(g => g.Key, g => g.ToList());

---

There are, of course, other ways to write this.. You could leave the GroupBy producing IGrouping<Thing> and instead Select the Associated ID out during the ToDictionary

.GroupBy(t => t.MasterId)
.ToDictionary(g => g.Key, g => g.Select(t => t.AssociatedId).ToList())

You could use the form of groupby that runs over the grouped result doing something else:

.GroupBy(t => t.MasterId, t => t.AssociatedId, (k, g) => new { K= k, L = g.ToList() } )
.ToDictionary(g => g.K, g => g.L)

There is always going to be tens of ways to skin this cat.. Most important for you is to appreciate that GroupBy turns a 1D list into a 2D list, which leads me to the footnote...

Footnote about databases

This is what Panagiotis is getting at. LINQ GroupBy is very different to SQL GROUP BY.

In an SQL GROUP BY you choose what things are going to be your key, and your only other option for getting any data out of it that is not key, is to perform an aggregation right there and then, which throws data away

SELECT MasterId, MIN(AssociatedId), MAX(AssociatedId)
FROM Table
GROUP BY MasterId

You simply cannot have the Key, and then all the associated data with SQL GROUP BY. All your rows with the same MasterId are thrown into a bucket with that masterid label on the outside, mixed up and you can only pull data out of the bucket using an aggregate operation like "the maximum AssociatedId, the average (nonsensical as it is) AssociatedId" etc. This mixes your data up because the MAX(AssociatedID) comes from one row, the MAX(OrderItemCount) comes from another row..

LINQ GroupBy clumps together the rows under the common key but then hands you back the set of buckets with all the data still inside, as whole rows of still-in-one-piece data. You can GroupBy in LINQ and then ask for the First() in each group and you get eg 584753,5 -> SQL just doesn't have that concept at all. There is no "first" anything after it's been thrown into a grouping bucket

..which means that your LINQ as expressed here simply cannot be translated to SQL and executed on the server. If you try (on EFCore) you'll get an error "this query has to be done client side" - in some older versions of EF(core and Non-core) the "i'll just pull all the rows to the client and do it there" was automatic, something we've moved away from because automatically downloading a million rows just to find something that the DB can't do is a decision the developer should concretely make

Answer 2

The question is unclear and contains contradictions. EF doesn't have datasets or datatables - it doesn't even have tables. A DbSet isn't a representation of a table, it's a way to map table data to client-side objects.

EF queries are converted to SQL. A GROUP BY doesn't nest values, it aggregates them, returning only one value per group. That's the opposite of what you asked. SQL results are always flat.

On the other hand, it's possible to use LINQ with in-memory ADO.NET DataTable objects the same way it's used with any other container. A DataRow doesn't have named columns though, so LINQ to Dataset functions are used to retrieve specific fields.

Unless you created your DataTable using Visual Studio's designer, which generates custom DataRow-derived classes that expose column values as properties.

Entity Framework

If you use EF you have to load the flat data in memory and nest it afterwards. You should avoid using GroupBy because you don't really want to generate a GROUP BY.

A Dictionary can't have many values per key. You can use ToLookup instead to generate an ILookup object that does.

The following query would nest entire rows:

var lookup= dbContext.OrderItems
                     .Where(...)
                     .ToLookup(o=>o.OrderID);

While this would only load the desired columns

var lookup= dbContext.OrderItems
                     .Where(...)
                     .Select(o=>new {o.OrderItemId,o.OrderId})
                     .ToLookup(o=>o.OrderID);

Answer 3

You need to cast the values inside the grouping to the dictionary

var test = Table.GroupBy(item => item.MasterId).ToDictionary(g => g.Key, v=> v.Select(x => x.InternalValue));

so you got a Dictionary<MasterID, IEnumerable >