Practical .NET

Speed Up Skip and Take in Entity Framework with Lambda Expressions

If you're using Skip and Take in LINQ to page through your data, a tweak to your syntax can cut your response time by as much as 10 percent as you page through your data.

Imagine that you're paging through your SalesOrder data by repeatedly issuing this query that uses Skip and Take:

Const size As Integer = 10
Dim pos As Integer

Dim sos = db.SalesOrders.Skip(pos).Take(size)
pos += size

Every time you issue this query, you increment the pos variable by the amount in size to move to the "next" set of data.

If you were only executing this query once, the syntax used with Skip and Take is fine. If, however, you're executing the query multiple times (as you do when paging through data), then you're taking on some unnecessary overhead in your repeated queries.

The problem is that LINQ+Entity Framework has probably been creating a new query for every request because it's been inserting the values for pos and size as literals into the query. Instead of recycling the query (which really hasn't changed from one request to another), LINQ+EF has been re-analyzing the expression tree for your LINQ statement and submitting a new SQL statement on each request.

If you're using Entity Framework 6, then you can switch to using lambda expressions with Skip and Take. The benefit here is that LINQ+EF will now recognize your Skip and Take values as parameters that can be passed to a compiled query. The change looks like this in Visual Basic:

Dim sos = db.SalesOrders.Skip(Function() pos).Take(Function() size)

and like this in C#:

var sos = db.SalesOrders.Skip(() => pos).Take(() => size);

Now, LINQ+EF will compile your query once and just re-execute it on each request, passing the new values for pos and size. It's a trivial change to your syntax but it can shave 10 percent off the time it takes your repeated query to run.

About the Author

Peter Vogel is a system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His blog posts on user experience design can be found at http://blog.learningtree.com/tag/ui/.

comments powered by Disqus

Featured

  • Hands On: New VS Code Insiders Build Creates Web Page from Image in Seconds

    New Vision support with GitHub Copilot in the latest Visual Studio Code Insiders build takes a user-supplied mockup image and creates a web page from it in seconds, handling all the HTML and CSS.

  • Naive Bayes Regression Using C#

    Dr. James McCaffrey from Microsoft Research presents a complete end-to-end demonstration of the naive Bayes regression technique, where the goal is to predict a single numeric value. Compared to other machine learning regression techniques, naive Bayes regression is usually less accurate, but is simple, easy to implement and customize, works on both large and small datasets, is highly interpretable, and doesn't require tuning any hyperparameters.

  • VS Code Copilot Previews New GPT-4o AI Code Completion Model

    The 4o upgrade includes additional training on more than 275,000 high-quality public repositories in over 30 popular programming languages, said Microsoft-owned GitHub, which created the original "AI pair programmer" years ago.

  • Microsoft's Rust Embrace Continues with Azure SDK Beta

    "Rust's strong type system and ownership model help prevent common programming errors such as null pointer dereferencing and buffer overflows, leading to more secure and stable code."

  • Xcode IDE from Microsoft Archrival Apple Gets Copilot AI

    Just after expanding the reach of its Copilot AI coding assistant to the open-source Eclipse IDE, Microsoft showcased how it's going even further, providing details about a preview version for the Xcode IDE from archrival Apple.

Subscribe on YouTube

Upcoming Training Events