asked on

LINQ and NOT IN / Contains Subquery Problem

I am trying to return a set of data using LINQ and Entity Framework which will only return items where a related item exists and fulfils a set of criteria.

I have the following code:

'Gets a list of customer IDs which have callbacks set
                Dim Callbacks = (From f In MyContext.Callbacks.Include("Customer") Where (f.TypeId > 0) Select f.Customer.Id).ToList

                'Select customers who DO NOT have callbacks set
                Customers = From c In MyContext.Customers _
                    Where (c.StatusId = 2) _
                    And Not Callbacks.Contains(c.Id) _
                    Select c

Open in new window

However, I am getting an error:

LINQ to Entities does not recognize the method 'Boolean Contains(Int32)' method, and this method cannot be translated into a store expression.

How can I do this with LINQ? I have tried using one LINQ query (with a nested subquery instead of the Callbacks.Contains(c.Id) ) and two LINQ queries as shown above

joriszwaenepoel

You could sole it like this:

Customers = From c In MyContext.Customers _
Where (c.StatusId = 2) _
Select c

For each cb in Callbacks
Customers = From c in Customers Where c.ID <> cb.ID
Next

Then, when the query is executed, the SQL will contain a list of OR operators instead of a single IN operator, but it is the only way I have been able to work around this error.

pipelineconsulting

ASKER

That doesn't quite make sense

For each cb in Callbacks
Customers = From c in Customers Where c.ID <> cb.ID
Next

Compairing c.ID to cb.ID is not correct as they are two different entities. Each customer has several callbacks but I don't think that comparing their primary keys will give me what I need.

I need only the customers that do not have a callback in place.

ASKER CERTIFIED SOLUTION

joriszwaenepoel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pipelineconsulting

ASKER

Seems to do the trick! There is probably a more elegant solution but for now it is fine.