Linq to Entities query non primitive type

Question

I have this method I wrote as:

private void GetReceivedInvoiceTasks(User user, List<Task> tasks)
        {
            var taskList = from i in _db.Invoices
                           join a in user.Areas on i.AreaId equals a.AreaId
                           where i.Status == InvoiceStatuses.Received
                           select i;

        }

Basically I was trying to get all the Invoices in the database in the user's area that have a status of received. I don't understand LINQ too well right now.

I'm getting the error:

base {System.SystemException} = {"Unable to create a constant value of type 'Models.Area'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."}

Can someone please explain to me what I'm doing wrong and how to remedy this? I can't currently understand what the problem is. If I take the join line out it's fine but I really need that line to make sure I only have invoices from the users area/areas (they can belong to more than one area). Have I got something fundamentally wrong with this query?

Answer 1

Entity framework does not support joins with in-memory collections. You will have to re-wire your query to make use of a Contains query with a collection of primitives instead.

Also EF currently does not support enum values so you will have to compare with an integer value (If InvoiceStatuses.Received is not an enum value ignore this part).

Both fixes combined lead to the following query approach which should result in results equivalent to your join:

int statusReceived = (int)InvoiceStatuses.Received;
var areaIds = user.Areas.Select(x=> x.AreaId).ToArray();

var taskList = from i in _db.Invoices
               where i.Status == statusReceived && areaIds.Contains(i.AreaId)
               select i;