Entity Framework Core Exclude Results

asp.net-core c# entity-framework-core sql-server

Question

Using Entity Framework Core and ASP.NET Core 2.1, how can the results from one table be returned, but only if that row id is NOT found in a second table?

For example, only the first two rows in the Entity1 table should be returned because the third row has a reference stored in the Entity2 table.

Entity1 Table

+-----------+-----------+
| Entity1Id | Name      |
+-----------+-----------+
| 1         | Row One   |
| 2         | Row Two   |
| 3         | Row Three |
+-----------+-----------+

Entity2 Table

+-----------+-----------+-----------+
| Entity2Id | Name      | Entity1Id |
+-----------+-----------+-----------+
| 1         | Row One   | 3         |
+-----------+-----------+-----------+
1
1
9/10/2018 12:22:17 AM

Accepted Answer

You could do...

var result = dbContext.Entity1.Where(w => !w.Entity2.Any()).ToList();

This should return all Entity1 rows where there are no Entity2 records.

2
9/10/2018 2:08:59 AM

Popular Answer

The most straightforward way is to use a subquery that can be described as below :

enter image description here

And now we can translate the formula into the following codes easily :

IQueryable<Entity1> q = _context.Entity1.FromSql(
    @"select * from Entity1 
    where not exists(
        select Entity2.Id from Entity2 where Entity2.Entity1Id = Entity1.Id
    )"
);

Another way to do that is left outer join :

IQueryable<Entity1> q = from i in _context.Entity1
                        join j in _context.Entity2 on i.Id equals j.Entity1Id into jj
                        from x in jj.DefaultIfEmpty()
                        where jj.All(x=> x.Entity1Id!=i.Id)
                        select i ;


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow