LINQ to SQL: Implementing the IN clause

Question

I've been trying to implement the IN clause in linq to sql. The SQL statement is:

SELECT KitId,PartId
FROM Kits k 
WHERE  k.PartId IN( 
    SELECT Id 
    FROM Parts p 
    WHERE CustomerId > 1 AND CustomerId < 100

I have read a number of SO entries on this, and have so far:

var subquery = (from p in Parts where p.Id > 1 && p.Id < 100 select new {p.ID}).ToArray();

var query = (from k in Kits
            where subquery.Contains(k.PartId)           
            select k).ToList();

There are compiler errors in the second statement: Delegate 'System.Func' does not take 1 arguments Cannot convert lambda expression to type 'string' because it is not a delegate type 'AnonymousType#1[]' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.Queryable.Contains(System.Linq.IQueryable, TSource)' has some invalid arguments Instance argument: cannot convert from 'AnonymousType#1[]' to 'System.Linq.IQueryable'

Substituting into the second statement the array: int[] subquery = {1,2,3};

Results in success. How would I manipulate the first array to arrive at a simple integer array?

EDIT: Altering the first statement to the below statement executes the second:

var subquery = from p in ctx.Parts
                            where p.CustomerId > 1 && p.CustomerId < 10
                            select p.Id

Nathan A · Accepted Answer · 2014-05-14 17:45:04Z

3

Define your subquery like this:

var subquery = (from p in Parts where p.Id > 1 && p.Id < 100 select p.ID).ToArray();

You want the actual ID, not an anonymous type with the single value of ID.

You shouldn't need the ToArray() part either, and the query should be faster if you define it as

var subquery = from p in Parts where p.Id > 1 && p.Id < 100 select p.ID

so that only one DB call is made instead of two.

answered May 14, 2014 at 17:45

Nathan A

11.4k4 gold badges53 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark McWhirter Over a year ago

I will need to add more to the where clause later, so I will not always arrive at the same result each time. I understand that I want the actual ID, I just don't know how to modify the statement to get it.

Nathan A Over a year ago

I already did that for you. You do not want to use the new {} clause.

Habib · Accepted Answer · 2014-05-14 17:49:53Z

3

You can do that in one statement like:

var query = Kits.Where(k=> Parts
                            .Where(p=> p.Id > 1 && p.Id <100)
                            .Select(r=> r.Id))
                   .Contains(k.PartId);

You are getting the error because you are selecting anonymous type with select new {p.ID}, you can remove the error by not selecting an anonymous type instead do select p.ID. Also no need for ToArray call, let lazy evaluation of LINQ do the work for you.

answered May 14, 2014 at 17:49

Habib

224k30 gold badges420 silver badges446 bronze badges

3 Comments

Nathan A Over a year ago

True, but for a beginner, separating the clauses is simpler, and as long as ToArray isn't used, there is no noticeable difference in speed

Habib Over a year ago

@NathanA, True, but less lines of code means less lines to manage :)

Mark McWhirter Over a year ago

I am also looking at this statement, and am trying modify for use in Entity Framework and ObjectSet<Kits> and ObjectSet<Parts>. Any suggestions?

Collectives™ on Stack Overflow

LINQ to SQL: Implementing the IN clause

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related