Linq Query To Return Complex Type

asp.net-mvc, c#, entity-framework, linq

Solution

Little experience with linq-to-entities, so this is a shot in the dark:

Why do you need .ToList()?

.Select() already returns an IEnumerable, so it should work without.

ToList() returns a List, not an IList or similar interface, but a concrete c# class. So I'd imagine that Linq to Entities cannot translate that into its own behind the scenes syntax

Does this work?

       var query = Context.Roles
               .Include("Users")
               .Where(r => r.ID == id)
               .Select(r => new RoleDetail() {
                  ID = r.ID,
                  Rolename = r.Rolename,
                  Active = r.Active,
                  Users = r.Users
                           .Select(u => new RoleDetail.RoleUser() {
                              ID = u.ID,
                              Username = u.Username
                            })
                            // .ToList()
                })
               .FirstOrDefault();

Problem

I have a pretty standard model for Users and Roles: ``` +-------+ +-----------+ +-------+ | Users | | UserRoles | | Roles | +-------+ +-----------+ +-------+ | ID (P)| < | UserID (P)| | ID (P)| | ... | | RoleID (P)| > | ... | +-------+ +-----------+ +-------+ ``` I am using the Entitiy framework as my ORM and I am trying to construct - in a single Linq query - my ViewModel, definied below: ``` public class RoleDetail { public class RoleUser { public int ID { get; set; } public string Username { get; set; } } public int ID { get; set; } public string Rolename { get; set; } public bool Active { get; set; } public IEnumerable<RoleUser> Users { get; set; } } ``` I figured that the following query should work: ``` var query = Context.Roles .Include("Users") .Where(r => r.ID == id) .Select(r => new RoleDetail() { ID = r.ID, Rolename = r.Rolename, Active = r.Active, Users = r.Users .Select(u => new RoleDetail.RoleUser() { ID = u.ID, Username = u.Username }) .ToList() }) .FirstOrDefault(); ``` However, this throws the following error: LINQ to Entities does not recognize the method 'System.Collections.Generic.List [RolesRepository+RoleDetail+RoleUser] ToList[RoleUser] (System.Collections.Generic.IEnumerable[RolesRepository+RoleDetail+RoleUser])' method, and this method cannot be translated into a store expression. I haven't worked with Linq a lot, so I am quite sure that my query is wrong somehow. Can someone show me where my query is going wrong and if there is a better way to accompish what I'm trying to achieve? Thank you in advance!

Original source