Select Single Parent with Multiple Children into Custom Entity

Question

I am trying to produce a custom entity using a select statement that involves tables a parent/child relation (1:n). I made an attempt to group the objects selected, but I am having trouble coming up with a solution.

My data would be this format:

ParentTable
Userid | Col1 | Col2 | ... | Coln

ChildTable:
Id | Userid | Col1 | Col2 | ... | Coln

The result object I am trying to produce would be this:

Custom { UserID, IEnumerable<ChildTable> }

I have written a query:

    from parent in db.ParentTable
    join child in db.ChildTable on new { Userid = parent.Id } equals new { Userid = child.Userid } into child_join
    from child in child_join.DefaultIfEmpty()
    where
         child.Col1 == Argument1 &&
         child.Col2 == Argument2
    select new {
         Username = parent.Username,
         child.Col1,
         child.Col2,
         child.Col3,
         child.Coln 
   }

That will return this result:

Username | Child Col1 | Child Col2 | Child Col3 | .. |Child Coln
asdf        1              11           22        ..     33
asdf        2              22           33        ..     44
asdf        3              33           44        ..     55
qwer        4              44           55        ..     66
qwer        5              55           66        ..     77
qwer        6              66           77        ..     88
zxcv        7              77           88        ..     99
zxcv        8              88           99        ..     00

I would like to achieve this:

Username | IEnumerable<Child>
asdf     |     {{1             11           22        ..     33}
         |      {2             22           33        ..     44}
         |      {3             33           44        ..     55}}
qwer     |     {{4             44           55        ..     66}
         |      {5             55           66        ..     77}
         |      {6             66           77        ..     88}}
zxcv     |     {{7             77           88        ..     99}
                {8             88           99        ..     00}}

I am trying to group the items by the username and produce a custom object in the form of Custom { UserID, IEnumerable } where I can serialize the objects once completed. Any help is appreciated. Edit: Regarding the data structure, it can not be changed as I am connecting to a 3rd party system so I am working with what is given.

Which LINQ provider are you using? LINQ to SQL? Entity Framework? Something else? — svick, Jan 31 '13 at 02:46
Linq to SQL. I am connecting to a MySql database using devart LinqConnect. — kryptonkal, Jan 31 '13 at 21:53

ptfaulkner · Answer 1 · 2013-02-01T14:32:58.720

4

I think maybe what you are looking for here is a subquery. I would try something like this:

from parent in db.ParentTable
select new {
  Username = parent.Username,
  Children = (from child in db.ChildTable
              where child.UserId == parent.UserId
              select child)
}

If you just want to populate your list from your groupby query without looping you can do:

List<ResultDataClass> returnData = dataResult.GroupBy(item => new KeyValuePair<int, string>(item.UserID, item.UserName), item => item)
                            .Select(rdc => var newdata = new ResultDataClass(userData.Key.Key, userData.Key.Value, userData.Select(item => item.TrackData))).ToList();

edited Feb 01 '13 at 14:32

answered Jan 31 '13 at 02:30

ptfaulkner

712
4
14

Thanks for the response, but that query would result in all entries in the parent table. The arguments I pass in are values within the child table, and I would only need the tables entries that contain these values. The query you supplied would give me all the users, and if the parent does not have any children, it would be null. I am trying to limit the size of the query; serializing such a large dataset would not be ideal. – kryptonkal Jan 31 '13 at 21:52

score 0 · Accepted Answer · answered Aug 14 '14 at 22:53

Although this is not an ideal solution, I decided to group the object after I had executed the query, but prior to serializing the data. I tried to apply a 'group by' clause, but I do not have any aggregates to apply as I was not attempting to find a max or sum of the child items, rather a straight group. My (unrefined) solution looks like so, using my original query, but placed into a custom entity class:

from parent in db.ParentTable
join child in db.ChildTable on new { Userid = parent.Id } equals new { Userid = child.Userid }
where
      child.Col1 == Argument1 &&
      child.Col2 == Argument2
select new ChildDataDomainModelClass {
      Username = parent.Username,
      child.Col1,
      child.Col2,
      child.Col3,
      child.Coln 
}

 //I had grouped the object like this, where the object type returned on the grouping is of type IEnumerable<IGrouping<KeyValuePair<int, string>, ChildDataDomainModelClass>>:

List<ResultDataClass> returnData= new List<ResultDataClass>();
var groupedByUserName = dataResult.GroupBy(item => new KeyValuePair<int, string>(item.UserID, item.UserName), item => item);
        foreach (var userData in groupedByUserName)
        {
             var newdata = new ResultDataClass(userData.Key.Key, userData.Key.Value, userData.Select(item => item.TrackData)); //TrackData is what I have named the Child Data that was returned
             returnData.Add(newdata);
        }

I do appreciate the input offered. If anyone has a more elegant solution to this problem where I do not have to loop over the objects prior to creating the custom class, let me know. Ideally, I would not have to create the 'ChildDataDomainModelClass' and through my query, I would select new 'ResultDataClass'. I hope this would help others that have encountered this problem. If I come up with a solution, I will update this post accordingly. I appreciate everyone's input. Thanks.

Select Single Parent with Multiple Children into Custom Entity

2 Answers2