I have 3 tables:
Contract, matter and claims. Each table has a 1:N relationship with the following table.
I want to select claim name where contract con_name = 'C109K'
var claims = await _context.Claims.AsNoTracking()
.Include(cl=>cl.Matter)
.ThenInclude(mat=>mat.Contract)
.Where(con=>con.con_name=='C109K')
.Select(cl=>new{name=cl.cl_name})
The error is that the where clause is unable to find con_name because the context is claims.
I also tried the other way round - that is - starting the query from contract. But in that case the select is unable to get claim name since the query context is contract.
How can I correctly write this query?
It's hard to be sure without the model, but I assume this should work:
var claims = await _context.Claims.AsNoTracking()
.Include(cl=>cl.Matter)
.ThenInclude(mat=>mat.Contract)
.Where(cl=>cl.Matter.Contract.con_name=='C109K') // <-- find the contract associated with the claim
.Select(cl=>new{name=cl.cl_name})
The .Include() and .ThenInclude() methods are used to load the related entities (see the documentation on eager loading). They ask EF Core to retrieve the data from the database, and load it in the associated entities.
For example in your case:
var claim = (await _context.Claims.AsNoTracking()).First();
var matter = claim.Matter; // matter will always be null, because it was no loaded
claim = (await _context.Claims.Include(cl=>cl.Matter).AsNoTracking()).First();
matter = claim.Matter; // the claim's matter is loaded thanks to the Include call
var contract = matter.Contract; // contract is null because it was not loaded
claim = (await _context.Claims.Include(cl=>cl.Matter).ThenInclude(mat=>mat.Contract).AsNoTracking()).First();
matter = claim.Matter; // the claim's matter is loaded thanks to the Include call
contract = matter.Contract; // contract is loaded thanks to the ThenInclude call
Those methods allow for granular control over which data is retrieved from your database: sometime you don't need all data, only the top level view.
In your case however, those calls are not needed because you don't retrieve the linked entities: you only need them for filtering, and the filtering is done solely by running the appropriate SQL query.