-3

When I try to get all users with department name they belong, Using ASP.Net MVC I keep getting this error in this line:

DepartmentName = db.Departments.Where(d => d.DepartmentId == u.DepartmentId).Select(n =>n.DepartmentName).ToString()

From what I searched in google, I think I have to perform two queries but I don't know how to do it. This is my ViewModel

public class UserListViewModel
    {
        public string Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string RoleName { get; set; }
        public string DepartmentName { get; set; }

    }

And Action

public ActionResult Users()
        {

            List<UserListViewModel> model = new List<UserListViewModel>();

            model = UserManager.Users.Select(u => new UserListViewModel
            {

                Id = u.Id,
                FirstName = u.FirstName,
                LastName = u.LastName,
                Email = u.Email,

               DepartmentName = db.Departments.Where(d => d.DepartmentId == u.DepartmentId).Select(n =>n.DepartmentName).ToString()

            }).ToList();

Thank you in advance!

Nkosi
  • 235,767
  • 35
  • 427
  • 472
Helen Tekie
  • 515
  • 1
  • 6
  • 23
  • 2
    `UserManager` and `db` are two different contexts. Try moving the `DepartmentName` selection out of your query to get the user - just get it after you have the user's id. – Dido Nov 25 '17 at 19:45
  • @Dido Thank you for responsing but how can I fetch Users and Departments separatly, can you please show me? I tried Before Like this DepartmentName = db.Departments.Where(d => d.DepartmentId == ?????).Select(n =>n.DepartmentName).ToString() , But d => d.DepartmentId with what? – Helen Tekie Nov 25 '17 at 20:04

1 Answers1

2

The linq to entities query gets translated in to sql and it gets executed in the sql server, the above code is using two different context instances in single query i.e. UserManager and db and the framework does not allow having two different database contexts in a single query.

you can fetch Users and Departments separately in memory and then organize them in one collection or you would need to use same context to query the data (using db for both Users and Departments) and then you can fetch both results in single request to database server.

Hope it helps.

Ehsan Sajjad
  • 61,834
  • 16
  • 105
  • 160
  • Thank you for responsing but how can I fetch Users and Departments separatly, can you please show me? I tried Before Like this DepartmentName = db.Departments.Where(d => d.DepartmentId == ?????).Select(n =>n.DepartmentName).ToString() , But d => d.DepartmentId with what? – Helen Tekie Nov 25 '17 at 20:04
  • second approach is more suitable in your use case using same context class will allow you to fetch using the query you currently have, i mean use `db` object for both `Users` and `Departments` – Ehsan Sajjad Nov 25 '17 at 20:06
  • Thank you I realy don't know why I used context instead of db. Now it's working with db. – Helen Tekie Nov 25 '17 at 20:13
  • `db` is actually object of `DbContext` class, what's wrong was using different context classes in same query. Glad to help btw. – Ehsan Sajjad Nov 25 '17 at 20:15