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.

CodePudding user response：

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();