CODEWITHSUNDEEP
asp.net-mvcentity-framework
smc develpments
smc develpments

Reputation: 145

Selecting list for drop down ASP.NET MVC

In my database, I have a table called Employee and it has columns 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 user level equal to the 4.

enter image description here

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 query 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?

Upvotes: 0

Views: 181

Answers (2)

Topher
Topher

Reputation: 1029

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.

Upvotes: 1

Serge
Serge

Reputation: 43860

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

Upvotes: 0

Related Questions