Geek asks and answers

Linq query for complex type return

I have a pretty standard model for users and roles:

+-------+   +-----------+   +-------+
| Users |   | UserRoles |   | Roles |
+-------+   +-----------+   +-------+
| ID (P)| < | UserID (P)|   | ID (P)|
| ...   |   | RoleID (P)| > | ...   |
+-------+   +-----------+   +-------+

I am using Entitiy framework as my ORM, and I am trying to build - in one Linq request - my ViewModel defined 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 decided 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 causes 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 the storage expression.

I have not worked with Linq much, so I am pretty sure that my request is somehow wrong. Can someone show me where my request goes wrong and if there is a better way to accompany what I am trying to achieve?

!

+3
3

linq-to-entity, :

.ToList()?

.Select() IEnumerable, .

ToList() , IList , #. , Linq to Entities

?

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

LINQ, EF LINQ, , L2E SQL. , EF , , LINQ 2.

, , FirstOrDefault. , IQueryable.AsEnumerable(); , LINQ 2 , .

- ToList, , ; , Users , IQueryable.Select EF. , , RoleDetail.Users , .

, , EF, , AsEnumerable :

var query = Context.Roles
           .Include("Users")
           .Where(r => r.ID == id)
           .AsEnumerable()
           .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();
+2

It looks like he is trying to execute ToList in the database and does not know how to do it. I would try changing it to ToArray or not, leaving it as IEnumerable.

+1
source

More articles:


All Articles