How to use clause Except in queryable context

Question

I'm using Asp.Net Core com Scaffolding generate context and models.
I need to check an except comparison between 2 tables.

In SQL server this work:

SELECT Id FROM AspNetUsers a
EXCEPT 
SELECT UserId FROM OtherClass

But in context not working. I tried this:

How can I implement these parameters?: _context.AspNetUsers.Except()
I tried with .FromSql unsuccessfully

public IActionResult Create()
{
    ViewData["UserId"] = new SelectList(_context.AspNetUsers.FromSql(@"SELECT Id FROM AspNetUsers a EXCEPT SELECT UserId FROM OtherClass"), "Id", "UserName");
    return View();
}

Answer 1

try this

//will generate list of ids to be excluded
var exceptIds = _context.OtherClass.Select(oc => oc.UserId).ToList();

//will generate list of filtered ids
var filteredIds = _context.AspNetUsers.Where(netUser => !exceptIds.Contains(netUser.Id)).Select(x => x.Id).ToList();

this will reverse the Contain which now will generate the list of ids NOT IN in the list of OtherClass.UserId

you could also do it in one query

var filteredIds = _context.AspNetUsers
                          .Where(netUser => 
                                    !_context.OtherClass
                                    .Select(r => r.UserId).Contains(netUser.Id))
                          .Select(filtered => filtered.Id).ToList();