Run maintenance jobs on Azure SQL

Azure SQL, do not forget to schedule maintenance

Many users on Azure SQL Server do not realize they have to do their own maintenance on Indexes. This index will slowly become fragmented and the performance will decrease over time. Azure SQL does not have a Job scheduler (agent) like on premise. In this post I’ll describe how to schedule a maintenance job from an ASP.NET Core application.
Continue reading “Run maintenance jobs on Azure SQL”

Increase timeout Migrations Entity Framework Core

Use a different connection timeout when running your migrations with EF Core.

Sometimes a migration takes more time then your default timeout of your database connection. In such a case you do want to increase the timeout for the migration to be able to complete, but you do not want to change the timeout for your normal operations.
Continue reading “Increase timeout Migrations Entity Framework Core”

Add Index with Include Entity Framework Core

This post explaines how to add index to EF Core with extra columns included from code.

When creating indexes with code first migrations in Entity Framework Core you can create an index on a table by adding the following to your DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<table>() 
        .HasIndex(t =&gt; new { t.Column1, t.Column2}); } 

Continue reading “Add Index with Include Entity Framework Core”

Implement Pessimistic Concurrency in Entity Framework Core

ConcurrencyIn a scenario where we were using SQL server as a queue, before publishing events to external queues, we wanted the data to be processed only once and in order, even with multiple processors for failover. When reading from the table we wanted to lock the records and block other processors from reading those records, while being processed. This is called Pessimistic Concurrency, unfortunately Entity Framework Core does not support this out of the box. To realize Pessimistic Concurrency you need to write your own SQL queries directly on the database (The solution is database type bound, in this case Microsoft SQL server). This blog post will show how it can be accomplished.
Continue reading “Implement Pessimistic Concurrency in Entity Framework Core”

Entity Framework and Full Text Search

Out of the box Entity Framework does not support Full Text Search. To do Full Text Search you have a number of options to get it working. In this blog post I’ll describe a method to get Full Text Search working using a table valued function. The method does the Full Text Search in a table valued function that returns an id list, the returned ids can be used to filter the records from an actual table.
Continue reading “Entity Framework and Full Text Search”

Managing SQL Server migrations

NuGet package to manage the SQL Server database version

In two projects I recently made a tool for managing the version of the SQL Server database manually. When writing almost the same utility code twice, there would be a third time. So I created a NuGet package to manage the SQL Server version. On start up it checks the database version and then run any upgrade scripts if needed. It also ensures the scripts run only once.
Continue reading “Managing SQL Server migrations”