locked
What is the Linq Query expression to get all the department Id , Name in a select list variable for drop down RRS feed

  • Question

  • I am trying to create the drop down to list the Department name having right to see for the login user. My Linq query is not working, please help I have the following model

     public class Depot
    {
        public  int Id { get; set; }
        public string  DepotName { get; set; }
    
    }
     public class Department
    {
        public int Id { get; set; }
        public string  DepartmentName { get; set; }
    
    }
         
     public class User
    {
        public  int Id { get; set; }
        public string  UserName { get; set; }
    
    }
    public class USerDepotDepartmentLink
    {
        public int UserId { get; set; }
        public int DepotId { get; set; }
        public int DepartmentId { get; set; }
        public IsDefault { get; set; }
    }

    From the above model I have the following records

    Depot Record
    Id = 1; DepotName = Depot1
    Id = 2 ; DepotName = Depot2
    
    Department Record
    Id = 1; DepartmentName = Sales
    Id = 2 ; DepartmentName = Account
    Id = 3 ; DepartmentName = Marketing
    
    User Record
    Id = 1; Username = User1
    Id = 2; Username = User2 
    
    USerDepotDepartmentLink
    UserId = 1, DepotId=1, DepartmentId =1 , IsDefault = 1 
    UserId = 1, DepotId=1, DepartmentId =2 , IsDefault = 0
    
    UserId = 2, DepotId=1, DepartmentId =2 , IsDefault = 0
    UserId = 2, DepotId=1, DepartmentId =3 , IsDefault = 0
    UserId = 2, DepotId=2, DepartmentId =1 , IsDefault = 0
    UserId = 2, DepotId=2, DepartmentId =2 , IsDefault = 1
    UserId = 2, DepotId=2, DepartmentId =3 , IsDefault = 0

    rom the above example for the UserId 1 The dropdown of the Department down list must be 'Sales, Accounts' and selected 'Sales department'

    For the userId 2 , the Department drop down list must be 'Sales, Accounts,Marketing and selected Department is Account.

    I have created  the Linq query given below but not working. I am passing the userid and int[] depotno. the Department must be listed  to that user  and depots

    public IEnumerable<SelectListItem> GetLocationsByDepotForDropdown(int userId, int[] depotNo) 
    {
    List<SelectListItem> locations = (from dpt in goContext.goDepartment
                                                  join dep in goContext.goUserDepartment 
                                                  on dpt.DepartmentID equals dep.DepartmentID
                                                 where dep.UserID == userId
                                                 select new SelectListItem
                                                 {
                                                     Value = dpt.DepartmentID.ToString(),
                                                     Text = dpt.DepartmentName
    						 Selected = isDefault
                                                 }).Where(i => depotNo.Contains(i.Value)).
                                                 Distinct().ToList<SelectListItem>();
                locations.Insert(0, new SelectListItem
                {
                    Value = "0",
                    Text = "-- Select --"
                });
    } 
    

    Please help to get the  Linq Query

    Pol


    polachan


    • Edited by polachan Monday, August 3, 2020 6:53 AM correction
    Monday, August 3, 2020 6:38 AM

All replies

  • Hi polachan,
    Based on your code, there is no DepartmentId in Department.
    So you need to change the following code:

    on dpt.DepartmentID equals dep.DepartmentID

    to 

    on dpt.Id equals dep.DepartmentID

    Best Regards,
    Daniel Zhang


    "Windows Forms General" forum will be migrating to a new home on Microsoft Q&A (Preview)!
    We invite you to post new questions in the "Windows Forms General" forum’s new home on Microsoft Q&A (Preview)!
    For more information, please refer to the sticky post.


    Tuesday, August 4, 2020 6:43 AM