Tag Archives: ef core 5

Bulk copy with Entity Framework Core 5

MS SQL Server provides functionality to quickly insert large amounts of data. It is called Bulk Copy and is performed by SqlBulkCopy class. I already compared how fast is it compared to EF Core 5 in this post: https://www.michalbialecki.com/2020/05/03/entity-framework-core-5-vs-sqlbulkcopy-2/, but this time I want to check something different – linq2db library.

What is Linq2db

Let’s check how Linq2db is described on its website:

LINQ to DB is the fastest LINQ database access library offering a simple, light, fast, and type-safe layer between your POCO objects and your database.

Sounds impressive and recently I discovered that there is an EF Core extensions package linq2db.EntityFrameworkCore, which is integrated with EF Core and enriches a DbContext with some cool features.

The most interesting are: 

  • Bulk copy (bulk insert)
  • Fast Eager Loading (incomparable faster on massive Include query)
  • MERGE statement support
  • Temporary Tables support
  • Full-Text Search extensions
  • and a few more

Let’s write some code

In the PrimeHotel project, I already implemented SqlBulkCopy method to insert profiles to db. First I generate profiles with Bogus library and then insert them. In this example, I’m going to use 3 methods from ProfileController:

  • GenerateAndInsert – implemented with pure EF Core
  • GenerateAndInsertWithSqlCopy – implemented with SqlBulkCopy class
  • GenerateAndInsertWithLinq2db – implemented with Linq2db

Let me show you quickly how those 3 methods look like. The first one is GenerateAndInsert, implemented with pure Entity Framework Core 5.

[HttpPost("GenerateAndInsert")]
public async Task<IActionResult> GenerateAndInsert([FromBody] int count = 1000)
{
    Stopwatch s = new Stopwatch();
    s.Start();

    var profiles = GenerateProfiles(count);
    var gererationTime = s.Elapsed.ToString();
    s.Restart();

    primeDbContext.Profiles.AddRange(profiles);
    var insertedCount = await primeDbContext.SaveChangesAsync();

    return Ok(new {
            inserted = insertedCount,
            generationTime = gererationTime,
            insertTime = s.Elapsed.ToString()
        });
}

I use a Stopwatch class to measure how long does it take to generate profiles with the GenerateProfiles method and how long does it take to insert them. 

GenerateAndInsertWithSqlCopy is implemented with SqlBulkCopy class:

[HttpPost("GenerateAndInsertWithSqlCopy")]
public async Task<IActionResult> GenerateAndInsertWithSqlCopy([FromBody] int count = 1000)
{
    Stopwatch s = new Stopwatch();
    s.Start();

    var profiles = GenerateProfiles(count);
    var gererationTime = s.Elapsed.ToString();
    s.Restart();

    var dt = new DataTable();
    dt.Columns.Add("Id");
    dt.Columns.Add("Ref");
    dt.Columns.Add("Forename");
    dt.Columns.Add("Surname");
    dt.Columns.Add("Email");
    dt.Columns.Add("TelNo");
    dt.Columns.Add("DateOfBirth");

    foreach (var profile in profiles)
    {
        dt.Rows.Add(string.Empty, profile.Ref, profile.Forename, profile.Surname, profile.Email, profile.TelNo, profile.DateOfBirth);
    }

    using var sqlBulk = new SqlBulkCopy(connectionString);
    sqlBulk.DestinationTableName = "Profiles";
    await sqlBulk.WriteToServerAsync(dt);

    return Ok(new
    {
        inserted = dt.Rows.Count,
        generationTime = gererationTime,
        insertTime = s.Elapsed.ToString()
    });
}

Notice that this implementation is much longer and I needed to create DataTable object, to pass my data as a table.

And finally, GenerateAndInsertWithLinq2db implementation, which uses linq2db library.

[HttpPost("GenerateAndInsertWithLinq2db")]
public async Task<IActionResult> GenerateAndInsertWithLinq2db([FromBody] int count = 1000)
{
    Stopwatch s = new Stopwatch();
    s.Start();

    var profiles = GenerateProfiles(count);
    var gererationTime = s.Elapsed.ToString();
    s.Restart();

    using (var db = primeDbContext.CreateLinqToDbConnection())
    {
        await db.BulkCopyAsync(new BulkCopyOptions { TableName = "Profiles" }, profiles);
    }

    return Ok(new
    {
        inserted = profiles.Count(),
        generationTime = gererationTime,
        insertTime = s.Elapsed.ToString()
    });
}

This method is almost as short as EF Core one, but it creates a DataConnection with CreateLinqToDbConnection method.

The results

I compared those 3 methods with inserting 1k, 10k, 50k, 100k and 500k records. How fast would it be? Let’s check.

  EF Core Bulk insert linq2db bulk insert
1000 0.22 0.035 0.048
10000 1.96 0.2 0.318
50000 9.63 0.985 1.54
100000 19.35 1.79 3.18
500000 104 9.47 16.56

Here is a table with results in seconds. EF Core itself isn’t impressive at all, but combined with Linq2db library is almost as fast as a bulk copy.

And here is the chart, the lower value, the better.

Funny thing – while testing I noticed, that generating test data is actually slower than inserting to DB, wow 😀

The summary

Linq2db is an impressive library and already offers a lot. From GitHub, it seems that it is a well-established project with a bunch of contributors. Knowing that I’m surprised that I haven’t come across it earlier. 

A bulk insert with linq2db is almost as fast as using SqlBulkCopy class but is much cleaner and shorter. It is also less error-prone and I would definitely use it in my projects.

All code posted here is available on my GitHub.

Hope it will be useful for you as well, cheers 😄

Entity Framework Core – is it fast?

Entity Framework Core is a great ORM, that recently reached version 5. Is it fast? Is it faster than it’s predecessor, Entity Framework 6, which still offers slightly more functionality? Let’s check that out.

This comparison was made by Chad Golden, comparing the performance of adding, updating, and deleting 1000 entities. The exact data and code are available on his blog: https://chadgolden.com/blog/comparing-performance-of-ef6-to-ef-core-3

The conclusions are obvious: in almost every test conducted by Chad, Entity Framework Core 3 is faster than Entity Framework 6 – exactly 2.25 to 4.15 times faster! So if performance is important to your application and it operates on large amounts of data, EF Core should be a natural choice.

Is it faster than Dapper?

Dapper is a very popular object-relational mapper and, like EF Core, it facilitates working with the database. It’s called the king of Micro ORM because it’s very fast and does some of the work for us. If we compare EF Core and Dapper, we immediately notice that the capabilities of EF Core are much greater. Microsoft technology allows you to track objects, migrate the database schema, and interact with the database without writing SQL queries. Dapper, on the other hand, maps the objects returned by the database, but all SQL commands have to be written yourself. This certainly allows more freedom in operating the database, but there is a greater risk of making a mistake when writing a SQL query. Similarly to updating the database schema, EF Core can create changes and generate a migration by itself, and in Dapper, you have to manually edit the SQL code.

There is no doubt, however, that Dapper has its supporters, mainly due to its performance. On the blog exceptionnotfound.net we can find a comparison between Entity Framework Core 3 and Dapper version 2.

As you can see, we compare 3 database reads here, where Entity Framework Core with object tracking in one case, non-tracking in the other, and Dapper’s third. Tracking changes to entities in EF Core can be turned off with the AsNoTracking() option, which makes reading operations significantly faster. More information on this test can be found here: https://exceptionnotfound.net/dapper-vs-entity-framework-core-query-performance-benchmarking-2019/

Summary

All in all – Dapper is much faster to read from the database and will certainly be comparatively fast when writing. However, it requires writing SQL queries, which can expose the developer to errors. I have personally used Dapper on several projects, and basically, only one has been dictated by performance. For the simple logic of saving and retrieving data from the database, I would use Entity Framework Core because of its simplicity and convenience in introducing changes.

 

Unit tests in Entity Framework Core 5

Tests are an integral part of software development. These are separate programs that allow you to check if a piece of the program written by us does exactly what it should. Unit tests are small pieces of code that test individual program elements and in Entity Framework Core 5 it’s surprisingly easy to write them.

In memory or not

Microsoft recommends that when writing tests that use EF Core, you should use a real database whenever possible. In fact, it is best to use the database in exactly the same configuration and on the same server on which our application is to run. This approach may not make sense when it comes to cost, as Microsoft also admits. Performance tests should certainly check our solutions in an environment as close to production as possible. However, when writing unit tests, it’s enough to keep the database in memory. Entity Framework Core allows you to run on a virtual database created only in memory. We can also use the SQLite database because it works fast and does not need a server. It also has a mode in which it can run in memory. In this chapter, we won’t go into detail about using SQLite for testing, but I can assure you that it doesn’t take much effort.

Writing unit tests

In Entity Framework Core 5 it’s very easy to configure the database to run in memory. In a test project, just install the NuGet package called Microsoft.EntityFrameworkCore.InMemory, but also a few more might come in handy. Let’s check the full list:

  • Microsoft.EntityFrameworkCore.InMemory – to run EF Core 5 in memory
  • NUnit – a framework to write and run unit tests
  • NUnit3TestAdapter – an adapter to run NUnit tests in Visual Studio
  • FluentAssertions – easy library to write nice and readable assertions

For testing, I will use the ReservationController class. Here is its full content:

[ApiController]
[Route("[controller]")]
public class ReservationsController : ControllerBase
{
    private readonly PrimeDbContext primeDbContext;

    public ReservationsController(PrimeDbContext _primeDbContext)
    {
        primeDbContext = _primeDbContext;
    }

    [HttpGet]
    public async Task<IEnumerable<Reservation>> Get()
    {
        return await primeDbContext.Reservations.Include(r => r.Room).AsNoTracking().ToListAsync();
    }

    [HttpGet("{id}")]
    public async Task<IActionResult> GetById(int id)
    {
        var reservation = await primeDbContext.Reservations.FindAsync(id);
        if (reservation == null)
        {
            return NotFound();
        }

        await primeDbContext.Entry(reservation).Collection(r => r.Profiles).LoadAsync();
        await primeDbContext.Entry(reservation).Reference(r => r.Room).LoadAsync();

        return Ok(reservation);
    }

    [HttpPost]
    public async Task<IActionResult> Post([FromBody] NewReservation newReservation)
    {
        var room = await primeDbContext.Rooms.FirstOrDefaultAsync(r => r.Id == newReservation.RoomId);
        var guests = await primeDbContext.Profiles.Where(p => newReservation.GuestIds.Contains(p.Id)).ToListAsync();

        if (room == null || guests.Count != newReservation.GuestIds.Count)
        {
            return NotFound();
        }

        var reservation = new Reservation
        {
            Created = DateTime.UtcNow,
            From = newReservation.From.Value,
            To = newReservation.To.Value,
            Room = room,
            Profiles = guests
        };

        var createdReservation = await primeDbContext.Reservations.AddAsync(reservation);
        await primeDbContext.SaveChangesAsync();

        return Ok(createdReservation.Entity.Id);
    }
}

I named the test class ReservationControllerTests, which is the name of the class and the Tests ending at the end. In these tests, I will focus on checking how to replace data in Entity Framework Core, and not to test all possible cases.

The basis here is the appropriate preparation of PrimeDbContext for testing. The very base of the class with tests looks like this:

public class ReservationsControllerTests
{
    private DbContextOptions<PrimeDbContext> dbContextOptions = new DbContextOptionsBuilder<PrimeDbContext>()
        .UseInMemoryDatabase(databaseName: "PrimeDb")
        .Options;
    private ReservationsController controller;

    [OneTimeSetUp]
    public void Setup()
    {
        SeedDb();

        controller = new ReservationsController(new PrimeDbContext(dbContextOptions));
    }

    private void SeedDb()
    {
        using var context = new PrimeDbContext(dbContextOptions);
        var rooms = new List<Room>
        {
            new Room { Id = 1, Description = "Room nr 1", Number = 1, Level = 1, RoomType = RoomType.Standard },
            new Room { Id = 2, Description = "Room nr 2", Number = 2, Level = 1, RoomType = RoomType.Standard },
            new Room { Id = 3, Description = "Room nr 3", Number = 3, Level = 2, RoomType = RoomType.Suite }
        };

        var profiles = new List<Profile>
        {
            new Profile { Id = 1, Ref = "Profile 1", Forename = "Michał", Surname = "Białecki" },
            new Profile { Id = 2, Ref = "Profile 2", Forename = "John", Surname = "Show" },
            new Profile { Id = 3, Ref = "Profile 3", Forename = "Daenerys", Surname = "Targaryen" }
        };

        context.AddRange(rooms);
        context.AddRange(profiles);

        context.AddRange(new List<Reservation>
        {
            new Reservation
            { 
                Id = 1,
                Room = rooms[0],
                Profiles = new List<Profile>{ profiles[0] },
                From = DateTime.Today,
                To = DateTime.Today.AddDays(2)
            },
            new Reservation
            {
                Id = 2,
                Room = rooms[2],
                Profiles = new List<Profile>{ profiles[1], profiles[2] },
                From = DateTime.Today.AddDays(1),
                To = DateTime.Today.AddDays(3)
            }
        });

        context.SaveChanges();
    }
}

The first thing that immediately catches our attention is the SeedDb method, which is used to add test data to the EF Core context. For these tests, the data will be entered only once, at the very beginning thanks to the [OneTimeSetUp] attribute. The state of the database will be preserved as long as the process that performs these tests is running. However, the more important part is at the top, which is creating a dbContextOptions. Note that this is where we use the UseInMemoryDatabase option, and then create the PrimeDbContext class using this object. When creating, we give the name of the database and always use the same one. Another very important line is:

using var context = new PrimeDbContext(dbContextOptions);

At first, we use the using keyword because we don’t want Garbage Collector to remove the context variable from memory while the test is running.

Since we already have a configured database and data, it’s time to test:

[Test]
public async Task Get_FetchesReservationsWithoutRoomsAndGuests()
{
    using var context = new PrimeDbContext(dbContextOptions);
    var reservations = (await controller.Get()).ToList();

    reservations.Count.Should().Be(2);
    reservations.All(r => r.Room == null).Should().BeFalse();
    reservations.All(r => r.Profiles == null).Should().BeTrue();
}

In the first test, we get all reservations and check if their dependencies are loaded. In this case, it won’t, because the Get method in the controller doesn’t force dependencies to be loaded. Let’s check another method.

[Test]
public async Task GetById_WhenIdIsProvided_FetchesReservationWithRoomsAndGuests()
{
    using var context = new PrimeDbContext(dbContextOptions);
    var result = await controller.GetById(2);
    var okResult = result.As<OkObjectResult>();
    var reservation = okResult.Value.As<Reservation>();

    reservation.Should().NotBeNull();
    reservation.Profiles.Should().NotBeNull();
    reservation.Room.Should().NotBeNull();
}

In the second test, we take a single booking and here we check that both the room and the profiles are loaded. This is because in the GetById method we use the Collection and Reference methods to load these dependencies. Now let’s test the Post method.

[Test]
public async Task Post_WithRoomAndProfiles_AddsReservation()
{
    var newReservation = new NewReservation
    {
        From = DateTime.Today.AddDays(3),
        To = DateTime.Today.AddDays(7),
        RoomId = 3,
        GuestIds = new List<int> { 2 }
    };

    using var context = new PrimeDbContext(dbContextOptions);
    var result = await controller.Post(newReservation);

    var okResult = result.As<OkObjectResult>();
    var reservationId = okResult.Value.As<int>();
    var addedReservation = await context.Reservations
        .Include(p => p.Profiles)
        .Include(r => r.Room)
        .FirstOrDefaultAsync(r => r.Id == reservationId);

    addedReservation.Should().NotBeNull();
    addedReservation.Profiles.Should().NotBeNull();
    addedReservation.Profiles.Count.Should().Be(1);
    addedReservation.Profiles[0].Id.Should().Be(2);
    addedReservation.Room.Should().NotBeNull();
    addedReservation.Room.Id.Should().Be(3);
}

The last test checks if the added reservation was added correctly. We check whether the room and the guest’s profile have been properly assigned to the new booking.

The summary

Unit testing in Entity Framework Core is really simple and understandable. Only a few lines of configuration allow us to use the dbContext class to prepare the desired database state. We do not have to replace individual collections in PrimeDbContext separately, as was the case with the Entity Framework tests. In this respect, Entity Framework Core is refined, and unit testing using it does not differ significantly from any other unit tests. Working with them is easy and fun, which is exactly as it should be.

All code mentioned here is available on my GitHub, feel free to take a look at how EF Core can be used in other parts of the project.

Thanks for reading! 😊

How to configure relationships in Entity Framework Core 5

Relationships in a database context define how two entities relate to each other. Entity Framework Core really shines in supporting relationships. It offers a convention-based configuration, that will configure relationships based on the model provided. For more advanced cases we can use robust Fluent API capabilities, that offer greater flexibility.

I must admit that working with relationships in Entity Framework Core 5 feels very natural for a developer and that might be simply its most important feature.

Types of relationships

Relationships in the database will mean, that two entities relate to each other. They are logically connected. Let’s take a look at a hotel model example:

We have a reservation, that has a single room and a list of guests. The room can be assigned to many reservations. A profile can be assigned to many reservations, but it has only one address related. We have 3 different types of relationships defined here:

  • one-to-many – room to a reservation
  • many-to-many – reservation to profile
  • one-to-one – profile to address

Those types are nicely supported by Entity Framework Core, so let’s have a look at the model that corresponds to this schema. Here is a Reservation:

    public class Reservation
    {
        public int Id { get; set; }

        public int RoomId { get; set; }

        public Room Room { get; set; }

        public List<Profile> Profiles { get; set; }

        public DateTime Created { get; set; }

        public DateTime From { get; set; }

        public DateTime To { get; set; }
    }

And Room:

    public class Room
    {
        public int Id { get; set; }

        public int Number { get; set; }

        public string Description { get; set; }

        public DateTime LastBooked { get; set; }

        public int Level { get; set; }

        public RoomType RoomType { get; set; }

        public bool WithBathroom { get; set; }

        public int NumberOfPlacesToSleep { get; set; }
    }

    public enum RoomType
    {
        Standard,
        Suite
    }

And Profile:

    public class Profile
    {
        public int Id { get; set; }

        public string Ref { get; set; }

        public string Salutation { get; set; }

        public string Forename { get; set; }

        public string Surname { get; set; }

        public string TelNo { get; set; }

        public string Email { get; set; }

        public string Country { get; set; }

        public DateTime? DateOfBirth { get; set; }

        public Address Address { get; set; }

        public List<Reservation> Reservations { get; set; }
    }

And finally, Address:

    public class Address
    {
        public int Id { get; set; }

        public string Street { get; set; }

        public string HouseNumber { get; set; }

        public string City { get; set; }

        public string PostCode { get; set; }

        public int ProfileId { get; set; }

        public Profile Profile { get; set; }
    }

And a cherry on top, a PrimeDbContext:

    public class PrimeDbContext : DbContext
    {
        public PrimeDbContext(DbContextOptions<PrimeDbContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Room> Rooms { get; set; }

        public virtual DbSet<Profile> Profiles { get; set; }

        public virtual DbSet<Reservation> Reservations { get; set; }

        public virtual DbSet<Address> Address { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }
    }

Please notice a very important thing, thanks to a convention-based configuration, there is no additional configuration needed in the model classes and PrimeDbContext class.

Configuration is easy

Have you noticed how easy it is to configure relationships in Entity Framework Core 5? If you have properties named correctly, then EF Core will deduct relationships on its own. A relationship is defined by a navigation property, that is an entity inside an entity. Look at the Reservation. There is a Room, that is a navigation property and there is a RoomId that will be treated as a foreign key to define a constraint. 

There are 3 ways to configure model and relationships:

  • convention-based – with properly named properties, EF Core will deduct how entities are related
  • data annotations – handy attributes that you can put on the entity property
  • Fluent API – a fully-featured API for configuring relations and entities as you wish

Here is an example of a data annotation to set a non-standard named foreign key:

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogForeignKey { get; set; }

    [ForeignKey("BlogForeignKey")]
    public Blog Blog { get; set; }
}

If the foreign key would be named BlogId, then it would be configured automatically, but a custom name has to be handled manually.

Even with many-to-many relationships there is no need to define a joining table and write additional configuration. It is a new feature available from RC1 version, so documentation available on the official Microsoft page can be misleading. Hopefully, it will be updated soon.

Luckily, in most cases, you would not need to write much of configuration manually, cause it is mainly required when coping with advanced scenarios and custom mappings.

Model First approach

Model First approach lets you define your model and relationships and use Entity Framework Core to generate SQL for you. All you need to do is to create models that you want and when you’re done, just create a database migration. This is true of course when you already have EF Core migrations in place. 

It also works for updating the model, when you need to add a related entity, EF Core migrations will handle that surprisingly well.

Let’s say I had a profile entity and I wanted to add an Address entity in a one-to-one relationship. You can take a look at the code of those both classes above. When I add a new migration from dotnet CLI, I get a new migration like this, already generated for me, based on my model changes.

    public partial class AddAddress : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Address",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Street = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    HouseNumber = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    City = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    PostCode = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    ProfileId = table.Column<int>(type: "int", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Address", x => x.Id);
                    table.ForeignKey(
                        name: "FK_Address_Profiles_ProfileId",
                        column: x => x.ProfileId,
                        principalTable: "Profiles",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_Address_ProfileId",
                table: "Address",
                column: "ProfileId",
                unique: true);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Address");
        }
    }

Nice and easy, but what’s most important – it works!

Summary

Configuring relationships in Entity Framework Core 5 is as simple as it can possibly be. Most of the mapping can be done automatically by the framework, just by naming properties right. If you’re struggling with more advanced scenarios you can go with Fluent API, which offers a lot and is rather compact. In just a few lines you can define how to map a view to an entity.

My favorite part, though, is a Model First approach, where you create a model that you would like to work with and generate SQL with EF Core migrations. 

All code mentioned here is available on my GitHub, code that uses those relationships as well! Feel free to drop by.

How not to pass parameters in Entity Framework Core 5

Recently I wrote a post about executing raw SQL scripts in Entity Framework Core 5: Executing raw SQL with Entity Framework Core 5. One of the readers noticed that I did a big mistake when passing parameters. Let’s take a closed look.

I had code like this:

    [HttpPost("UpdateProfiles")]
    public async Task<IActionResult> UpdateProfiles([FromBody] int minimalProfileId = 0)
    {
        await primeDbContext.Database.ExecuteSqlRawAsync(
            $"UPDATE Profiles SET Country = 'Poland' WHERE LEFT(TelNo, 2) = '48' AND Id > {minimalProfileId}");

        return Ok();
    }

This method updates profiles Country to Poland, where the phone number starts with 48, and an Id is higher than provided. Notice that I used ExecuteSqlRawAsync and provided an interpolated string, where I pass minimalProfileId. So where’s the catch?

When passing parameters to SQL you have to be super cautious. Especially when you’re passing a user-provided data, you are exposed to SQL injection attack. In order to avoid that, you should use FromSqlInterpolated or ExecuteSqlInterpolated methods. Changes to the code are minimal:

    [HttpPost("UpdateProfiles")]
    public async Task<IActionResult> UpdateProfiles([FromBody] int minimalProfileId = 0)
    {
        await primeDbContext.Database.ExecuteSqlInterpolatedAsync(
            $"UPDATE Profiles SET Country = 'Poland' WHERE LEFT(TelNo, 2) = '48' AND Id > {minimalProfileId}");

        return Ok();
    }

Nice huh? I can still pass an interpolated string, but with ExecuteSqlInterpolatedAsync I’m safe. Using this method allows parameters to be passed separately, causing .Net Core to check them for invalid characters or expressions. You can read more about it on the Microsoft website.

Hope you liked this post, stay safe 🙂

All code posted here can be found on my GitHub account. Enjoy!

Executing raw SQL with Entity Framework Core 5

Entity Framework Core 5 is an open-source, lightweight, extensible, and a cross-platform ORM. It is easy to apply and it makes database access super simple. However, sometimes working with tables and views is just not enough. How to execute raw SQL script with Entity Framework Core 5? Let’s find out.

Running the raw SQL

Running a SQL without carrying about the result is quite easy. Have a look at this example:

await primeDbContext.Database.ExecuteSqlInterpolatedAsync(
$"UPDATE Profiles SET Country = 'Poland' WHERE LEFT(TelNo, 2) = '48' AND Id > {minimalProfileId}");

This SQL updates the Country based on TelNo column for profiles with Id higher then the one provided. It is just a few lines of code and it works perfectly! It also shows how we can pass a parameter to SQL, but you also can format it with curly braces.

Executing a stored procedure

A stored procedure is a perfect example of a SQL, that you might want to run directly on the database, but keep SQL on the database side. Let’s say you already have a stored procedure named UpdateProfilesCountry with one parameter. If you would just like to execute it, you could simply have a code like this:

await primeDbContext.Database.ExecuteSqlInterpolatedAsync(
    $"UpdateProfilesCountry {minimalProfileId}");

You don’t need a DbSet to map the results, so you can use DbContext.Database.ExecuteSqlRawAsync and pass parameters if you’d like to.

If you’d like to get the full picture, read the separate article: Execute a stored procedure with Entity Framework Core 5.

Running a stored procedure with a result

While running a stored procedure or a plain SQL command can be done directly on the database level, returning the result is slightly more complicated. First of all, you need to add a keyless model to map your results. Let’s say we have a stored procedure GetGuestsForDate and we would like to return a model named GuestArrival.

[Keyless]
public class GuestArrival
{
    public string Forename { get; set; }

    public string Surname { get; set; }

    public string TelNo { get; set; }

    public DateTime From { get; set; }

    public DateTime To { get; set; }

    public int RoomNumber { get; set; }
}

We also need to add a DbSet to our PrimeDbContext.

public class PrimeDbContext : DbContext
{
    public PrimeDbContext(DbContextOptions<PrimeDbContext> options)
        : base(options)
    {
    }

    // from stored procedures
    public virtual DbSet<GuestArrival> GuestArrivals { get; set; }
}

Now we can go ahead and use it. Here is how we can accomplish that:

var guests = primeDbContext.GuestArrivals.FromSqlInterpolated($"GetGuestsForDate '{date}'").ToList();

And if I place it in my API project, it will map results to entities:

Notice that to map results to objects, we need to use DbSet collection.

If you’d like to get the full picture, read the separate article: Select data with a stored procedure with Entity Framework Core 5

Summary

It is not only possible but also surprisingly easy to run any SQL on the database with Entity Framework Core 5. You can execute SQL on a database level, but when you care about the result, you need to add a DbSet representing your results.

All code mentioned here can be found on my GitHub, feel free to experiment with it.

Cheers!

 

Working with views in Entity Framework Core 5

In SQL, a view is a virtual table based on the result-set of an SQL statement. They are typically used as read-only objects that are optimized to provide data for a given scenario. Entity Framework Core 5 can handle views and in this article, I’m going to show you how.

Adding a view

First of all, we need to add a view to the database. The best way to do so is to add a database migration with an appropriate SQL. Let’s start by adding a migration with EF Core global tool command:

dotnet ef migrations add vwGuestArrivals

This will generate a migration, that we can put our SQL into. Let’s see how it may look:

public partial class vwGuestArrivals : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sql = @"
            CREATE OR ALTER VIEW [dbo].[vwRoomsOccupied] AS
                SELECT r.[From], r.[To], ro.Number As RoomNumber, ro.Level, ro.WithBathroom
                FROM Reservations r
                JOIN Rooms ro ON r.RoomId = ro.Id";

        migrationBuilder.Sql(sql);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"DROP VIEW vwRoomsOccupied");
    }
}

This view presents rooms that are occupied, where we can filter by date. This kind of data can be useful for example when planning maintenance.

Getting view data

In Entity Framework Core 5 views can be represented as a regular DbSet. In my case, to map all view’s columns, we need to create a RoomOcupied model, that would look like this:

[Keyless]
public class RoomOccupied
{
    public DateTime From { get; set; }

    public DateTime To { get; set; }

    public int RoomNumber { get; set; }

    public int Level { get; set; }

    public bool WithBathroom { get; set; }
}

Now we need to add a DbSet to my PrimeDbContext and we need to configure our model, so that RoomsOccupied will be executed against view. Let’s see how that can be accomplished:

public class PrimeDbContext : DbContext
{
    public PrimeDbContext(DbContextOptions<PrimeDbContext> options)
        : base(options)
    {
    }

    public virtual DbSet<RoomOccupied> RoomsOccupied { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<RoomOccupied>(eb =>
            {
                eb.HasNoKey();
                eb.ToView("vwRoomsOccupied");
            });
    }
}

As you can see it is a normal DbSet, that can be queried as we wish. However, there is a tiny detail that makes this collection different. Notice that we configure RoomOccupied entity to have no key. This way we do not need to have a key in the result, but it also means that it would only be read-only model.

Currently, Entity Framework Core 5 does not support updating the view, while it is possible in SQL Server database. You can specify a view with a key, though. Just remember to remove HasNoKey in configuration and [Keyless] attribute in the entity.

Let’s use the code that we just wrote. To do so the easiest possible way, I just added a method to my ASP.NET Core API. Here is how it looks like:

[HttpGet("GetRoomsOccupied")]
public IActionResult GetGuestArrivalsFromView([FromQuery] string date)
{
    var parsedDate = DateTime.ParseExact(date, "dd-MM-yyyy", CultureInfo.InvariantCulture);
    var rooms = primeDbContext.RoomsOccupied.Where(r => r.From <= parsedDate && r.To >= parsedDate);

    return Ok(rooms);
}

Here I’m passing date in dd-MM-yyyy format and list all occupied rooms at the given date. Here is the result.

It uses a vwRoomsOccupied and executes a SQL query with all filters applied. We can take a look in SQL Server Profiler at the SQL that was executed.

Note that in this example we are using only dates with no time and it works fine. However, if you would like to compare dates with time as well, you would need to use a slightly different approach.

Summary

Entity Framework Core 5 can handle views flawlessly. You need to configure it in your DbContext class and specify that a specific entity will be mapped to the view. When that’s done, you can use a DbSet as you wish and all filters will be applied directly into generated SQL.

What’s more, you can handle adding or updating the view with EF Core migrations, which means that all work required can be done with EF Core.

All code mentioned here can be found on my GitHub, feel free to experiment with it.

Cheers!

 

Select data with a stored procedure with Entity Framework Core 5

Stored procedures are an integral part of any MS SQL database. They are perfect to wrap complicated SQL into a database object, that we can reuse. How to execute a stored procedure that returns data in Entity Framework Core 5? In my last post: Execute a stored procedure with Entity Framework Core 5 I showed how to run a stored procedure, but selecting the data it’s a different kind of story. Let’s have a look.

Adding a stored procedure

First of all, we need to add a stored procedure. The best way to do so is to add a database migration with an appropriate SQL. Let’s start by adding a migration with EF Core global tool command:

dotnet ef migrations add spGetGuestsForDate 

This will generate a migration, that we can put our SQL into. Let’s see how it may look:

public partial class spGetGuestsForDate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sql = @"
            IF OBJECT_ID('GetGuestsForDate', 'P') IS NOT NULL
            DROP PROC GetGuestsForDate
            GO

            CREATE PROCEDURE [dbo].[GetGuestsForDate]
                @StartDate varchar(20)
            AS
            BEGIN
                SET NOCOUNT ON;
                SELECT p.Forename, p.Surname, p.TelNo, r.[From], r.[To], ro.Number As RoomNumber
                FROM Profiles p
                JOIN Reservations r ON p.ReservationId = p.ReservationId
                JOIN Rooms ro ON r.RoomId = ro.Id
                WHERE CAST([From] AS date) = CONVERT(date, @StartDate, 105)
            END";

        migrationBuilder.Sql(sql);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"DROP PROC GetGuestsForDate");
    }
}

This is a simple SQL code, that first checks if a procedure exists and if so, it deletes it. Then it creates a new procedure with the name GetGuestsForDate, which will get all arriving guests for a given date.

When the migration will be executed on the database, this stored procedure will be present, which we can see here:

Selecting data with a stored procedure

When you look closely at the SQL, you will notice that we expect to receive a list of guests with fields: Forename, Surname, TelNo, From, To and RoomNumber. In order to use the stored procedure to query the database and map results into entities, we need to add an appropriate entity. In my case I’ll add GuestArrival, that looks like this:

[Keyless]
public class GuestArrival
{
    public string Forename { get; set; }

    public string Surname { get; set; }

    public string TelNo { get; set; }

    public DateTime From { get; set; }

    public DateTime To { get; set; }

    public int RoomNumber { get; set; }
}

This class contains all columns that I’d like to map and also it has [Keyless] attribute. Keyless entities have most of the mapping capabilities as normal entities, but they are not tracked for changes in the DbContext. It also means that we won’t be able to perform insert, update, or delete on such entity.

We also need to add a DbSet to our PrimeDbContext.

public class PrimeDbContext : DbContext
{
    public PrimeDbContext(DbContextOptions<PrimeDbContext> options)
        : base(options)
    {
    }

    // from stored procedures
    public virtual DbSet<GuestArrival> GuestArrivals { get; set; }
}

Now we can go ahead and use it. Here is how we can accomplish that:

var guests = primeDbContext.GuestArrivals.FromSqlInterpolated($"GetGuestsForDate '{date}'").ToList();

And if I place it in my API project, it will map results to entities:

In here I’m using date parameter in dd-mm-yyyy format, which I pass as a string into my stored procedure. Then inside I use CONVERT(date, @StartDate, 105), where 105 is a date format I’m going to parse. You can find more supported formats in this article. It probably would be better to use SqlParameter class with Date datatype, but I couldn’t get it to work. 

Summary

Entity Framework Core can handle data selected by stored procedures easily. There is no dedicated method to run the procedure, but it can be run as a standard raw SQL on a DbSet. However, if you’re interested only in executing the stored procedure, you don’t need a DbSet for it. You can check out the details in my previous post: Execute a stored procedure with Entity Framework Core 5.

What’s more, you can handle adding or updating stored procedures with EF Core migrations, which means that all work required can be done with EF Core.

All code mentioned here can be found on my GitHub, feel free to experiment with it.

Cheers!

 

Execute a stored procedure with Entity Framework Core 5

Stored procedures are an integral part of any MS SQL database. They are perfect to wrap complicated SQL into a database object, that we can reuse. How to execute a stored procedure in Entity Framework Core 5? Let’s have a look.

Adding a stored procedure

First of all we need to add a stored procedure. The best way to do so is to add a database migration with an appropriate SQL. Let’s start by adding a migration with EF Core global tool command:

dotnet ef migrations add spUpdateProfilesCountry

This will generate a migration, that we can put our SQL into. Let’s see how it may look:

public partial class spUpdateProfilesCountry : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sql = @"
            IF OBJECT_ID('UpdateProfilesCountry', 'P') IS NOT NULL
            DROP PROC UpdateProfilesCountry
            GO

            CREATE PROCEDURE [dbo].[UpdateProfilesCountry]
                @StardId int
            AS
            BEGIN
                SET NOCOUNT ON;
                UPDATE Profiles SET Country = 'Poland' WHERE LEFT(TelNo, 2) = '48' AND Id > @StardId
            END";

        migrationBuilder.Sql(sql);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"DROP PROC UpdateProfilesCountry");
    }
}

This is a simple SQL code, that first checks if procedure exists and if so, it deletes it. Then it creates a new procedure with UpdateProfilesCountry name, which will update Country column for every Profile that phone number starts from 48.

When this migration will be run on the database, it will create UpdateProfilesCountry stored procedure as in my case.

Running the stored procedure

There is no dedicated method to run a stored procedure, so in the case where a stored procedure doesn’t return data, we can just invoke it as a raw SQL. This can be achieved like this:

await primeDbContext.Database.ExecuteSqlInterpolatedAsync(
    "UpdateProfilesCountry @p0",
    parameters: new[] { minimalProfileId.ToString() });

When I query the database for all numbers starting with 48, I will see that the country has been updated to Poland. This means that our procedure was executed successfully.

BTW. Don’t worry, those are fake data, generated with Bogus 🙂

Summary

Entity Framework Core can handle stored procedures quite well. There is no dedicated method to run the procedure, but it can be run as a standard raw SQL. However, when you’d like to query the database with a stored procedure, you must use a different approach. I have described that in a separate post: Select data with a stored procedure with Entity Framework Core 5.

What’s more, you can handle adding or updating stored procedures with EF Core migrations, which means that all work required can be done with EF Core.

All code mentioned here can be found on my GitHub, feel free to experiment with it.

Cheers!

Merging migrations in Entity Framework Core 5

When working with a fast-evolving project changes happen rapidly not only to the project’s code but also to the database schema. It happens especially when working on a micro-service from the start when its purpose shifts.

How EF Core 5 migrations work

In Entity Framework Core 5 migrations we are adding a migration as a delta changes between our DbContext class and existing [DbContext name]ModelSnapshot. When generating new migration a CLI Tool will generate only the differences between those two and put them in two methods: Up and Down. In the first one, there will be a change to apply the migration and in the second one, to remove the migration.

After the migration is applied, its name is noted in the __EFMigrationsHistory table.

Merging multiple migrations 

Let’s say that after changing a schema multiple times in the early stages our project is now stable. We have a bunch of migrations that could be merged into one, that would create a model once, without many small updates. 

When we can remove everything

The easiest way to merge all migrations would be removing everything! What I mean is:

  • remove Migrations directory with all migrations
  • clear __EFMigrationHistory table
  • remove all tables and other database objects that were added via migrations
  • create new migration with all changes

This is a drastic way of merging migrations because we will lose all the data. However, it’s super simple and it might work in some cases.

When we need to preserve data

This means that we cannot remove all already created database objects, but we can merge migration files in our code. Let’s see how that can be done.

  1. Delete all migration scripts from Migrations folder
  2. Add a new migration with command dotnet ef migrations add MergedMigration
  3. Copy the entire file and clear both Up and Down methods
  4. Update the database and apply MergedMigration migration with command dotnet ef database update
  5. After that, replace the content of the MergedMigration file with earlier generated code

As a result, you will have only one migration file. In my example the __EFMigrationHistory table looks like this.

And now in the Visual Studio, I can see only on migration.

That contains changes from all my previous migrations merged together.

It worked!

Tip! You can also name you merged migration as the first one, that was already applied so that you wouldn’t have to update the database and add it to the __EFMigrationHistory table. 

It will not work in every case

We can easily merge migrations when they are applied only to the database that we control. I addition, it will not work for environments that don’t have all merged migrations applied. The process is easy, but there are certain things that need to be taken into account.

On the other hand, do we need to keep all migrations, even when we know that we won’t run them ever again? I don’t think so. At this point, it would be great to merge old migrations but leave the newest ones. It could be accomplished with a very similar process.

  1. Revert last N migrations locally, one by one, and move them somewhere
  2. Check out the project in the place matching the migrations
  3. Merge all existing migrations
  4. Check out the project in the newest state
  5. Add saved newest migrations, one by one

In this case, we will keep the newest migrations and create a big initial migration, that would be consistent with your project. If there will be a case, where all migrations would need to be applied to a database, the database schema wouldn’t be broken.

Summary

Merging migrations in Entity Framework Core 5 is possible and I can say it’s surprisingly easy. However, it involves an automatic generation process and you need to check if merged migration does exactly the same thing as all migrations applied one by one. Moreover, there is more than one way to merge migrations and you need to choose the one that is best for you.

All code posted here is available at my GitHub, so you can download it and play with it. Take a look also at this post on how to run it: PrimeHotel – how to run this project.

Thanks for reading and hopefully see you again 🙂