Previous month:
July 2019

August 2019

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.