Linq NOT IN query - based on SQL query

Question

I'm trying to figure out how I can convert this same SQL query into a Linq query, but I'm not seeing a way to do NOT IN with Linq like you can with SQL.

SELECT COUNT(DISTINCT ID) 
FROM References 
WHERE ID NOT IN (
    SELECT DISTINCT ID 
    FROM References
    WHERE STATUS = 'COMPLETED')
AND STATUS = 'FAILED'

I need to know how many distinct [ID] values exist that contain a [Status] value of "FAILED" that do not also have a [Status] of "COMPLETED". Basically, if there is a failed without a completed, i need the distinct amount for that.

var query_5 = from r in Records where r.ID NOT IN(from r in Records where
r.Record_Status == "COMPLETED" ) && (r.Record_Status == "FAILED") 
select r.ID;

var rec_5 = query_5;
Console.WriteLine(rec_5.Distinct());

This was my attempt to do it, but I'm receiving numerous errors as it is not the right way to code it. Any examples on how to accomplish this would be much appreciated!

This is how the rest of my setup is looking.

public class References
{
  public string ID;
  public string Record_Status;
}

public static List<References> Records = new List<References>
{
};

Possible duplicate of [How would you do a "not in" query with LINQ?](http://stackoverflow.com/questions/183791/how-would-you-do-a-not-in-query-with-linq) — Tyler Lee, Aug 01 '16 at 20:47

score 1 · Accepted Answer · answered Aug 01 '16 at 20:48

1

The rough equivalent of a (not) in is using Contains(). Since the inner subquery doesn't reference the outer, you could write it like this:

var completedIds =
    (from r in ctx.References
    where r.Status == "COMPLETED"
    select r.Id).Distinct();

var count =
    (from r in ctx.References
    where !completedIds.Contains(r.ID)
    where r.Status == "FAILED"
    select r.Id).Distinct().Count();

answered Aug 01 '16 at 20:48

Jeff Mercado

129,526
32
251
272

What is the ctx part? How do I define that? – Alex Aug 01 '16 at 21:17
It's whatever data context you're using to run your queries. However in your case, it looks like you could drop that all together. – Jeff Mercado Aug 01 '16 at 21:19

rhaben · Answer 2 · 2016-08-02T01:20:33.133

You could use the Except method:

var completed =
    (from r in References
        where r.Record_Status == "COMPLETED"
        select r.Id).Distinct();

var failed =
    (from r in References
        where r.Record_Status == "FAILED"
        select r.Id).Distinct();

var countFailedNotCompleted = failed.Except(completed).Count();

Note that this does not require using Contains during iteration. The sequences will be compared all at once within the Except method. You could also tack ToArray() on to each of the distinct sequences to ensure minimal iteration in the case where you want to use those sequences more than once.

Linq NOT IN query - based on SQL query

2 Answers2