Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1208
  • Last Modified:

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
0
pipelineconsulting
Asked:
pipelineconsulting
  • 2
  • 2
1 Solution
 
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
 
joriszwaenepoelCommented:
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
 
pipelineconsultingAuthor Commented:
Seems to do the trick! There is probably a more elegant solution but for now it is fine.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now