Selecting list for drop down asp.MVC-CodePudding

In my db there is a table called employee and consists EmpNames and EmpId which same EmpId created User table with user levels. I want to get a list of empNames and id's to who are userlevel equal to the 4.

This is how I got empname list for a drop down list

List<M_Employee> EmpList = db.CreateEmployee.Where(x => x.Status == true).ToList();
List<SelectListItem> EmpDropDown = EmpList.Select(x => new SelectListItem { Text = x.EmpName, Value = x.Id.ToString() }).ToList();

Same way I have tried to quary the user level = 4 and tried to join emp table with user table to get the emp names who assigned user levels to 4 but it didn't work.

Here is my code for that

List<int> TopEmp = db.Master_Users.ToList().Where(r => r.EmpId == int.Parse(db.CreateEmployee.Where(x=> x.Id))).ToList().

Can you help me on this?

CodePudding user response：

Firstly, you need to understand how ToList works.

When you call ToList it means that Entity framework will execute the sql statement constructed at that point and retrieve the results into memory.

You generally want to construct your entire query first and then have that query get all the data you want from the database in the format of an object you want by using .Select(x => x.whatever).ToList(). Otherwise you'll be making multiple calls to the database to get bits of data here and there and then joining them or working with them unnecessarily in memory which is slower than having the database do it.

So your first query where you get the select list items can be rewritten like this:

  List<SelectListItem> EmpDropDown = db.CreateEmployee
                                         .Where(x => x.Status == true)
                                         .Select(x => new SelectListItem { Text = x.EmpName, Value = x.Id.ToString() })
                                         .ToList()

And from what you've described you should be able to rewrite the 2nd query like this:

   List<int> TopEmp = (from u in db.Master_Users
                join e in db.CreateEmployee on u.EmpId equals e.Id
                where u.Level == 4 
                select e.Id
                ).ToList();

This is using a different query syntax but allows to specify the key to join on easily as I don't know how your foreign keys and navigation properties are setup.

CodePudding user response：

I can't see you dbcontext, maybe it is possible to use include too, but for the start try this

List<SelectListItem> EmpDroDown =  (from  emp in db.CreateEmployee
                   join usr in  db.Master_Users  on emp.Id equals usr.EmpId
                  where emp.Status == true && usr.UserLevel==4
                  select new SelectListItem { Text = em.EmpName, 
                  Value = emp.Id.ToString() }).ToList();