CODEWITHSUNDEEP
c#linq
Extrakun
Extrakun

Reputation: 19305

Adding a where/order by clause to an IQueryable

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)

Upvotes: 8

Views: 25903

Answers (4)

Saeed Amiri
Saeed Amiri

Reputation: 22555

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.

Upvotes: 25

Harry Sarshogh
Harry Sarshogh

Reputation: 2197

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

Upvotes: 3

Patko
Patko

Reputation: 4423

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

Upvotes: 0

Stilgar
Stilgar

Reputation: 23551

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.

Upvotes: 3

Related Questions