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;

Model Below:

Goal is to get 'Description' from tableOT for passed in 'ItemNo'.

 public partial class tableRI 
{
    public string ItemNo { get; set; }
    public Guid RId { get; set; }

}  

// I get multiple 'RitsId' for passed in 'RID', Need to add conditions to get single 'RitsId'
 public partial class tableRITS 
{
    public Guid RitsId { get; set; } 
    public Guid RId { get; set; }
    public DateTime RitsDate{ get; set; }
    public int? Points { get; set; }
}

 // By adding conditions, I can retrieve and pass the single RitsId of my choice below
 public partial class tableRP 
{
    public Guid RitsId { get; set; }
    public Guid? OTId { get; set; }

}

public partial class tableOT
{
    public Guid? OTId { get; set; }
    public string Description { get; set; }

}

CodePudding user response：

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();