Performing a subquery in Entity Framework Core

Question

I'm trying to convert some common queries that we use to an API call using EF Core but I can't seem to get it working.

The SQL that will typically be run will looking something like this:

select * 
from my_table 
where convert(date, datefield) = (select date 
                                  from another_table 
                                  where name == 'my_string')

My attempts at converting this into Linq have been fruitless. I've tried something like this and similar variants.

public async Task<my_table> GetStuff()
{
    return await _context.my_table
                         .Where(m => _context.another_table
                                             .Where(a => a.name == "my_string")
                                             .Select(a => a.date).Equals(m.updatedate)).FirstAsync();
}

There error I get is:

System.InvalidOperationException: The LINQ expression 'DbSet<my_table>()
    .Where(t => DbSet<another_table>()
        .Where(t0 => t0.name == "my_string")
        .Select(t0 => t0.date).Equals((object)t.updatedate))' could not be translated.

I've seen some posts about using Include or joining, but these tables are not related.

Any help would be appreciated. Thanks!

Answer 1

The first thing to mention is that even SQL allows the following

where convert(date, datefield) = (select date 
                                  from another_table 
                                  where name == 'my_string')

it will fail if the right subquery returns more than one result. Because in general subqueries (so is the LINQ "collections") return sequences, not single value.

So the safer and better approach would be to use either IN

where convert(date, datefield) in (select date 
                                  from another_table 
                                  where name == 'my_string')

or EXISTS

where exists (select * from another_table 
    where name == 'my_string' and date == convert(date, my_table.datefield))

Both these translate naturally to LINQ - IN to Contains

_context.my_table
    .Where(m => _context.another_table
        .Where(a => a.name == "my_string")
        .Select(a => a.date)
        .Contains(m.updatedate) // <--
    )

and EXISTS to Any

_context.my_table
    .Where(m => _context.another_table
        .Any(a => a.name == "my_string" && a.date == m.updatedate) // <--
    )