Get Total Records Count and Custom No of Records in One Database Hit

Question

I am trying to implement custom paging in Ext.js grid, I need to get the total records for my table and the records according to page size. I am using a compiled query for this.

But I want to give just one database hit.

    List<MyTable> .GetRecordsById(Int32 ID, Int32 start, Int32 limit)
    {
        return CompliedGetRecordsById_CustomPaging(_dbNavigation, ID, start, limit).ToList();
    }

    /// <summary>
    /// Complied query for retrieving manage tasks Custom Paging
    /// </summary>
    public static readonly Func<SomeEntities, Int32,Int32, Int32, IQueryable<MyTable>> CompliedGetTaskByProjectId_CustomPaging =
        CompiledQuery.Compile<SomeEntities, Int32, Int32, Int32, IQueryable<MyTable>>((dbNavigation, ID, start, limit) =>
                                        dbNavigation.SiteTasksMappings.Include("TaskMaster")
                                        .Where(x=> x.myTableID == ID && x.STMIsActive == 1).OrderBy(x=>x.STMID).Skip(start).Take(limit));

This will only give the records only according to the arguments, I want to return total records and custom no of records from here

score 3 · Accepted Answer · answered Nov 27 '13 at 07:06

3

I guess you are using MS-SQL Server? Well this cannot be done. You will at least need a subselect for count. But that would lead you to a anonymous result type which is kind of ugly.

I recommend you to write a view for such Tables which adds a total count field. Note that MS SQL Server supports write operations for views which only addresses one table. If you have more simply write some stored procedures and assign them to the entity model of the view.

answered Nov 27 '13 at 07:06

sra

23,820
7
55
89

thanks for the reply, i have tried with reference to this answer(http://stackoverflow.com/questions/7767409/better-way-to-query-a-page-of-data-and-get-total-count-in-entity-framework-4-1), as i don't have SQL profiler i am not able to verify it though, that its giving only one hit – Vinay Pratap Singh Bhadauria Nov 27 '13 at 07:10
1

@Rex Well take a look what the query optimizer does with the query and how it executes... Anyway my point is that you should go with views in such cases. It is now up to you if you are using subselects or grouping. The point is that the Database (or even you) can optimize the query at a early state. Your query is at some point always new for the database – sra Nov 27 '13 at 07:18

Get Total Records Count and Custom No of Records in One Database Hit

1 Answers1