Entity Framework Core: Making RowNumber (more) usefull

In the blog post ROW_NUMBER Support we saw how to implement a custom function and in the previous post Improved Value Conversion Support we realized that inserting and selecting custom types is one thing but using them for filtering is something totally different.

Let's take a query from one of the previous posts and add a WHERE clause:

var query = dbContext.OrderItems
.Select(i => new
{
...,
RowNumber = EF.Functions.RowNumber(i.ProductId)
})
.Where(i => i.RowNumber == 1);

When executing the query we get a SqlException because the SQL statement is not valid.

SELECT
...,
ROW_NUMBER() OVER(ORDER BY i.ProductId) AS RowNumber
FROM
OrderItems AS i
WHERE
ROW_NUMBER() OVER(ORDER BY i.ProductId) = CAST(1 AS bigint)

The ROW_NUMBER is not just in SELECT but in WHERE as well because EF cannot know that the main query should be put into a sub query before accessing RowNumber, i.e. something like:

SELECT ...
FROM
(
SELECT
...,
ROW_NUMBER() OVER(ORDER BY i.ProductId) AS RowNumber
FROM
OrderItems AS i
) t
WHERE
t.RowNumber = CAST(1 AS bigint)

Probably, the easiest way is to introduce a method that gives EF a hint that the previous query should be a sub query. Something like:

var query = dbContext.OrderItems
.Select(i => new
{
...,
RowNumber = EF.Functions.RowNumber(i.ProductId)
})
.AsSubQuery()
.Where(i => i.RowNumber == 1);

Fortunately, we don't have do much because internally the method AsQueryable (or rather the expression associated with it) does just that. We will just (shamelessly ab)use it:

public static class MyQueryableExtensions
{
private static readonly MethodInfo _asQueryableMethodInfo
= typeof(Queryable)
.GetMethods(BindingFlags.Public | BindingFlags.Static)
.Single(m => m.Name == nameof(Queryable.AsQueryable)
&& m.IsGenericMethod);

public static IQueryable<TEntity> AsSubQuery<TEntity>(
this IQueryable<TEntity> source)
{
if (source == null)
throw new ArgumentNullException(nameof(source));

if (!(source.Provider is EntityQueryProvider))
return source;

var methodCall = Expression.Call(
null,
_asQueryableMethodInfo.MakeGenericMethod(typeof(TEntity)),
source.Expression);

return source.Provider.CreateQuery<TEntity>(methodCall);
}
}

Having the method AsSubQuery we get the expected results.

 

 

 


Entity Framework Core: Improved Value Conversion Support

Entity Framework Core (EF) 2.1 introduced a new feature called Value Conversion. Now, we are able to map custom .NET types to a type the database understands and vice versa. This long-awaited feature is especially popular among software engineers following the domain driven design (DDD) patterns.

At the moment the value conversion has some limitations that will be removed in the future. The limitation I want to address in this post is the fallback to client-side evaluation in some fairly simple but rather frequently needed use case.

As an example I will use an entity containing the property of type System.Drawing.Color:

public class Product
{
...
public Color Color { get; set; }
}

And the corresponding value conversion:

modelBuilder.Entity<Product>()
.Property(p => p.Color)
.HasConversion(color => color.Name, name => Color.FromName(name));

With provided conversion, only the name of the color is saved and when the name comes back from the database then it is mapped to an instance of Color.

Having the converter we are now able to insert, update and select the property Color.

DemoDbContext ctx = ...;

// adding new product
ctx.Products.Add(new Product { ..., Color = Color.Green});
ctx.SaveChanges();

// retrieving all products
var products = await ctx.Products.ToListAsync();

In both cases we get the expected results. Now let us fetch all products with the color Green.

var products = await ctx.Products
.Where(p => p.Color == Color.Green)
.ToListAsync();

This time the result is correct as well but EF emits a warning: The LINQ expression 'where ([p].Color == __Green_0)' could not be translated and will be evaluated locally.

A look at the SQL statement proves that the filtering is done in memory and not in the database:

SELECT ..., [p].[Color]
FROM [Products] AS [p]

When having just a few products in the database then there is nothing to worry about but with a lot of records this query may lead to major performance issues.

Implement the IRelationalTypeMappingSourcePlugin

The provided value converter is applied to the property Color only so the constant value on the right side of the equation is not being converted. What we need is kind of global value converter, so let's build one.

To provide EF with the value converter for Color we have to implement the interface IRelationalTypeMappingSourcePlugin that has just 1 method FindMapping. First, we prepare the mapping that will be returned by the plugin. The type mapping is derived from RelationalTypeMapping and is providing the CLR type and the converter to the base class.

Actually, the store type nvarchar(50) is not being used in this case so you can even write rumpelstilzchen if you want to.

public class ColorTypeMapping : RelationalTypeMapping
{
private static readonly ValueConverter<Color, string> _converter
= new ValueConverter<Color, string>(color => color.Name,
name => Color.FromName(name));

public ColorTypeMapping()
: base(new RelationalTypeMappingParameters(
new CoreTypeMappingParameters(typeof(Color), _converter),
"nvarchar(50)"))
{
}
}

The plugin itself is just waiting for the method to be called with the CLR type Color to return previously implemented ColorTypeMapping.

public class ColorTypeMappingPlugin : IRelationalTypeMappingSourcePlugin
{
public RelationalTypeMapping FindMapping(in RelationalTypeMappingInfo mappingInfo)
{
if (mappingInfo.ClrType == typeof(Color))
return new ColorTypeMapping();

return null;
}
}

Register the plugin with dependency injection

Now, we have to register the ColorTypeMappingPlugin with the (internal) dependency injection (DI) of EF. For that we have to implement IDbContextOptionsExtension like I did in one of my previous blogs (EF Core: Custom Functions - search for the first occurrence of IDbContextOptionsExtension) or use the nuget package Thinktecture.EntityFrameworkCore.Relational (docs).

By using the nuget package the registration of the plugin looks as following:

services
.AddDbContext<DemoDbContext>(builder => builder
...
.AddRelationalTypeMappingSourcePlugin<ColorTypeMappingPlugin>()

Trying out the plugin

Let's look at the SQL statements when we query for products with the color Green:

// having the value `Color.Green` inline we get the SQL
//    SELECT ..., [p].[Color]
//    FROM [Products] AS [p]
// WHERE [p].[Color] = @__Green_0

var products = await ctx.Products
.Where(p => p.Color == Color.Green)
.ToListAsync();

// but more often the filter comes via parameter
//    SELECT ..., [p].[Color]
// FROM [Products] AS [p]
// WHERE [p].[Color] = @__color_0

var color = Color.Green;
var products = await ctx.Products
.Where(p => p.Color == color)
.ToListAsync();

With less than 20 lines of code we were able to improve the usability of the value conversion so the EF model can be as close as possible to our domain model without loosing performance. It is just a matter of time until EF team removes the limitation for this rather simple use cases. Still, there will be some edge cases in the future that will require some tweaking once again.


Entity Framework Core: Custom Functions (using HasDbFunction)

In the previous post (Custom Functions - using IMethodCallTranslator) we looked at the more flexible approach that requires some boilerplate code. For the usage of a custom function in a project, especially if there are no plans to make the function public then the sacrifice of some (not required) flexibility to reduce the amount of code is fully acceptable.

  1. Entity Framework Core: ROW_NUMBER Support
  2. Entity Framework Core: Custom Functions (using IMethodCallTranslator)
  3. Entity Framework Core: Custom Functions (using HasDbFunction)

As in the previos post we will use the extension method RowVersion with the ORDER BY part only as an example. The actual code can be found on Azure DevOps: Thinktecture.EntityFrameworkCore

Create a static method RowNumber

For this approach the method RowNumber must a static method containing the ORDER BY parameters only, i.e. the method cannot be an extension method for DbFunctions like before.

public static class DbFunctionsExtensions
{
// will throw at runtime because EF tries to translate DbFunctions as well
public static long RowNumber(this DbFunctions _, object orderBy)
{
throw new InvalidOperationException("...");
}

// works as expected
public static long RowNumber(object orderBy)
{
throw new InvalidOperationException("This method is for use with Entity Framework Core only and has no in-memory implementation.");
}
}

Due to new method signature of RowNumber the usage is slightly different as well.

...
.Select(i => new
{
...,
RowNumber = DbFunctionsExtensions.RowNumber(i.ProductId)
})

Introduction of custom function to EF

In the previous approach we had to implement IMethodCallTranslator, IMethodCallTranslatorPlugin and IDbContextOptionsExtension to introduce a new function to EF during the configuration of the (SqlServer)DbContextOptionsBuilder. This time we will skip the IMethodCallTranslator and use the ModelBuilder during OnModelCreating.

The configuration of a new function is encapsulated in an extension method AddRowNumberSupport.

public class DemoDbContext : DbContext
{
...

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

modelBuilder.AddRowNumberSupport();
}
}

I was not totally honest with you at the beginning. It is true that we don't need custom implementation of IMethodCallTranslator (SqlServerRowNumberTranslator) but we need some code from it. I will pretend that the SqlServerRowNumberTranslator does not exist and copy the required code to the extension method AddRowNumberSupport

Like before we fetch a MethodInfo of the method RowNumber first.

public static class ModelBuilderExtensions
{
private static readonly MethodInfo _rowNumberMethod
= typeof(DbFunctionsExtensions)
.GetMethod(nameof(DbFunctionsExtensions.RowNumber),
new[] { typeof(object) });

Then we use HasDbFunction and HasTranslation to introduce the function to EF. Inside of HasTranslation we (re)use the RowNumberExpression from the previous post.

   public static ModelBuilder AddRowNumberSupport(this ModelBuilder modelBuilder)
{
modelBuilder.HasDbFunction(_rowNumberMethod)
.HasTranslation(expressions =>
{
var orderByParams = ExtractParams(expressions.First());

return new RowNumberExpression(orderByParams);
});

return modelBuilder;
}

The method ExtractParams is the same as in the SqlServerRowNumberTranslator.

   private static ReadOnlyCollection<Expression> ExtractParams(Expression parameter)
{
if (parameter is ConstantExpression constant
&& constant.Value is IEnumerable<Expression> enumerable)
{
return enumerable.ToList().AsReadOnly();
}

return new List<Expression> { parameter }.AsReadOnly();
}

Summary

This approach is easier to grasp and the difference in the amout of code is not that big to base a decision on that alone. Use the approach you like the best because the change from one approach to another is easy and requires very litte time.


Entity Framework Core: Custom Functions (using IMethodCallTranslator)

In the previous blog post (Entity Framework Core: ROW_NUMBER Support) we saw how to use the custom function RowVersion with Entity Framework Core (EF). Now, we will look into the implementation.

In the next and the last post of this series we will look ath the 2nd approach that uses the method HasDbFunction in combination with HasTranslation.

  1. Entity Framework Core: ROW_NUMBER Support
  2. Entity Framework Core: Custom Functions (using IMethodCallTranslator)
  3. Entity Framework Core: Custom Functions (using HasDbFunction)

As an example we will use the extension method RowVersion with the ORDER BY part only. The actual code can be found on Azure DevOps: Thinktecture.EntityFrameworkCore

Define a new extension method for DbFunctions

At first we define an extension method RowNumber to be used in a LINQ query. The method itself contains no logic in it because it won't be executed in .NET but translated to SQL.

Actually, it does not matter what class the extension method is defined for but I'm following the pattern the EF team uses for their functions like the methods DateDiffDay or Like.

public static long RowNumber(this DbFunctions _, object orderBy)
{
throw new InvalidOperationException("This method is for use with Entity Framework Core only and has no in-memory implementation.");
}

Having the extension method we are now able to define a RowNumber but this will lead to errors during the execution of the query because EF doesn't know what to do with it.

// Expected output: ROW_NUMBER() OVER(ORDER BY ProductId)
// but will throw an error at this point
...
.Select(i => new
{
...,
RowNumber = EF.Functions.RowNumber(i.ProductId)
})

Provide custom implementation of IMethodCallTranslator

EF needs some help to be able to translate the newly implemented method RowNumber. For that we create a new class and implement the method Translate of the interface IMethodCallTranslator

Expression Translate(MethodCallExpression methodCallExpression);

Our implementation is going to be called with different method call expressions so we need to check whether the method is the one we are interested in or not. For easier check we will save the MethodInfo of RowNumber to a variable.

public class SqlServerRowNumberTranslator : IMethodCallTranslator
{
private static readonly MethodInfo _rowNumberMethod
= typeof(DbFunctionsExtensions).GetMethod(
nameof(DbFunctionsExtensions.RowNumber),
new[] { typeof(DbFunctions), typeof(object) });

In Translate we check the method being passed in and if it's not the RowNumber we return the methodCallExpression unchanged, otherwise we extract the ORDER BY columns.

   public Expression Translate(MethodCallExpression methodCallExpression)
{
if (methodCallExpression.Method != _rowNumberMethod)
       return null;

var orderByParams = ExtractParams(methodCallExpression.Arguments[1]);

Luckily, EF translated the parameter(s) provided to RowVersion (like i.ProductId) to expressions EF understands (like ColumnExpression). The only thing we do is to check whether we have 1 or more "columns".

private static ReadOnlyCollection<Expression> ExtractParams(Expression parameter)
{
if (parameter is ConstantExpression constant
&& constant.Value is IEnumerable<Expression> enumerable)
{
return enumerable.ToList().AsReadOnly();
}

return new List<Expression> { parameter }.AsReadOnly();
}

Back to method Translate.

After having the ORDER BY expressions we return a new custom expression RowNumberExpression that is responsible for generation of the SQL.

      return new RowNumberExpression(orderByParams);
}

The RowNumberExpression derives from Expression and has 2 methods of interest: Accept for rendering SQL and VisitChildren for leting all kind of visitors to visit "our" inner expressions.

First, we implement the method VisitChildren and other few not-that-exciting members.

public class RowNumberExpression : Expression
{
private readonly IReadOnlyCollection<Expression> _orderBy;

public override ExpressionType NodeType => ExpressionType.Extension;
public override Type Type => typeof(long);
public override bool CanReduce => false;

public RowNumberExpression(IReadOnlyCollection<Expression> orderBy)
{
_orderBy = orderBy;
}

protected override Expression VisitChildren(ExpressionVisitor visitor)
{
var visitedOrderBy = visitor.VisitExpressions(_orderBy);

if (ReferenceEquals(_orderBy, visitedOrderBy))
return this;

return new RowNumberExpression(visitedOrderBy);
}

The method VisitExpressions inside VisitChildren is a helper method that iterates over the expressions and calls visitor.Visit(expression). The implementation can be found in ExpressionVisitorExtensions.cs on Azure DevOps.

The remaining method Accept is responsible for the generation of the SQL fragment. Yet again, this method is called with different kind of visitors but we are interested in IQuerySqlGenerator only. Having the SQL generator we let it generate some static SQL fragments and the ORDER BY columns.

protected override Expression Accept(ExpressionVisitor visitor)
{
if (!(visitor is IQuerySqlGenerator))
return base.Accept(visitor);

visitor.Visit(new SqlFragmentExpression("ROW_NUMBER() OVER( ORDER BY "));

RenderColumns(visitor, _orderBy);

visitor.Visit(new SqlFragmentExpression(")"));

return this;
}

private static void RenderColumns(ExpressionVisitor visitor,
IEnumerable<Expression> columns)
{
var insertComma = false;

foreach (var column in columns)
{
if (insertComma)
visitor.Visit(new SqlFragmentExpression(", "));

visitor.Visit(column);
insertComma = true;
}
}

Now, the extension method RowNumber can be translated by the SqlServerRowNumberTranslator to SQL. In theory... because we have to register the translater with EF.

Register custom IMethodCallTranslator with EF

An implementation of IMethodCallTranslator can be registered using an implementation of IMethodCallTranslatorPlugin that has just 1 property Translators.

public class SqlServerMethodCallTranslatorPlugin : IMethodCallTranslatorPlugin
{
public IEnumerable<IMethodCallTranslator> Translators { get; }

public SqlServerMethodCallTranslatorPlugin()
{
Translators = new List<IMethodCallTranslator>
{
new SqlServerRowNumberTranslator()
};
}
}

Now, the plugin has to get into the dependency injection (DI) of the EF. For that we need to implement IDbContextOptionsExtension to get to an instance of IServiceCollection.

Please note that EF has its own DI container by default.

public class SqlServerDbContextOptionsExtension : IDbContextOptionsExtension
{
public string LogFragment => "'RowNumberSupport'=true";

public bool ApplyServices(IServiceCollection services)
{
services.AddSingleton<IMethodCallTranslatorPlugin,
SqlServerMethodCallTranslatorPlugin>();
return false;
}

public long GetServiceProviderHashCode()
{
return 0;
}

public void Validate(IDbContextOptions options)
{
}
}

Last but not least, we need an easy method to add RowNumber support to a DbContext. Due to the fact that this is a SQL Server feature we implement an extension method for SqlServerDbContextOptionsBuilder instead of the general DbContextOptionsBuilder.

public static class SqlServerDbContextOptionsBuilderExtensions
{
public static SqlServerDbContextOptionsBuilder AddRowNumberSupport(
this SqlServerDbContextOptionsBuilder sqlServerOptionsBuilder)
{
var infrastructure = (IRelationalDbContextOptionsBuilderInfrastructure)
sqlServerOptionsBuilder;
var builder = (IDbContextOptionsBuilderInfrastructure)
infrastructure.OptionsBuilder;

// if the extension is registered already then we keep it
      // otherwise we create a new one
var extension = infrastructure.OptionsBuilder.Options
.FindExtension<SqlServerDbContextOptionsExtension>()
?? new SqlServerDbContextOptionsExtension();
builder.AddOrUpdateExtension(extension);

return sqlServerOptionsBuilder;
}
}

Summary

Although there are quite a few components we need to implement a custom function, in the end the amount of code is not that big. Especially, if you consider that the last part (registration with EF) is reusable for all your custom EF features you need.

In the last post of this series we will look at the 2nd approach that requires less code and is less flexible.

UPDATE

  • IMethodCallTranslator.Translate must return null if the method is not the one we wait for so EF continues on with other translators.

Entity Framework Core: ROW_NUMBER Support

There are some use cases that force me to use raw T-SQL instead of using LINQ. One of them is the need for ROW_NUMBER. But not anymore...

One again, due to great work of Entity Framework (EF) team we are able to add more capabilities to EF quite easily. At the moment there are (at least) 2 approaches, the first one is by implementing a custom IMethodCallTranslator the other one is by using the method HasDbFunction.

This blog post is all about the usage of the EF function RowNumber. In the following post we will see how to add a custom function to EF by implementing an IMethodCallTranslator and to register it with EF. The last post of this series will show you the 2nd approach that uses the method HasDbFunction.

  1. Entity Framework Core: ROW_NUMBER Support
  2. Entity Framework Core: Custom Functions (using IMethodCallTranslator)
  3. Entity Framework Core: Custom Functions (using HasDbFunction)

The actual code can be found on Azure DevOps: Thinktecture.EntityFrameworkCore

Activate the RowNumber support

To be able to define a RowNumber in a regular LINQ query we have to install the Nuget package Thinktecture.EntityFrameworkCore.SqlServer first. The package will provide a new extension method AddRowNumberSupport for the SqlServerDbContextOptionsBuilder that we have to call during the configuration of a DbContext.

services
.AddDbContext<DemoDbContext>(builder => builder
.UseSqlServer("conn-string", sqlOptions =>
{
sqlOptions.AddRowNumberSupport();
});

Usage of RowNumber in LINQ

There are 2 method overloads for definition of a RowNumber, one with and one without the PARTITION BY part. In both cases we are using the new extension method overload RowNumber for the DbFunctions.

RowNumber with the ORDER BY part only:

// ORDER BY ProductId
var query = dbContext.OrderItems
.Select(i => new
{
...,
RowNumber = EF.Functions.RowNumber(i.ProductId)
});

// ORDER BY ProductId, OrderId
var query = dbContext.OrderItems
.Select(i => new
{
...,
RowNumber = EF.Functions.RowNumber(new
{
i.ProductId,
i.OrderId
})
});

Use another new extension method Descending for changing the default sort order (ascending).

In the example below I'm using _ instead of a property name because the names on the left side of = don't matter.

// ORDER BY ProductId, OrderId DESC
...
.Select(i => new
{
    ...,
                 RowNumber = EF.Functions.RowNumber(new
                                               {
                                                 i.ProductId,
_ = EF.Functions.Descending(i.OrderId)
})
})

RowNumber with PARTITION BY and ORDER BY:

The second overload of RowNumber has 2 arguments. The first one is for the definition of the PARTITION BY part and the second is for the ORDER BY.

// PARTITION BY ProductId ORDER BY OrderId
...
.Select(i => new
{
...,
RowNumber = EF.Functions.RowNumber(i.ProductId, i.OrderId)
})

// PARTITION BY ProductId, Id ORDER BY OrderId
...
.Select(i => new
{
...,
RowNumber = EF.Functions.RowNumber(new
{
i.ProductId,
i.Id
},
i.OrderId)
})

 

 

In the next blog post we will see how to introduce a new EF function by implementing IMethodCallTranslator.


Entity Framework Core: Contains Check via Temp Tables

Motivation

One of the main queries in my projects is to select multiple database records having a collection of some kind of identifiers. Most of the time it works very well using the method Contains().

List<Guid> ids = ...;

var customers = dbContext.Customers.Where(c => ids.Contains(c.Id));

The query above is translated by the Entity Framework Core (EF Core) to IN clause: WHERE Id IN ('id_1', ... 'id_n')

Most of the time using an IN clause is not a problem but when having a big collection of ids then the transmission of a huge SQL statement and parsing of it will have negative impact on the performance. This is just one use case of many.

In the second use case we have a collection of tuples, say a collection of (customerId, productId). In this case the method Contains can't help us, we have to use a JOIN but joining a query with an in-memory-collection (like a List<>) will lead to downloading all data from database because the JOIN will be done in memory.

List<(Guid customerId, Guid productId)> tuples = ...;

// can cause performance issues
var orderItems = ctx.OrderItems.Join(tuples,
                                     i => new { i.Order.CustomerId, i.ProductId },
                                     t => new {
                                                CustomerId = t.customerId,
                                               ProductId = t.productId
                                             }, (i, t) => i);

In both cases it would be better to Bulk Insert the ids into a temp table to perform a JOIN with it.

Solution

Use the following links to try out the demos:

Before using a temp table with EF Core we have to introduce it to our DbContext using the extension method ConfigureTempTable with desired column type(s).
The extension method returns an instance of QueryTypeBuilder for further configuration but most of the time there is no need for that.

Remarks: For different column types we have to make multiple calls of ConfigureTempTable. For example one temp table with Guid, the other with Guid? (i.e. nullable Guid), the third with a string, etc.

public class DemoDbContext : DbContext
{
   ...

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

      modelBuilder.ConfigureTempTable<Guid>();        // for use case 1
      modelBuilder.ConfigureTempTable<Guid, Guid>();  // for use case 2
   }
}

After introduction of the temp tables we now may use them as often as needed.

The solution for 1st use case:

List<Guid> ids = ...;

var tempTableQuery = await ctx.BulkInsertTempTableAsync(ids);
var customers = await ctx.Customers
                         .Join(tempTableQuery, c => c.Id, t => t.Column1, (c, t) => c)
                         .ToListAsync();

Remarks: Each call of BulkInsertTempTableAsync is using a different temp table name by default so there are no conflicts when using this method multiple times. For more control over temp table creation and the bulk insert via SqlBulkCopy provide an instance of type SqlBulkInsertOptions as the 2nd parameter.

The solution for the 2nd use case:

List<(Guid customerId, Guid productId)> tuples = ...;

var tempTableQuery = await ctx.BulkInsertTempTableAsync(tuples);
var orderItems = await ctx.OrderItems.Join(tempTableQuery,
                                           i => new { i.Order.CustomerId, i.ProductId },
                                           t => new {
                                                     CustomerId = t.Column1,
                                                     ProductId = t.Column2
                                                    }, (i, t) => i) .ToListAsync();

 

The NuGet package will be released in the near future, at the moment I'm gathering feedback.


Entity Framework Core: Isolation of Integration Tests

When working with Entity Framework Core (EF) a lot of code can be tested using the In-Memory database provider but sometimes you want (or have) to go to the real database. For example, you are using not just LINQ but custom SQL statements due to performance reasons or you want to check that a specific exception is thrown by the database under some conditions like when having a primary key violation.

The biggest challenge of integration tests is the isolation of one test from another. In this post we will look at 3 options how to do that.

The code for the 3rd option is on GitHub: PawelGerr/Presentation-EntityFrameworkCore

Remarks: in my demos I'm using 3rd party libs: FluentAssertions and xunit.

Given is a DemoRepository with a method AddProduct that we want to test. (The code kept oversimplified for clarity reasons)

public class DemoRepository
{
...

public void AddProduct(Guid id)
{
_dbContext.Products.Add(new Product { Id = id });
_dbContext.SaveChanges();
}
}

Using Transaction Scopes

EF Core added support for TransactionScope in version 2.1.

The isolation of tests via TransactionScope is very simple just wrap the call AddProduct into a TransactionScope to revert all changes at the end of the test. But, there are few preconditions. The testing method must not starting transactions using BeginTransaction() or it has to use a TransactionScope as well.

Also, I recommend to read my other blog post: Entity Framework Core: Use TransactionScope with Caution!

public DemoRepositoryTests()
{
_dbContext = CreateDbContext();
_repository = new DemoRepository(_dbContext);
}
[Fact]
public void Should_add_new_product()
{
var productId = new Guid("DBD9439E-6FFD-4719-93C7-3F7FA64D2220");

using(var scope = new TransactionScope())
{
_repository.AddProduct(productId);

_dbContext.Products.FirstOrDefault(p => p.Id == productId).Should().NotBeNull();

// the transaction is going to be rolled back because the scope is not completed
// scope.Complete();
}
}

Using new Databases

Creating a new database for each test is very easy but the tests are very time consuming. On my machine each test takes about 10 seconds to create and to delete a database on the fly.

The steps of each test are: generate a new database name, create the database by running EF migrations and delete the database in the end.

public class DemoRepositoryTests : IDisposable
{
private readonly DemoDbContext _dbContext;
private readonly DemoRepository _repository;
private readonly string _databaseName;

public DemoRepositoryTests()
{
_databaseName = Guid.NewGuid().ToString();

var options = new DbContextOptionsBuilder<DemoDbContext>()
.UseSqlServer($"Server=(local);Database={_databaseName};...")
.Options;

_dbContext = new DemoDbContext(options);
_dbContext.Database.Migrate();

_repository = new DemoRepository(_dbContext);
}

// Tests come here

public void Dispose()
{
_dbContext.Database.ExecuteSqlCommand((string)$"DROP DATABASE [{_databaseName}]");
}
}

Using different Database Schemas

The 3rd option is to use the same database but different schemas. The creation of a new schema and running EF migrations usually takes less than 50 ms, which is totally acceptable for an integration test. The prerequisites to run queries with different schemas are schema-aware instances of DbContext and schema-aware EF migrations. Read my blog posts for more information about how to change the database schema at runtime:

The class executing integration tests consists of 2 parts: creation of the tables in constructor and the deletion of them in Dispose().

I'm using a generic base class to use the same logic for different types of DbContext.

In the constructor we generate the name of the schema using Guid.NewGuid(), create DbContextOptions using DbSchemaAwareMigrationAssembly and DbSchemaAwareModelCacheKeyFactory described in my previous posts, create the DbContext and run the EF migrations. The database is now fully prepared for executing tests. After execution of the tests the EF migrations are rolled back using IMigrator.Migrate("0"), the EF history table __EFMigrationsHistory is deleted and newly generated schema is dropped.

public abstract class IntegrationTestsBase<T> : IDisposable
where T : DbContext
{
private readonly string _schema;
private readonly string _historyTableName;
private readonly DbContextOptions<T> _options;

protected T DbContext { get; }

protected IntegrationTestsBase()
{
_schema = Guid.NewGuid().ToString("N");
_historyTableName = "__EFMigrationsHistory";

_options = CreateOptions();
DbContext = CreateContext();
DbContext.Database.Migrate();
}

protected abstract T CreateContext(DbContextOptions<T> options,
IDbContextSchema schema);

protected T CreateContext()
{
return CreateContext(_options, new DbContextSchema(_schema));
}

private DbContextOptions<T> CreateOptions()
{
return new DbContextOptionsBuilder<T>()
.UseSqlServer($"Server=(local);Database=Demo;...",
builder => builder.MigrationsHistoryTable(_historyTableName, _schema))
.ReplaceService<IMigrationsAssembly, DbSchemaAwareMigrationAssembly>()
.ReplaceService<IModelCacheKeyFactory, DbSchemaAwareModelCacheKeyFactory>()
.Options;
}

public void Dispose()
{
DbContext.GetService<IMigrator>().Migrate("0");
DbContext.Database.ExecuteSqlCommand(
(string)$"DROP TABLE [{_schema}].[{_historyTableName}]");
DbContext.Database.ExecuteSqlCommand((string)$"DROP SCHEMA [{_schema}]");

DbContext?.Dispose();
}
}

The usage of the base class looks as follows

public class DemoRepositoryTests : IntegrationTestsBase<DemoDbContext>
{
private readonly DemoRepository _repository;

public DemoRepositoryTests()
{
_repository = new DemoRepository(DbContext);
}

protected override DemoDbContext CreateContext(DbContextOptions<DemoDbContext> options,
IDbContextSchema schema)
{
return new DemoDbContext(options, schema);
}

[Fact]
public void Should_add_new_product()
{
var productId = new Guid("DBD9439E-6FFD-4719-93C7-3F7FA64D2220");

_repository.AddProduct(productId);

DbContext.Products.FirstOrDefault(p => p.Id == productId).Should().NotBeNull();
}
}

 

Happy testing! 


Entity Framework Core: Changing DB Migration Schema at Runtime

In the first part of this short blog post series we looked at how to change the database schema of a DbContext, now it is all about changing the schema of the EF Core Migrations at runtime.

The samples are on Github: PawelGerr/Presentation-EntityFrameworkCore

Given is a DemoDbContext implementing our interface IDbContextSchema from the first part of this series.

public interface IDbContextSchema
{
string Schema { get; }
}
public class DemoDbContext : DbContext, IDbContextSchema
{
public string Schema { get; }

public DbSet<Product> Products { get; set; }

...
}

At first we create a migration the usual way: dotnet ef migrations add Initial_Migration

And we get the following:

public partial class Initial_Migration : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable("Products",
table => new { Id = table.Column<Guid>() },
constraints: table => table.PrimaryKey("PK_Products", x => x.Id));
}

protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable("Products");
}
}

Next, we add a constructor to provide the migration with IDbContextSchema and pass the schema to CreateTable and DropTable.

public partial class Initial_Migration : Migration
{
private readonly IDbContextSchema _schema;

public Initial_Migration(IDbContextSchema schema)
{
_schema = schema ?? throw new ArgumentNullException(nameof(schema));
}

protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable("Products",
table => new { Id = table.Column<Guid>() },
constraints: table => table.PrimaryKey("PK_Products", x => x.Id),
schema: _schema.Schema);
}

protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable("Products", _schema.Schema);
}
}

If we try to run the migration then we get a MissingMethodException: No parameterless constructor defined for this object. because EF Core needs a parameterless constructor to be able to create an instance of the migration. Luckily, we can adjust the part that is responsible for the creation of new instances. For that we derive from MigrationsAssembly and override the method CreateMigration. In CreateMigration we check if the migration requires an instance of IDbContextSchema and whether the current DbContext is implementing this interface. If so, then we create new instance of the migration by ourselves and return this instance to the caller, otherwise we pass the call to the default implementation.

public class DbSchemaAwareMigrationAssembly : MigrationsAssembly
{
private readonly DbContext _context;

public DbSchemaAwareMigrationAssembly(ICurrentDbContext currentContext,
        IDbContextOptions options, IMigrationsIdGenerator idGenerator,
        IDiagnosticsLogger<DbLoggerCategory.Migrations> logger)
: base(currentContext, options, idGenerator, logger)
{
_context = currentContext.Context;
}

public override Migration CreateMigration(TypeInfo migrationClass,
        string activeProvider)
{
if (activeProvider == null)
throw new ArgumentNullException(nameof(activeProvider));

var hasCtorWithSchema = migrationClass
            .GetConstructor(new[] { typeof(IDbContextSchema) }) != null;

if (hasCtorWithSchema && _context is IDbContextSchema schema)
{
var instance = (Migration)Activator.CreateInstance(migrationClass.AsType(), schema);
instance.ActiveProvider = activeProvider;
return instance;
}

return base.CreateMigration(migrationClass, activeProvider);
}
}

The last step is to register the DbSchemaAwareMigrationAssembly with the dependency injection of EF Core.

Remarks: to change the schema (or the table name) of the migration history table you have to use the method MigrationsHistoryTable

var optionsBuilder = new DbContextOptionsBuilder<DemoDbContext>()
.UseSqlServer("..."
                      // optional
//, b => b.MigrationsHistoryTable("__EFMigrationsHistory", schema)
                          )
.ReplaceService<IModelCacheKeyFactory, DbSchemaAwareModelCacheKeyFactory>()
.ReplaceService<IMigrationsAssembly, DbSchemaAwareMigrationAssembly>();

 

That's all!  


Entity Framework Core: Use TransactionScope with Caution!

One of the new features of Entity Framework Core 2.1 is the support of TransactionScopes. The usage of a TransactionScope is very easy, just put a new instance in a using, write the code inside the block and when you are finished then call Complete() to commit the transaction:

using (var scope = new TransactionScope())
{
var groups = MyDbContext.ProductGroups.ToList();

scope.Complete();
}

But, before changing your code from using BeginTransaction() to TransactionScope you should know some issues caused by them.

The demos are on GitHub: github.com/PawelGerr/Presentation-EntityFrameworkCore

In all examples we will select ProductGroups from a DemoDbContext.

public class DemoDbContext : DbContext
{
public DbSet<ProductGroup> ProductGroups { get; set; }

public DemoDbContext(DbContextOptions<DemoDbContext> options)
: base(options)
{
}
}

public class ProductGroup
{
public Guid Id { get; set; }
public string Name { get; set; }
}

Async methods

EF has for (almost?) every synchronous operation an asynchronous one. So, it is nothing special (even recommended) to use async-await for I/O operations.

In the first example we are using await inside a TransactionScope.

using (var scope = new TransactionScope())
{
var groups = await Context.ProductGroups.ToListAsync().ConfigureAwait(false);
}

Looks harmless but it throws a System.InvalidOperationException: A TransactionScope must be disposed on the same thread that it was created.

The reason is that the TransactionScope doesn't flow from one thread to another by default. To fix that we have to use TransactionScopeAsyncFlowOption.Enabled:

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
var groups = await Context.ProductGroups.ToListAsync().ConfigureAwait(false);
}

Does it work now? It depends.

If the calls with and without TransactionScopeAsyncFlowOption are using the same database connection and the call without the option is executed first, then we get another exception: System.InvalidOperationException: Connection currently has transaction enlisted. Finish current transaction and retry.

In other words, the first call is the culprit but the second one breaks:

try
{
using (var scope = new TransactionScope())
{ // We know this one - System.InvalidOperationException:
// A TransactionScope must be disposed on the same thread that it was created.
var groups = await Context.ProductGroups.ToListAsync().ConfigureAwait(false);
}
}
catch (Exception e)
{
// error handling
}

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
// Implemented correctly but throws anyways // System.InvalidOperationException:
// Connection currently has transaction enlisted. Finish current transaction and retry.
var groups = await Context.ProductGroups.ToListAsync().ConfigureAwait(false);
}

Imagine the first call is done in a 3rd party lib or a framework you are using, i.e. you don't know the code - you will be searching for the cause forever, if you haven't seen this error before.

BeginTransaction within TransactionScope

The transaction scopes can be nested. For example, if the outer scope is rolled back then the changes made in the inner scope are reverted as well. The following example works without problems:

using (var scope = new TransactionScope())
{
// some code
Do();
}

public void Do()
{
using (var anotherScope = new TransactionScope())
{
var groups = Context.ProductGroups.ToList();
}
}

Let's try to change the inner scope to BeginTransaction().

using (var scope = new TransactionScope())
{
// some code
Do();
}

public void Do()
{
using (var tx = Context.Database.BeginTransaction())
{
var groups = Context.ProductGroups.ToList();
}
}

The shown use case is not supported, and we get a System.InvalidOperationException: An ambient transaction has been detected. The ambient transaction needs to be completed before beginning a transaction on this connection.

 Yet again, if Do() is part of a 3rd party lib or a framework then this method has be moved out of outer TransactionScope.

Multiple instances of DbContext (or rather DB connections)

Depending on the project we could end up having multiple instances of DbContext. The instances could be of the same or different type and it may be that the other context doesn't even belong to your application but is being used by a framework you are using.

The use case is the following, we are having a TransactionScope with 2 database accesses using different database connections.

using (var scope = new TransactionScope())
{
var groups = Context.ProductGroups.ToList();
var others = AnotherCtx.SomeEntities.ToList();
}

This use case is not supported as well because a distributed transaction coordinator is required and there is none besides on Windows, so EF team has dropped the support altogether. The exception we get on Windows and Linux is System.PlatformNotSupportedException: This platform does not support distributed transactions.

Conclusion 

The issues mentioned in this blog post are neither new nor specific to Entity Framework Core. I recommend putting some research into this matter before deciding to use or not to use transaction scopes.


Entity Framework Core: Changing Database Schema at Runtime

At the moment there is no built-in support for changing the database schema at runtime. Luckily, Entity Framework Core (EF) provides us with the right tools to implement it by ourselves.

The demos are on GitHub: github.com/PawelGerr/Presentation-EntityFrameworkCore

Given are a database context DemoDbContext and an entity Product.

public class DemoDbContext : DbContext
{
public DbSet<Product> Products { get; set; }

public DemoDbContext (DbContextOptions<DemoDbContext> options)
: base(options)
{
}
}
public class Product
{
public Guid Id { get; set; }
}

There are 2 ways to change the schema, either by applying the TableAttribute or by implementing the interface IEntityTypeConfiguration<TEntity>.

The first option won't help us because the schema is hard-coded.

[Table("Products", Schema = "demo")]
public class Product
{
public Guid Id { get; set; }
}

The second option gives us the ability to provide the schema from DbContext to the EF model configuration. At first we implement the entity configuration for Product.

public class ProductEntityConfiguration : IEntityTypeConfiguration<Product>
{
private readonly string _schema;

public ProductEntityConfiguration(string schema)
{
_schema = schema;
}

public void Configure(EntityTypeBuilder<Product> builder)
{
if (!String.IsNullOrWhiteSpace(_schema))
builder.ToTable(nameof(DemoDbContext.Products), _schema);

builder.HasKey(product => product.Id);
}
}

Now we use the entity configuration in OnModelCreating and pass the schema to it via constructor. Additionally, we create the interface IDbContextSchema containing just the schema (i.e. a string) to be able to inject it into DemoDbContext.

public interface IDbContextSchema
{
string Schema { get; }
}
// DbContext implements IDbContextSchema as well
// so we know it is "schema-aware"
public class DemoDbContext : DbContext, IDbContextSchema
{
public string Schema { get; }

public DbSet<Product> Products { get; set; }

public DemoDbContext(DbContextOptions<DemoDbContext> options,
IDbContextSchema schema = null)
: base(options)
{
Schema = schema?.Schema;
}

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

modelBuilder.ApplyConfiguration(new ProductEntityConfiguration(Schema));
}
}

We are almost done. The last task is to change how EF is caching database model definitions. By default just the type of the DbContext is used but we need to differentiate the models not just by type but by the schema as well. For that we implement the interface IModelCacheKeyFactory.

public class DbSchemaAwareModelCacheKeyFactory : IModelCacheKeyFactory
{
public object Create(DbContext context)
{
return new {
        Type = context.GetType(),
        Schema = context is IDbContextSchema schema ? schema.Schema : null
    };
}
}

No we have to replace the default implementation with ours and to register the IDbContextSchema. In current example the IDbContextSchema is just a singleton but it can be provided by anything we want like read from a database or derived from a JWT bearer token during an HTTP request, etc.

IServiceCollection services = ...;

services
.AddDbContext<DemoDbContext>(
     builder => builder.UseSqlServer("...")
                  .ReplaceService<IModelCacheKeyFactory, DbSchemaAwareModelCacheKeyFactory>())
  .AddSingleton<IDbContextSchema>(new DbContextSchema("demo"));

--------------------------------------------

// just a helper class public class DbContextSchema : IDbContextSchema
{
public string Schema { get; }

public DbContextSchema(string schema)
{
Schema = schema ?? throw new ArgumentNullException(nameof(schema));
}
}

 

Voila! 

 

PS: There is one special use case for that feature - isolation of integration tests due to missing support of ambient transactions. For that we need schema-aware migrations we will look at in the next blog post.

Stay tuned!