Additional conditions to join

Question

Fourth line in the query returns multiple 'RitsId' currently. I want to select one 'RitsId' from it, by adding below two conditions.

Need to filter by 'date' and 'points'

1. where 'points' is not equal to 10 (where rits.Points != 10)
2. querying by latest 'date' from rits table return single record (for eg: .OrderByDescending(rits => rits.RitsDate)

May I know how do I add these conditions in the below query? I am still a beginner. Thank you.

  var query = from ot in DbContext.tableOT
              join rp in DbContext.tableRP on ot.OTId equals rp.OTId 
              join rits in DbContext.tableRITS on rp.RitsId equals rits.RitsId  
  // I get multiple RitsId as result in the below line, should add conditions to get a single RitsId of my choice
              join ri in DbContext.tableRI on rits.RId equals ri.RId 
              where ri.ItemNo == itemno

            select new pt()
                          {
                              ...
                          }).FirstOrDefaultAsync(); 

        
 return await query;

Answer 1

Try the following query:

var query = 
    from ri in DbContext.tableRI
    where ri.ItemNo == itemno
    from rits in DbContext.tableRITS
      .Where(rits => rits.Points != 10 && ri.RId == rits.RId)
      .OrderByDescending(rits => RitsDate)
      .Take(1)
    join rp in DbContext.tableRP on rits.RitsId equals r.RitsId 
    join ot in DbContext.tableOT on rits.OTId equals ot.OTId 
    select new 
    {
        // ... maybe other properties
        ot.Description
    };

return await query.FirstOrDefaultAsync();