Pull back records that include child records

Question

I am trying to pull back records that include child records for the View I'm putting together. Right now I have a working Linq statement, but it's big, and I think I could make it cleaner.

var reqMatsInc = (from rm in db.RequiredMaterial
                    join p in db.Project on rm.ProjectTemplateId equals p.ProjectTemplateId
                    where p.ProjectId == id
                    select new RequiredMaterialViewModel
                    {
                        RequiredMaterialId = rm.RequiredMaterialId,
                        ProjectTemplateId = rm.ProjectTemplateId,
                        RequiredMaterialName = rm.RequiredMaterialName,
                        RequiredMaterialDescription = rm.RequiredMaterialDescription,
                        CustomFormId = rm.CustomFormId,
                        AcceptFile = rm.AcceptFile,
                        RequiredMaterialRoles = (
                        from rmr in db.RequiredMaterialRole
                        where rmr.RequiredMaterialId == rm.RequiredMaterialId
                        select new RequiredMaterialRoleViewModel
                        {
                            RequiredMaterialRoleId = rmr.RequiredMaterialRoleId,
                            RequiredMaterialId = rmr.RequiredMaterialId,
                            RoleId = rmr.RoleId,
                            RoleName = rmr.Role.RoleName,
                            IsActive = rmr.IsActive
                        }
                        ).ToList()
                    }
);

Right now the ViewModel classes are pretty much mirror images of the Code First class they represent. db is my DbContext.

Here are some of the ViewModel classes:

RequiredMaterialViewModel

public class RequiredMaterialViewModel
{
    public Guid RequiredMaterialId { get; set; }

    public Guid ProjectTemplateId { get; set; }

    public ProjectTemplate ProjectTemplate { get; set; }

    [Display(Name = "Required Material Name")]
    public string RequiredMaterialName { get; set; }

    [Display(Name = "Required Material Description")]
    public string RequiredMaterialDescription { get; set; }

    public Guid? CustomFormId { get; set; }

    public CustomForm CustomForm { get; set; }

    public bool AcceptFile { get; set; }

    public ICollection<RequiredMaterialRoleViewModel> RequiredMaterialRoles { get; set; }

    public bool IsActive { get; set; }
}

RequiredMaterialRoleViewModel:

public class RequiredMaterialRoleViewModel
{
    public Guid RequiredMaterialRoleId { get; set; }

    public Guid RequiredMaterialId { get; set; }

    public int RoleId { get; set; }

    public string RoleName { get; set; }

    public bool IsActive { get; set; }
}

I can just get the RoleName for this View; that simplifies it some.

Answer 1

The fact that your ViewModel is almost an identical copy of your entity class is a code smell. ViewModel should represent data as it is to be shown on screen, that means that most properties should be simple types (string, int and bool mostly). Weed them out first, I don't believe you need all the IDs either!

Your repository doesn't do any real projection, so you can get rid of the entire select clause. Use eager loading (Include()) instead of joins (your domain and/or EF config might need to be modified):

var reqMatsInc = (from rm in db.RequiredMaterial.Include(rm => rm.RequiredMaterialRoles)
                  join p in db.Project on rm.ProjectTemplateId equals p.ProjectTemplateId
                  where p.ProjectId == id).

Then, have another method in another class (preferrably in your UI layer) map the result to your ViewModels.

(may I suggest AutoMapper to handle this task for you, it was made for exactly these kind of things).