I have a query which is getting a list of objects based on a join (irrelevant for this question) and filtering on if the created date is within the last 60 days. This is working. What I want to do is:

1. know how many objects are in the query WITHOUT the createdDate where clause, plus return a list of ResultObj WITH the createdDate where clause. The return object would look more like this in my mind:

public class QueryResult 
{
    public long TotalPossibleCount {get; set;}
    public List<ResultObj> Results {get; set;}
}

IQueryable<ResultObj> res = 
    from tA in ctx.TableA
    join tB in ctx.TableB on tA.Id equals tb.CustId
    where tA.Id == 12345 && 
        tB.CreatedDate >= DateTime.Now.AddDays(-60)
    select new ResultObj
    {
        // some object properties
    };

return await res.OrderByDescending(x => x.CreatedDate).ToListAsync();

Can this be done in one query? Get the count of all possible if I didn't have the tb.CreatedDate >= ... plus the list of data objects with it?

CodePudding user response：

When most items will satisfy tB.CreatedDate >= DateTime.Now.AddDays(-60) you could get them all, get Count and then filter the last step in memory.

But usually you will want

IQueryable<ResultObj> res = 
    from tA in ctx.TableA
    join tB in ctx.TableB on tA.Id equals tb.CustId
    where tA.Id == 12345;

long count = await res.CountAsync();

// restart the query or start a new one
var results = res
  .Where(tB.CreatedDate >= DateTime.Now.AddDays(-60))
    select new ResultObj
    {
        // some object properties
    }
   .OrderByDescending(x => x.CreatedDate)
   .ToListAsync();

CodePudding user response：

You can do that with the following query and with one roundtrip to database:

var taFiltered = ctx.TableA.Where(x => x.Id == 12345);

var joined = 
    from tA in taFiltered
    join tB in ctx.TableB on tA.Id equals tb.CustId
    select new { tA, Tb };
    
var res = 
    from tA in taFiltered
    select new QueryResult
    {
        TotalPossibleCount = joined.Count(),
        Results = ctx.TableB
            .Where(tB => tA.Id == tB.CustId 
                tB.CreatedDate >= DateTime.Now.AddDays(-60)
            .OrderByDescending(x => x.CreatedDate)
            .Select(x => new ResultObj
            {
                 // some object properties
            }
            .ToList()
    };

return await res.FirstOrDefautAsync();