Populating a nested list within a linq to sql query

Question

Keeping it simple: I'd like to populate a custom type with a list in it.

List<ParentModel> parentModelList = 
    (from c in _context.children
    join p in _context.parents on c.ParentId equals p.ParentId
    orderby c.BirthDate
    where p.GenderId = gender.GenderId // Could be a male parent or female or whatever
    group c by p into childGroup
    select new ParentModel()
    {
            ParentId = p.ParentId,
            ChildList = childGroup.Select(x => new ChildModel()
            {
                ChildId = childGroup.Key.ChildId,
                ChildName = childGroup.FirstOrDefault().ChildName,
            }).ToList()
    }).ToList();

You can probably guess where my issue is. I can't call ToList() in the middle of a linq to Entities query otherwise I'd get this runtime Exception:

A type that implements IEnumerable cannot be initialized in a LINQ to Entities query

I'm connecting to a MySql database using EF6 where context is my dbContext. Below are the stores:

public class ParentModel
{
    public int ParentId { get; set; }
    // other properties...
    List<ChildModel> ChildList { get; set; }
}

public class ChildModel
{
    public int ChildId { get; set; }
    public string ChildName { get; set; }
}

So, how would I write this query in order to retrieve information as expected? I'd prefer not to drop out of the operation and run a foreach over the results. Any enlightenment would be greatly appreciated.

Can you edit your question and explain how you want to use the .ToList() when you say I can't call ToList() in the middle of a linq to SQL query? — Paul Sinnema
– Paul Sinnema, Commented Jul 29, 2022 at 20:44
(You say LINQ to SQL, but your error says Entity Framework: those are two different things.) — StriplingWarrior
– StriplingWarrior, Commented Jul 29, 2022 at 20:49
@PaulSinnema alright. I'll try do so. If I'm to do so in short over here, however: Calling ToList() in the middle of a link to SQL query results in an exception. Objects that inherit off IEnumerae are only permitted at the end of the query. — Tim
– Tim, Commented Jul 29, 2022 at 20:51
Normally .ToList() works fine in Entity Framework. This could be due to your MySQL database connector (I've heard bad things about the official MySQL connector for Entity Framework). But the error you're reporting usually happens when you try to initialize a property as a new List<...>(). Is it possible that in "keeping it simple" you omitted a line of code which does that? — StriplingWarrior
– StriplingWarrior, Commented Jul 29, 2022 at 20:53
@Tim: Do you understand the concept of LINQ? In the first part of your query upto the .ToList() you are creating an in memory query tree. At the moment you execute the .ToList() the query is executed and the result resolved. — Paul Sinnema
– Paul Sinnema, Commented Jul 29, 2022 at 20:54

Svyatoslav Danyliv · Accepted Answer · 2022-08-01 10:33:36Z

1

It is called Eager Loading query. You don't have to do grouping, just projection:

var query = 
    from p in _context.parents
    where p.GenderId = gender.GenderId // Could be a male parent or female or whatever
    select new ParentModel
    {
        ParentId = p.ParentId,
        ChildList = p.children.Select(x => new ChildModel
        {
            ChildId = x.ChildId,
            ChildName = x.ChildName,
        }).ToList()
    };

var parentModelList = query.ToList();

answered Aug 1, 2022 at 10:33

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Populating a nested list within a linq to sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related