× {{alert.msg}} Never ask again
Get notified about new tutorials RECEIVE NEW TUTORIALS

EntityFramework: Optimizing queries

Aydin Adn
Apr 15, 2016
<p>When executing your queries using Entity Framework, select only the properties you need, <br> <br> <span style="color:#0000FF"><small><span style="background-color:#FFFFFF">this.AppContext.Users.ToList();</span> </small></span></p> <p>Will get translated into this:</p> <p><span style="color:#0000FF"><small>SELECT [t0].[Id], [t0].[FirstName], [t0].[LastName], [t0].[RegistrationDate], [t0].[Email],<br>        [t0].[EmailConfirmed], [t0].[PasswordHash], [t0].[SecurityStamp], [t0].[PhoneNumber],<br>        [t0].[PhoneNumberConfirmed], [t0].[TwoFactorEnabled], [t0].[LockoutEndDateUtc],<br>        [t0].[LockoutEnabled], [t0].[AccessFailedCount], [t0].[UserName]<br> FROM [AspNetUsers] AS [t0]<br> GO</small></span></p> <p>Yeah... it aint pretty! To select specific columns, use the <span style="color:#0000FF"><small>IQueryabe&lt;T&gt;.Select</small></span> extension method. Example: </p> <p><span style="color:#0000FF"><small>this.AppContext.Users.Select(user =&gt; new { user.FirstName, user.LastName, user.Email}).ToList();</small></span></p> <p>Which gets translated into this:</p> <p><small><span style="color:#0000FF">SELECT [t0].[FirstName], [t0].[LastName], [t0].[Email]<br> FROM [AspNetUsers] AS [t0]</span></small></p> <p> </p> <p>No more extra baggage!</p> <hr> <p>The next important thing to consider is paging. You don't want to be requesting a 1000 items from the database in a single go, instead you can page the query by using <span style="color:#0000FF"><small>.Skip(number of items * total pages).Take(number of items)</small></span> .</p> <p><span style="color:#0000FF"><small>var userQuery = this.AppContext.Users.Select(user =&gt; new { user.FirstName, user.LastName, user.Email});<br> var users = users.Skip(page * itemsPerPage).Take(itemsPerPage).ToList(); </small></span></p> <p>Or, you could use this little helper library instead: <a href="https://github.com/AydinAdn/Pager">https://github.com/AydinAdn/Pager</a></p> <p> </p>

Get New Tutorials Delivered to Your Inbox

New tutorials will be sent to your Inbox once a week.

comments powered by Disqus