Tag Archives: SQL

Useful SQL statements when writing EF Core 5 migrations

Entity Framework Core 5 is a great ORM and I love how efficient and concise it is. With the migrations mechanism enabled, you can generate the next migration based on changes applied to your model. This is so cool, but when it comes to other database objects, you are on your own. I mean – you can still use migrations, but you have to figure out a SQL statement yourself. Let’s take a look at some cool statements you can use with the SQL Server database.

CREATE OR ALTER

This is a very powerful command that literally just check if a database object exists, and it alters it or creates a new one based on that fact.

So instead of writing a statement like this:

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

I can use CREATE OR ALTER, like this:

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

Additionally, CREATE OR ALTER does not reset permissions, so you don’t need to re-apply them, which would be necessary with the first script.

CREATE OR ALTER can be used with the following objects:

  • STORED PROCEDURES (including natively compiled)
  • FUNCTIONS (Transact-SQL, including natively compiled)
  • TRIGGERS
  • VIEWS

Other database objects like tables or indexes cannot be handled with that statement. You can find more information about it in this Microsoft post.

Note: CREATE OR ALTER is available from SQL Server 2016 SP1.

DROP IF EXISTS

DROP IF EXISTS is a useful statement that can be used for many database objects. It will check if the object exists and if it does, it will drop it – all in one statement.

Instead of writing a statement like this:

IF OBJECT_ID('dbo.Products', 'U') IS NOT NULL 
  DROP TABLE dbo.Products; 

Now we can write a simple one-liner:

DROP TABLE IF EXISTS dbo.Products

Clean and simple. DROP IF EXISTS will work for many database objects, like:

  • AGGREGATE
  • ASSEMBLY
  • VIEW
  • DATABASE
  • DEFAULT
  • FUNCTION
  • INDEX
  • PROCEDURE
  • ROLE
  • RULE
  • SCHEMA
  • SECURITY POLICY
  • SEQUENCE
  • SYNONYM
  • TABLE
  • TRIGGER
  • TYPE
  • USER
  • VIEW

You can read more about it in this Microsoft post. Also Note: DROP IF EXISTS is available from SQL Server 2016.

Hope you like it, maybe you have some SQL statements you find useful at your work? Give me a shout or leave a comment. Cheers! 😉 

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!

 

Code review #1 – dapper and varchar parameters

This is a first post about great code review feedback, that I either gave or received. It will always consist of 3 parts: context, review feedback and explanation. You can go ahead and read previous ones here: https://www.michalbialecki.com/2019/06/21/code-reviews/. So lets not wait anymore and get to it.

The context

This is a simple ASP.Net application, that is requesting database to get count of elements filtered by one parameter. In this case we need a number of users providing a country code, that is always two character string.

This is how DB schema looks like:

Code in .net app is written with Dapper nuget package, that extends functionalities of IDbCommand and offers entities mapping with considerably good performance. It looks like this:

public async Task<IEnumerable<UserDto>> GetCountByCountryCode(string countryCode)
{
    using (var connection = new SqlConnection(ConnectionString))
    {
        return await connection.QueryAsync<UserDto>(
            "SELECT count(*) FROM [Users] WHERE CountryCode = @CountryCode",
            new { CountryCode = countryCode }).ConfigureAwait(false);
    }
}

Looks pretty standard, right? What is wrong here then?

Review feedback

Please convert countryCode parameter to ANSI string in GetCountByCountryCode method, cause if you use it like that, it’s not optimal.

Explanation

Notice, that CountryCode in database schema is a varchar(2) and this means that it stores two 1-byte characters. On the contrary nvarchar type is 2-byte per character type, that can store multilingual data. When using .net String type we are using unicode strings by default and therefore if we pass countryCode string to SQL it will have to be converted to ANSI string first.

The correct code should look like this:

public async Task<IEnumerable<UserDto>> GetCountByCountryCodeAsAnsi(string countryCode)
{
    using (var connection = new SqlConnection(ConnectionString))
    {
        return await connection.QueryAsync<UserDto>(
            "SELECT count(*) FROM [Users] WHERE CountryCode = @CountryCode",
            new { CountryCode = new DbString() { Value = countryCode, IsAnsi = true, Length = 2 } })
            .ConfigureAwait(false);
    }
}

If we run SQL Server Profiler and check what requests are we doing, this is what we will get:

As you can see first query needs to convert CountryCode parameter from nvarchar(4000) to varchar(2) in order to compare it.

In order to check how that would impact the performance, I created a SQL table with 1000000(one milion) records and compared results.

Before review it took 242 miliseconds and after review it took only 55 miliseconds. So as you see it is more that 4 times performance improvement in this specific case.

  All code posted here you can find on my GitHub: https://github.com/mikuam/console-app-net-core