Linq-Query Equivalent for a SQL-Statement-CodePudding

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?

CodePudding user response：

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);

CodePudding user response：

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.