Adding a where/order by clause to an IQueryable

Question

I have ths function to query a set of records from the DB:

public IQueryable<PointTransactionViewModel> GetPointTransactions(int UserID)
        {
            return
                (
                    from PointTransaction p in entities.PointTransaction
                    join ActivityLog a in entities.ActivityLog
                    on p.TransactionID equals a.TransactionID
                    where p.UserID == UserID
                    select new PointTransactionViewModel
                    {
                        ID = p.TransactionID,
                        Balance = p.Balance,
                        Points = p.Amount,
                        RelatedActivityID = a.ID,
                        When = p.When,
                        Sender = p.SenderUserInfo.CompleteName
                    }
                );
        }

I wish to add an additional cause, like this

var entries = GetPointTransaction(1);
return entries.OrderbyDescending.Where( x => x.When >= start && w.When <= end).
               ( x => x.When);

However, I seem to need to create a new query from the existing one for this to work. But, I have seem this work before without creating a new query, in the code snippet before:

 public PaginatedList(IQueryable<T> source, int pageIndex, int pageSize)
        {
            PageIndex = pageIndex;
            PageSize = pageSize;
            TotalCount = source.Count();
            TotalPages = (int)Math.Ceiling(TotalCount / (double)PageSize);

            this.AddRange(source.Skip(PageIndex * PageSize).Take(PageSize));
        }

Does the code above somehow doesn't need a new query to be created for the IQueryable source object? Was a temporary object created?

Edit

It's strange, but to get it to work I have to do the following:

IQueryable<ActivityLogEntry> log = activityRepo.GetPointTransaction(userID).
   Where(x => x.PointsEarned == 50);
return log.ToList();

The following will not work:

var log =  = activityRepo.GetPointTransaction(userID);
log.Where( x => x.PointsEarned == 50);
return log.ToList();

There is no error message, just that the where clause seems to be ignored (it is also returning all data which PointsEarned is not 50)

Answer 1

Your entries is of IQueryable type, that's enough and you can add any number of clauses before fetching the data, e.g. before calling the ToList() function. It doesn't execute the SQL code, just an expression tree will be created until you fetch the whole data with one of the existing methods (again, e.g. the ToList() function).

var query = context.Where(x=>x.id == test);
query = query.Where(anotherCondition1);
query = query.Where(anotherCondition2);
...
var result = query.ToList();

it's equal to

var result = context.Where(x=>x.id == test)
                    .Where(anotherCondition1)
                    .Where(anotherCondition2)
                    ....
                    .ToList()

This is called deferred execution, for more details see the MSDN blog post on LINQ and Deferred Execution.