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? — Tyler Lee
– Tyler Lee, Commented Aug 1, 2016 at 20:47

Jeff Mercado · Accepted Answer · 2016-08-01 20:48:33Z

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 1, 2016 at 20:48

Jeff Mercado

136k34 gold badges268 silver badges282 bronze badges

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

2 Comments

Alex Over a year ago

What is the ctx part? How do I define that?

Jeff Mercado Over a year ago

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.

rhaben · Accepted Answer · 2016-08-02 01:20:33Z

0

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.

edited Aug 2, 2016 at 1:20

answered Aug 2, 2016 at 0:48

rhaben

1572 silver badges10 bronze badges

Collectives™ on Stack Overflow

Linq NOT IN query - based on SQL query

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related