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

You do need to create a new object. IQueryable is immutable. Don't worry this is how you are supposed to do it. This is how the queries are formed internally. All the extension methods like "Where" don't actually change the object. They just return a new one.

The code that you claim works should not work. The method doesn't even have a type.

Answer 2

i mean you can write this sample :

opportunites =  from opp in oppDC.Opportunities
join org in oppDC.Organizations on opp.OrganizationID equals org.OrgnizationID
select new
                        {
                            opp.OpportunityID,
                            opp.Title,
                            opp.PostedBy,
                            opp.Address1,
                            opp.CreatedDate,
                            org.OrganizationName
                        };
if(condition)
{
   opportunites  = opportunites.Where(opp => opp.Title.StartsWith(title));
}
//------Other Condition you need
if(!String.IsNullOrEmpty(title))
{
    opportunites  = opportunites.Where(.....);
}

if(!String.IsNullOrEmpty(name))
{
    opportunites  = opportunites.Where(.....);
} 

Answer 3

As others have pointed out, you do not need a new object. Your syntax for OrderByDescending is wrong though, you need to specify the key selector.

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