Linq-Query Equivalent for a SQL-Statement

Question

I have to generate a List of IDs (int) from a table with Linq-Query for specific Conditions (count of specific column = 0).

In SQL having a Statement like:

select id 
from person 
where (select count(*) from mitgliedschaft 
       where mitgliedschaft.person_id = person.id 
         AND (austritt IS NULL OR austritt > getDate())) = 0

Trying to do this with Linq:

List<int> personIDsOhneAktuelleMitgliedschaft = db.mitgliedschaft
    .Where(x => x.deletedFlag == 0 && (x.austritt == null || x.austritt > DateTime.Now))
    .Select(x => x.person_id.Value)
    .ToList();

I dont know how to set it up, to check the count = 0 in the where-part!

Can someone help me please?

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Feb 22 '22 at 21:30

user449689 · Answer 1 · 2022-02-22T12:59:30.403

Based on your LINQ query you can do something like this:

var personIDsOhneAktuelleMitgliedschaft = db.mitgliedschaft
    .Where(x => x.deletedFlag == 0 && (x.austritt == null || x.austritt > DateTime.Now))
    .Select(x => x.person_id.Value);

var result = db.Person
    .Where(x => !personIDsOhneAktuelleMitgliedschaft.Contains(x.Id))
    .Select(x => x.Id);

Structuring your EF model with navigation property will simplify your queries.

score 0 · Answer 2 · answered Feb 22 '22 at 11:53

In Where you have to put LINQ subquery. Exactly as for SQL. Also you do not need Count for such filter NOT EXISTS is enough and it has analogue in LINQ - !Any.

var query = db.Person
    .Where(p => !db.mitgliedschaft.Any(x => x.person_id == p.Id && x.deletedFlag == 0 && (x.austritt == null || x.austritt > DateTime.Now)))
    .Select(p => p.Id);

Linq-Query Equivalent for a SQL-Statement

2 Answers2