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
pipelineconsultingAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
joriszwaenepoelConnect With a Mentor Commented:
Sorry,

Since cb is already a customerID, I meant:

For each cb in Callbacks
    Customers = From c in Customers Where c.ID <> cb
Next
0
 
joriszwaenepoelCommented:
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.
0
 
pipelineconsultingAuthor Commented:
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.
0
 
pipelineconsultingAuthor Commented:
Seems to do the trick! There is probably a more elegant solution but for now it is fine.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.