TSQL to LINQ conversion reference?

Question

I'm trying to find a good reference to help me convert this TSQL statement to Linq:

EXAMPLE:

SELECT * FROM Categories WHERE ProductID IN (SELECT ProductID FROM ProductCategories WHERE CatID = 23)

I can't find anywhere that references how to do the WHERE "IN" part.

Answer 1

While it's not exactly a TSQL to LINQ reference, I have found the 101 LINQ Samples page on MSDN helpful for this purpose.

See also this related question

Answer 2

See if this works:

var qry = from c in Categories
          where (from pc in ProductCategories where pc.CatID = 23 select pc.ProductID).Contains(c.ProductID)
          select c;

I don't know if there is a direct translation of the IN WHERE part in LinqToSQL (didn't found any reference) but I replaced that with a Contains.

Answer 3

Using extension methods...

var productIDs = context.ProductCategories
                        .Where( c => c.CatID == 23 )
                        .Select( c => c.ProductID );
var categories = context.Categories
                        .Where( c => productIDS.Contains( c => c.ProductID ) );

EDIT: 101 Linq Samples is a pretty good reference for simple things. For more complicated stuff I find that I usually have to resort to Google. FYI, if you want to search StackOverflow, I've found that it works better to use google and specify site: stackoverflow.com. YMMV.