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

Steve Py · Accepted Answer · 2017-07-21T23:54:58.057

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)
..

edited Jul 21 '17 at 23:54

answered Jul 21 '17 at 07:37

Steve Py

26,149
3
25
43

The first part of the answer identifying the cause of the issue is correct. However, the proposed usage with `Any` translates differently, to get `IN (...)` translation it should use `Contains` (`where departmentIds.Contains(ers.org_assigneddept)` – Ivan Stoev Jul 21 '17 at 07:49
Thanks a lot for answer. It works perfectly well. I have used Contains to get the result. Is there any significant difference between using Contains() and Any(). – simple user Jul 21 '17 at 08:37
Yes, good pick up, since it's an expression to check the collection of IDs against the ID of the entity it should be .Contains. Updating answer. The difference between .contains and .Any can be found here: https://stackoverflow.com/questions/23526773/what-is-the-difference-between-contains-and-any-in-linq – Steve Py Jul 21 '17 at 23:54

Not able to use IN query in LINQ with Entity Framework

1 Answers1