Not able to use IN query in LINQ with Entity Framework

Question

I am using EF Framework to retrieve the data from SQL DB.

Sub Request Table looks like below:

In this table "org_assigneddept" is foreign key to another Department Table.

I have list of Departments as Input and I want to retrieve only those rows from DB whose org_assigneddept is matching the list.

Please find my whole code:-

   private List<EventRequestDetailsViewModel> GetSummaryAssignedDeptEventRequests(List<EmpRoleDeptViewModel> vmDept)
            {
                List<EventRequestDetailsViewModel> vmEventRequestDeptSummary = new List<EventRequestDetailsViewModel>();

                RequestBLL getRequestBLL = new RequestBLL();
                Guid subRequestStatusId = getRequestBLL.GetRequestStatusId("Open");

                using (var ctxGetEventRequestSumm = new STREAM_EMPLOYEEDBEntities())
                {
                    vmEventRequestDeptSummary = (from ers in ctxGetEventRequestSumm.SubRequests                                                                                    
                                                 where vmDept.Any(dep=>dep.DeptId == ers.org_assigneddept)  
                                                 select new EventRequestDetailsViewModel
                                                 {
                                                     SubRequestId = ers.org_subreqid
                                                 }).ToList();
                }
           }

It is giving the following error at the LINQ Query level:-

System.NotSupportedException: 'Unable to create a constant value of type 'Application.Business.DLL.EmpRoleDeptViewModel'. Only primitive types or enumeration types are supported in this context.'

Please let me know as how can I achieve the result

Answer 1

You cannot pass the department VMs to SQL, it doesn't know what those are.

// Extract the IDs from the view models.. Now a list of primitive types..
var departmentIds = vmDept.Select(x => x.DeptId).ToList();

then in your select statement...

..
where departmentIds.Contains(id=> id == ers.org_assigneddept)
..