Solved

LINQ and NOT IN / Contains Subquery Problem

Posted on 2010-08-26
4
1,195 Views
Last Modified: 2013-11-11
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
0
Comment
Question by:pipelineconsulting
  • 2
  • 2
4 Comments
 
LVL 10

Expert Comment

by:joriszwaenepoel
Comment Utility
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
 

Author Comment

by:pipelineconsulting
Comment Utility
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
 
LVL 10

Accepted Solution

by:
joriszwaenepoel earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:pipelineconsulting
Comment Utility
Seems to do the trick! There is probably a more elegant solution but for now it is fine.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now