Converting SQL subquery with IN clause to LINQ expression

Data and Databases
1

I’m working with a database that has three related tables:

  1. Files (FileID, FileName)
  2. Categories (CategoryID, CategoryName)
  3. FileCategories (FileID, CategoryID)

I need to retrieve all files from the Files table based on a specific CategoryID that gets passed as a parameter.

My current SQL approach uses a nested query:

SELECT * FROM Files 
WHERE FileID IN (
    SELECT FileID FROM FileCategories WHERE CategoryID = 5
);

How can I convert this SQL statement into a LINQ query? I’ve been struggling with the syntax for handling the subquery part. Any help would be really appreciated!

2

you could also use Any() which is cleaner: context.Files.Where(f => context.FileCategories.Any(fc => fc.FileID == f.FileID && fc.CategoryID == categoryId)) - reads more naturally and skips the two-step process. performance is similar to Contains but some ppl find it easier to understand.

3

have you thought about trying a join instead? something like context.Files.Join(context.FileCategories, f => f.FileID, fc => fc.FileID, (f, fc) => new { f, fc }).Where(x => x.fc.CategoryID == categoryId).Select(x => x.f) - curious if this works better or if there’s a reason for the subquery?

4

Just use the Contains method - it’s basically the same as your SQL IN clause: var fileIds = context.FileCategories.Where(fc => fc.CategoryID == 5).Select(fc => fc.FileID); var files = context.Files.Where(f => fileIds.Contains(f.FileID)).ToList(); This does exactly what your SQL subquery does. Contains generates an IN clause behind the scenes, so you get the same performance. I’ve used this tons of times in production and it handles parameter substitution cleanly when you swap out that hardcoded 5 for your actual parameter.