Entity Framework School System
This application illustrates a series of performance problems which are commonly encountered when using Entity Framework. It is designed to accompany the article at https://www.simple-talk.com/dotnet/.net-tools/entity-framework-performance-and-what-you-can-do-about-it/.
It's built around a SQL Server database with two tables, storing details of Schools and the Pupils who attend them. A WinForms application has an Entity Framework code first model of this database, and some code to access information from it. In all cases, it does this data access inefficiently.
- Clone this repository.
- Create a new SQL Server database called
SQL_Latin1_General_CP1_CI_AS. I'd recommend using the Simple Recovery Model to save on disk space, as backup considerations aren't important. If possible I'd recommend using a real instance of SQL Server rather than LocalDB, SQL Express, or Azure SQL Database.
- Create the schema. If you have Redgate's SQL Compare, you can select to compare from Source Control (Scripts folder – choose the
/Database/Schemafolder) to your blank database. Alternatively you can run
- Populate some sample data. If you have Redgate's SQL Data Generator, you can open the
/Database/SampleData.sqlgenproject file and click Generate Data, which will create some realistic sample data (this may take several minutes). You may need to first edit the file to change the
Passwordfields. You may also want to tune the amount of sample data generated depending on the specs of your machine. If you don’t have Sql Data Generator you can download a free trial, or write your own scripts to generate a few million rows.
- Open the
EntityFrameworkSchoolSystem.slnsolution in Visual Studio. In
App.config, modify the connection string to point to your database. Build the application (ideally as debug) and run it. Check everything's working by ensuring you get some data back when clicking Button 1.
Eight common issues are demonstrated - there's a detailed explanation of each at https://www.simple-talk.com/dotnet/.net-tools/entity-framework-performance-and-what-you-can-do-about-it/. You can see all of these in action by profiling the application using ANTS Performance Profiler. Having installed ANTS, click
New profiling session, select
.NET executable, and browse to the built application.
- Retrieving more data than needed
- N+1 select issues
- Retrieving more columns than needed
- Mismatched data types
- Missing indexes
- Generic queries
- Generating unparameterised queries
- Change detection and unbatched insert statements