Select Single Parent with Multiple Children to Custom Object
I am trying to create a custom object using a select statement that includes parent / child (1: n) tables. I tried to group the selected objects but I am having problems with the solution.
My data will be in this format:
ParentTable
Userid | Col1 | Col2 | ... | Coln
ChildTable:
Id | Userid | Col1 | Col2 | ... | Coln
The result object I am trying to create will be as follows:
Custom { UserID, IEnumerable<ChildTable> }
I wrote a request:
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
}
This 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 items by username and create a custom object in the form Custom {UserID, IEnumerable} where I can serialize the objects after completion. Any help is appreciated. Edit: As far as the data structure is concerned, it cannot be changed as I am connecting to a third party system, so I am working with what is given.
source to share
Although not a perfect solution, I decided to group the object after the request was made, but before the data was serialized. I tried to apply the "group by" clause, but I don't have any aggregates to apply as I was not trying to find the maximum sum or the sum of the children, not the group. My (unrefined) solution looks like this using my original query, but fits 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 appreciate the suggested input. If anyone has a more elegant solution to this problem where I don't have to iterate over objects before creating a custom class, let me know. Ideally, I would not need to create a "ChildDataDomainModelClass", and on my request, I would select the new "ResultDataClass". Hope this helps others facing this problem. If I come up with a solution, I will update this post accordingly. I appreciate all the participants. Thank.
source to share
I think you may be looking for 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 a groupby request without a loop, 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();
source to share