Setting batch size in Entity Framework Core

Entity Framework (pre-core) didn’t support batching of CUD statements, so if you were inserting 100 entities for example, EF sent 100 statements across the wire which isn’t ideal from a latency point of view.

Thankfully Entity Framework Core supports batching by default. Currently there’s no default batch size (limited by the DB platform) however there’s likely to be a ‘sweet spot’ for your application of perhaps between 20-50 inserts per statement which gives the best performance.

There’s a lot of factors at play so its really a matter of trial and error to see what performs best. Setting the batch size too low will result in too many round-trips whereas if the batch size is set too high (or just left to the default) there will be less roundtrips but performance could still be poor as the larger query will take more time to run on the DB server.

Setting the batch size is easy and is done in the OnConfiguring method. In the below example I’ve instructed EF Core to update no more than 50 entities in each SQL statement.

EF Core Batching

Note from above you can see I’ve also set MinBatchSize which means batching will only kick in when there’s at least 5 entities to update. If there’s less than 5 items to be updated separate queries will be sent by EF Core. In most cases you can safely ignore this setting as EF will trigger batching of entities at around 3-7 entities anyhow.

View SQL queries run by Entity Framework Core in the console

In Entity Framework Core writing the executed queries to the console is easy. Just create a ILoggerFactory and then configure EF Core to use it in the OnConfiguring method.

The code below is valid as of Entity Framework Core 3.1.3.

Output EF Core queries to the console

Remember if your using a database first approach your OnConfiguring customisations will be erased when you re-scaffold your DB so put your customisations in a subclass of the EF generated context.

*** Note ***
In EF Core 5 you’ll be able exclude OnConfiguring when scaffolding.

Using the C# Interactive window to test code snippets in Visual Studio

If you want to test little snippets of code in Visual Studio before having to change your code and having to launch and debug your app, check out the Interactive window via the View -> Other Windows -> C# Interactive menu option.

The C# Interactive window is a REPL Editor, i.e. Read-Evaluate-Print-Loop and can be useful for things like

– checking date formats (shown below)
– declaring and testing methods
– testing strings – interpolation, substring, replace, trim etc.
– testing LINQ queries on declared collections

C# Interactive Window

Very nice introduction to the interactive window on the Microsoft Visual Studio channel on YouTube

Make entities inherit from a base class in Entity Framework Core DB First

It’s often useful to have our entities inherit from a base class. This is easily done using EF Core Power Tools which is a Visual Studio extension that provides a graphical rather than command line based approach to scaffolding.

EF Core Power Tools uses the Scaffolding.Handlebars library to support the customization of code which is generated during the scaffolding process. You don’t need the VS extension to use the library but it’s a bit easier and EF Core Power Tools is a great extension anyway so I’ll use it in the post.

The steps are…

Install the extension via the Extensions -> Manage Extensions window (you’ll then see the menu as a right-click option on your projects).

Right click on the relevant project and select EF Core Power Tools -> Reverse Engineer and first select which tables you want to scaffold and then on the subsequent screen check the option for customizing the code.

Reverse Engineer

After running the scaffolding you should see entities generated AND a CodeTemplates folder which contains all the handlebar template files for customizing entities and the DbContext.

To inherit from a base class just amend the Class template (Class.hbs) file as shown on the left below.

Inherit from a base class

Re-scaffold again with right click -> EF Core Power Tools -> Reverse Engineer and all your entities should now inherit from the base class

Entity inheriting base class example

Of course to have your entities implement an interface the process is identical.

Excluding tables in Entity Framework Core Scaffold-DbContext command

In Entity Framework Core DB First if you don’t want models created for certain tables you need to explicitly list the tables you do want models created for in the Scaffold-DbContext command. There is no tables to exclude parameter. If you have a large number of tables and only want to exclude a few this is tedious.

Microsoft have an issue for this on their backlog, but it doesn’t look like its going to get into EF Core 5 (November 2020) so to ease your pain you can…

Wrap the Scaffold-DbContext command with your long list of tables in a .bat file which helps with rerunnability so you don’t have to keep on specifying your table names

or a much nicer option is to

Switch from command line to UI based scaffolding using EF Core Power Tools which is a great Visual Studio extension which will allow you to just unselect the tables you don’t need when scaffolding. Thankfully it can also remember your selection for next time too.

Select tables to include/exclude