c# - Kendo MVC ToDataSourceResult extremly slow with large IQueryable -

i have used kendo datasourceresult todatasourceresult(this iqueryable enumerable, datasourcerequest request); extension extensively , never noticed performance issue until when querying table of 40 million records. take 10 query wrote benchmark same request passed in.

this read action:

public actionresult readall([datasourcerequest] datasourcerequest  {     var starttimer = datetime.now;     var context = helpers.eftools.getcadatacontext();     debug.writeline(string.format("{0} : got context", datetime.now - starttimer));      var events = e in context.events                  select                      new models.event()                          {                              id = e.id,                              datetime = e.eventdatetime,                              hostid = e.door.hostid,                              siteid = e.door.host.siteid,                              userid = (int)e.userid,                              username = e.user.firstname + " " + e.user.surname,                              doorid = e.doorid,                              door = e.door.name,                              description = e.description,                              subdescription = e.subdescription                          };     debug.writeline(string.format("{0} : built query", datetime.now - starttimer));      var tenrecods = events.orderbydescending(i => i.datetime).take(10).tolist();     debug.writeline(string.format("{0} : taken 10", datetime.now - starttimer));      var result = events.todatasourceresult(request);     debug.writeline(string.format("{0} : datasource result", datetime.now - starttimer));      return this.json(result); } 

the output debug:

00:00:00.1316569 : got context 00:00:00.1332584 : built query 00:00:00.2407656 : taken 10 00:00:21.5013946 : datasource result 

although query times out. using dbmonitor captured both querys, first manual take 10:

"project1".id, "project1"."c1", "project1".hostid, "project1".siteid, "project1".userid, "project1"."c2", "project1".doorid, "project1"."name", "project1".description, "project1".subdescription ( select      "extent1".id,     "extent1".userid,     "extent1".description,     "extent1".subdescription,     "extent1".doorid,     "extent2"."name",     "extent2".hostid,     "extent3".siteid,      cast("extent1".eventdatetime timestamp) "c1",     "extent4".firstname || ' ' || "extent4".surname "c2"        public.events "extent1"     inner join public.doors "extent2" on "extent1".doorid = "extent2".id     inner join public.hosts "extent3" on "extent2".hostid = "extent3".id     inner join public.users "extent4" on "extent1".userid = "extent4".id )  "project1" order "project1"."c1" desc limit 10 

and todatasourcerequest query:

select  "groupby1"."a1" "c1" ( select count(1) "a1"      public.events "extent1"     inner join public.doors "extent2" on "extent1".doorid = "extent2".id )  "groupby1" 

this datasourcerequest request parameter passed in:

request.aggregates count = 0 request.filters count = 0 request.groups count = 0 request.page 1 request.pagesize 10 request.sorts count = 1 

this result of var result = events.todatasourceresult(request);

result.aggregateresults null result.data count = 10 result.errors null result.total 43642809 

how can datasourceresult events iqueryable using datasourcerequest in more efficient , faster way?

after implementing custom binding (suggested atanas korchev) lots of debug output time stamps, obvious causing performance issue, total count.

looking @ sql captured backs up, don't know why didn't see before.

getting total row count question post answers find here.