CODEWITHSUNDEEP
c#asp.netlinqentity-framework-4
Ankit
Ankit

Reputation: 6654

EF 4.0 make batch query to get count of resultset but return only top 5 records

Is there a way to get count of resultset but return only top 5 records while making just one db hit instead of 2 (one for count and second for data)

Upvotes: 2

Views: 403

Answers (2)

EBarr
EBarr

Reputation: 12026

There is not a particularly good way to do this in Entity Framework, at least as of v4. @Tobias writes a single LINQ query, but his suspicions are correct. You'll see multiple queries roll by in SQL Profiler.

Ignoring EF for a minute, this is a relatively complicated problem for SQL Server. Well, it's complicated once your data size gets large or your query gets complicated. You can get a flavor for what's involved here.

With that said, I wouldn't worry about it being 2 queries just yet. Don't optimize until you know it is an actual performance problem. You'll likely end up working around EF, maybe using the EF extensions and creating a stored proc that can take advantage of windowed functions and CTE's. Or maybe it will just return two result sets in a single procedure.

Upvotes: 3

Nuffin
Nuffin

Reputation: 3972

This little query should do the trick (I'm not sure if that's really just one physical query though, and it could be that the grouping is done in the code rather than in the DB), but it's definitely more convenient:

var obj = (from x in entities.SomeTable
          let item = new { N = 1, x }
          group item by item.N into g
          select new { Count = g.Count(), First = g.Take(5) }).FirstOrDefault();

Nonetheless, just doing this in two queries will definitely be much faster (especially if you define them in one stored procedure, as proposed here).

Upvotes: 1

Related Questions