Entity FrameWork SQL Query

Question

How to get Total row Count & the records from the below query?

Contracts cont = db.contracts.SqlQuery("SELECT TOP (20) * 
  FROM (SELECT 
            ROW_NUMBER() OVER (ORDER BY dbo.Contracts.expDate desc) As RowID, 
            dbo.Contracts.*, 
            TotalRows=COUNT(*) OVER() 
        FROM dbo.Contracts  
        Where dbo.Contracts.cancelled = 1) as temp 
  WHERE temp.RowID >" + 20).ToList();

I'm getting the records but don't know how to get the Total row Count. Can Any body suggest best method to get the Total row Count & the records from the above query?

Erik Funkenbusch · Accepted Answer · 2013-07-01 08:07:06Z

2

Your code won't work because you're returning a list of Contracts AND a count, but you're trying to assign it to only a Contracts. You need to project to an anonymous type, or create a custom type to project to that includes both the count and a collection of Contracts.

Why do you insist on using a sql query? This should do the same thing.

var contracts = (from x in db.contacts where x.cancelled == 1 
                 orderby x.expDate descending 
                 select new { Count=x.Count(), Records=x.Skip(20).Take(20) }).ToList();

Unless you want the total rows without the where clause, in which case it would be:

var contracts = (from x in db.contacts orderby x.expDate descending 
                 select new { Count=x.Count(), 
                   Records=x.Where(y => y.canceled == 1).Skip(20).Take(20) }).ToList();

edited Jul 1, 2013 at 8:07

answered Jul 1, 2013 at 7:51

Erik Funkenbusch

93.7k29 gold badges201 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Erik Funkenbusch Over a year ago

@user2254395 - "getting error" doesn't help. Since I don't have access to your actual source code, I can't know the real names of objects and their properties.. you will have to figure that out, because YOU have access to the code.

Collectives™ on Stack Overflow

Entity FrameWork SQL Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related