LINQ or lambda expression with subquery and NOT IN logic

Question

I'm trying to wrap my head around the proper syntax for a LINQ query or a lambda expression that returns the equivalent to the following SQL query:

SELECT * 
FROM tool t 
WHERE t.UniqTool 
  NOT IN (SELECT t1.uniqtool  
          FROM tool t1
          INNER JOIN [version] v ON v.UniqTool = t1.UniqTool 
            AND v.IsBetaVersion = 1)

Basically, I want to get the list of tools that don't already have a beta version. I have a tool object that has both the UniqTool (ID value) and a boolean property indicating whether that version is beta.

Here was my initial attempt:

var tools = from t in session.CachedTools where !t.IsBetaVersion select t;

I realized it gave me the wrong result set and I got lost trying to figure out the subquery with NOT IN logic.

Thanks!

Answer 1

Assuming you have a navigation property on your Tool entity this query should get you all the tools that do not have any version that is a beta:

var tools = session.CachedTools.Where(t => !t.Versions.Any(v => v.IsBetaVersion == 1));