Entity Framework Core: Beware of Lifetime Extension of TransactionScopes using C# 8.0

In the blog post Entity Framework Core: Use TransactionScope with Caution! we saw a few cases we have to be aware of when using TransactionsScopes. Here is another one that can lead to an exception when using the new C# 8.0 feature: using declarations.

First, we look at a code snippet without C# 8.0 feature:

using (var scope = new TransactionScope())
{
scope.Complete();
}

myDbContext.Products.ToList();

Which doesn't behave the same if we blindly apply the new C# 8.0 feature:

using var scope = new TransactionScope();

scope.Complete();

// System.InvalidOperationException: The current TransactionScope is already complete.
myDbContext.Products.ToList();

because it is not enough to Complete the TransactionScope to be able to use the underlying database connection for further queries, the TransactionScope must be disposed first. In the 2nd code snippet the ToList() is executed after Complete but before Dispose.

The corresponding code without using declaration looks like as following:

using (var scope = new TransactionScope())
{
scope.Complete();

// System.InvalidOperationException: The current TransactionScope is already complete.
myDbContext.Products.ToList();
}

This may be unexpected because the TransactionScope behaves differently than the regular IDbContextTranslation started with myDbContext.Database.BeginTransaction().  So this one does not lead to an error:

using var tx = ArrangeDbContext.Database.BeginTransaction();

tx.Commit();

myDbContext.Products.ToList();

 


Entity Framework Core: Include Filters

The Entity Framework Core (EF) extension method Include provides us the ability to load additional data besides the entities we are querying for. For example: loading products along with their translations.

var products = Context.Products
.Include(p => p.Translations)
.ToList();

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

public class Product
{
public Guid Id { get; set; }
public string Name { get; set; }
public List<ProductTranslation> Translations { get; set; }
}

public class ProductTranslation
{
public Guid ProductId { get; set; }
public string Locale { get; set; }
public string Description { get; set; }
}

In some use cases we want to load all translations for the requested products and in some cases we don't. There are plenty of times when we need translations for a specific locale (i.e. language) only but at the moment there is no built-in support for providing filters to entities being included. You may go to the corresponding github issue#1833 and vote for this feature to increase the priority of it.

Some of our options are:

Entity Framework Plus

Not much to say here. Install the nuget package and use the extension method IncludeFilter.

var products = Context.Products
.IncludeFilter(p => p.Translations.Where(t => t.Locale == "en"))
.ToList();

Please note that the generated queries will be different than when using built-in features of EF! To be aware of that is helpful especially during performance optimization, like finding appropriate database indexes.

Global Query Filters

The global query filters were introduced in EF 2.0 and are primarily for realization of features like soft delete and multi-tenancy. Although the filters are coupled to the entity type(s) and not to specific queries, still, we may enable and disable them at any time. Furthermore, despite the name the filters don't have to be "global", i.e. we can enable them for a specific instance of a DbContext only and not for all of them.

First, we introduce a variable that will be our filter and configure the corresponding query filter.

public class DemoDbContext : DbContext
{
private string _localeFilter;

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

modelBuilder.Entity<ProductTranslation>()
.HasQueryFilter(t => _localeFilter == null
|| t.Locale == _localeFilter);

}

For setting the filter we could make the variable _localeFilter public but the API would not be that nice and robust, that's why ...

The method for enabling/setting the filter comes next.

   // we are still in DemoDbContext
public IDisposable SetTranslationFilter(string locale)
{
if (locale == null)
throw new ArgumentNullException(nameof(locale));
if (_localeFilter != null)
throw new InvalidOperationException($"Changing a filter is not allowed.");

_localeFilter = locale;

return new FilterReset(this);
}

SetTranslationFilter is returning an IDisposable to not to forget to reset the filter. The FilterReset is a nested private struct inside DemoDbContext.

   private readonly struct FilterReset : IDisposable
{
private readonly DemoDbContext _ctx;

public FilterReset(DemoDbContext ctx)
{
_ctx = ctx ?? throw new ArgumentNullException(nameof(ctx));
}

public void Dispose()
{
_ctx._localeFilter = null;
}
}

We are done with the implementation of the filter. The usage is looks as following: 

using (Context.SetTranslationFilter(locale))
{
var products = Context.Products
.Include(p => p.Translations)
.ToList();
}

When using the global query filters in that manner then it is impossible that this filter is applied to other queries by accident because (1) the filter is bound to a specific instance of a DbContext and (2) a DbContext is not thread-safe so it must not be used in multiple threads concurrently.

 

 


ASP.NET Core 3.0: Custom JsonConverter for the new System.Text.Json

With the introduction of ASP.NET Core 3.0 the default JSON serializer has been changed from Newtonsoft.Json to System.Text.Json. For projects and libraries switching to the new JSON serializer this change means more performance and the opportunity to rewrite our JsonConverters.

Serialization of concrete classes

Let's start with a simple one that can (de)serialize a concrete class Category. In our example we (de)serialize the property Name only.

public class Category
{
public string Name { get; }

public Category(string name)
{
Name = name;
}
}

To implement a custom JSON converter we have to derive from the generic class JsonConverter<T> and to implement 2 methods: Read and Write.

public class CategoryJsonConverter : JsonConverter<Category>
{
public override Category Read(ref Utf8JsonReader reader,
Type typeToConvert,
JsonSerializerOptions options)
{
var name = reader.GetString();

return new Category(name);
}

public override void Write(Utf8JsonWriter writer,
Category value,
JsonSerializerOptions options)
{
writer.WriteStringValue(value.Name);
}
}

The method Read is using the Utf8JsonReader to fetch a string, i.e. the name, and the method Write is writing a string using an instance of Utf8JsonWriter.

In both cases (i.e. during serialization and deserialization) the converter is not being called if the value is null so I skipped the null checks. The .NET team doesn't do null checks either, see JsonKeyValuePairConverter<TKey, TValue>.

Let's test the new JSON converter. For that we create an instance of JsonSerializerOptions and add our CategoryJsonConverter to the Converters collection. Next, we use the static class JsonSerializer to serialize and to deserialize an instance of Category.

Category category = new Category("my category");

var serializerOptions = new JsonSerializerOptions
{
    Converters = { new CategoryJsonConverter() }
};

// json = "my category"
var json = JsonSerializer.Serialize(category, serializerOptions);

// deserializedCategory.Name = "my category"
var deserializedCategory = JsonSerializer.Deserialize<Category>(json, serializerOptions);

Serialization of generic classes

The next example is slightly more complex. The property we are serializing is a generic type argument, i.e. we can't use methods like reader.GetString() or writer.WriteStringValue(name) because we don't know the type at compile time.

In this example I've changed the class Category to a generic type and renamed the property Name to Key:

public class Category<T>
{
public T Key { get; }

public Category(T key)
{
Key = key;
}
}

For serialization of the generic property Key we need to fetch a JsonSerializer<T> using the instance of JsonSerializerOptions.

public class CategoryJsonConverter<T> : JsonConverter<Category<T>>
{
public override Category<T> Read(ref Utf8JsonReader reader,
Type typeToConvert,
JsonSerializerOptions options)
{
var converter = GetKeyConverter(options);
var key = converter.Read(ref reader, typeToConvert, options);

return new Category<T>(key);
}

public override void Write(Utf8JsonWriter writer,
Category<T> value,
JsonSerializerOptions options)
{
var converter = GetKeyConverter(options);
converter.Write(writer, value.Key, options);
}

private static JsonConverter<T> GetKeyConverter(JsonSerializerOptions options)
{
var converter = options.GetConverter(typeof(T)) as JsonConverter<T>;

if (converter is null)
throw new JsonException("...");

return converter;
}
}

The behavior of the generic JSON converter is the same as before especially if the Key is of type string.

Deciding the concrete JSON converter at runtime

Having several categories with different key types, say, string and int, we need to register them all with the JsonSerializerOptions.

var serializerOptions = new JsonSerializerOptions
{
Converters =
{
new CategoryJsonConverter<string>(),
new CategoryJsonConverter<int>()
}
};

If the number of required CategoryJsonConverters grows to big or the concrete types of the Key are not known at compile time then this approach is not an option. To make this decision at runtime we need to implement a JsonConverterFactory. The factory has 2 method: CanConvert(type) that returns true if the factory is responsible for the serialization of the provided type; and CreateConverter(type, options) that should return an instance of type JsonConverter.

public class CategoryJsonConverterFactory : JsonConverterFactory
{
public override bool CanConvert(Type typeToConvert)
{
if (!typeToConvert.IsGenericType)
return false;

var type = typeToConvert;

if (!type.IsGenericTypeDefinition)
type = type.GetGenericTypeDefinition();

return type == typeof(Category<>);
}

public override JsonConverter CreateConverter(Type typeToConvert,
JsonSerializerOptions options)
{
var keyType = typeToConvert.GenericTypeArguments[0];
var converterType = typeof(CategoryJsonConverter<>).MakeGenericType(keyType);

return (JsonConverter)Activator.CreateInstance(converterType);
}
}

Now, we can remove all registrations of the CategoryJsonConverter<T> from the options and add the newly implemented factory.

Category<int> category = new Category<int>(42);

var serializerOptions = new JsonSerializerOptions
{
    Converters = { new CategoryJsonConverterFactory() }
};

// json = 42
var json = JsonSerializer.Serialize(category, serializerOptions);

// deserialized.Key = 42
var deserialized = JsonSerializer.Deserialize<Category<int>>(json, serializerOptions);

 

In the end the implementation of a custom converter for System.Text.Json is very similar to the one for Newtonsoft.Json. The biggest difference here is the non-existence of a non-generic JsonConverter but for that we've got the JsonConverterFactory.

Actually, there is a non-generic JsonConverter which is the base class of the JsonConverter<T> and the JsonConverterFactory but we cannot (and should not) use this class directly because its constructor is internal.

 


C# 8: Nullable Reference Types in .NET Standard < 2.1 and .NET 4.x

By switching the LangVersion to 8.0 and setting Nullable to enable in the csproj-file we are now benefiting from the nullable reference types introduced with the latest version of C#. By enabling this new feature all type members, input and output parameters are considered to be not-null. If some members or parameters, like string value can be null then we need to put a ? at the end of the type: string? value.

For most use cases putting a ? is enough but sometimes we need more control over when a parameter is null and when it isn't. To do so we can use one of the new attributes from the namespace System.Diagnostics.CodeAnalysis, some of them are NotNullWhen and NotNullIfNotNull.

Here is an example to make the benefits of one of the attributes more clear:

[return: NotNullIfNotNull("value")]
public static string? Reverse(this string? value)
{
if (value == null)
return null;

return new String(Enumerable.Reverse(value).ToArray());
}

The NotNullIfNotNull states that if the parameter value is not null then the output will be not null as well. Thanks to the annotation we get a corresponding warning when trying to access a null reference.

string value1 = null;
var firstChar1 = value1.Reverse()[0]; // Warn: Dereference of a possibly null reference.

string value2 = "123";
var firstChar2 = value2.Reverse()[0]; // No warning

The problem is, this attributes are available in projects referencing .NET Standard 2.1 or .NET Core 3.0. So, having a class library targeting multiple frameworks like .NET Standard 2.0, .NET Standard 2.1 and .NET 4.8 we are kind of limited to the ? only, or so it seems.

The solution looks the same as with Jetbrains.Annotations, we copy the code of the required attributes into our project and make them internal:

#if !NETSTANDARD2_1

namespace System.Diagnostics.CodeAnalysis
{
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Parameter | AttributeTargets.ReturnValue, AllowMultiple = true)]
internal sealed class NotNullIfNotNullAttribute : Attribute
{
public string ParameterName { get; }

public NotNullIfNotNullAttribute(string parameterName)
{
ParameterName = parameterName;
}
}
}
#endif

The null-checks are now available in projects targeting the "older" platforms as well.


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.