Your use case is very similar to mine when I needed ROW_NUMBER support in EF Core.
Example:
// gets translated to
// ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY OrderId, Count)
DbContext.OrderItems.Select(o => new {
RowNumber = EF.Functions.RowNumber(o.ProductId, new {
o.OrderId,
o.Count
})
})
Use anonymous classes instead of arrays
The first thing you have to do is to switch from using an array to an anonymous class, i.e. you change the call from
DbContext.FullText(new[] { x.Col1, x.Col2, x.Col3 }, "keywords")
to
DbContext.FullText(new { x.Col1, x.Col2, x.Col3 }, "keywords")
The sort order of the parameters will stay as it is defined by in the query,
i.e new { x.Col1, x.Col2 } will be translated to Col1, Col2
and new { x.Col2, x.Col1 } to Col2, Col1.
You can even to the following: new { x.Col1, _ = x.Col1, Foo = "bar" } that is going to be translated to Col1, Col1, 'bar'.
Implement custom IMethodCallTranslator
If you need some hints then you can look through my code on Azure DevOps: RowNumber Support or if you can wait a few days then I will provide a blog post about the implementation of custom functions.
Updated (31 july, 2019)
Blog posts:
Updated (july 27, 2019)
Thanks to the comments below I see that some clarification is required.
1) As pointed out in the comment below there is another approach. With HasDbFunction I could save me some typing like the code for registration of the translator with EF but I would still need the RowNumberExpression because the function has 2 sets of parameters (for PARTITION BY and ORDER BY) and the existing SqlFunctionExpression doesn't support that. (or did I missed something?) The reason I've chosen the approach with IMethodCallTranslator is because I want the configuration of this feature to be done during setting up of the DbContextOptionsBuilder and not in OnModelCreating. That is, it’s a personal preference of mine.
In the end the thread creator can use HasDbFunction to implement the desired feature as well. In my case the code would look something like the following:
// OnModelCreating
var methodInfo = typeof(DemoDbContext).GetMethod(nameof(DemoRowNumber));
modelBuilder.HasDbFunction(methodInfo)
.HasTranslation(expressions => {
var partitionBy = (Expression[])((ConstantExpression)expressions.First()).Value;
var orderBy = (Expression[])((ConstantExpression)expressions.Skip(1).First()).Value;
return new RowNumberExpression(partitionBy, orderBy);
});
// the usage with this approach is identical to my current approach
.Select(c => new {
RowNumber = DemoDbContext.DemoRowNumber(
new { c.Id },
new { c.RowVersion })
})
2) An anonymous type can’t enforce the type(s) of its members, so you can get a runtime exception if the function is called with, say, integer instead of string. Still, it can be valid solution. Depending on the customer you are working for the solution may be more or less viable, in the end the decision lies with the customer. Not providing any alternatives is a possible solution as well but not a satisfying one.
Especially, if the usage of SQL is not desired (because you get even less support from compiler) so the runtime exception may be a good compromise after all.
But, if the compromise is still not acceptable then we can make a research on how to add support for arrays.
First approach could be the implementation of a custom IExpressionFragmentTranslator to “redirect” the handling of arrays to us.
Please note, it is just a prototype and needs more investigation/testing :-)
// to get into EF pipeline
public class DemoArrayTranslator : IExpressionFragmentTranslator
{
public Expression Translate(Expression expression)
{
if (expression?.NodeType == ExpressionType.NewArrayInit)
{
var arrayInit = (NewArrayExpression)expression;
return new DemoArrayInitExpression(arrayInit.Type, arrayInit.Expressions);
}
return null;
}
}
// lets visitors visit the array-elements
public class DemoArrayInitExpression : Expression
{
private readonly ReadOnlyCollection<Expression> _expressions;
public override Type Type { get; }
public override ExpressionType NodeType => ExpressionType.Extension;
public DemoArrayInitExpression(Type type,
ReadOnlyCollection<Expression> expressions)
{
Type = type ?? throw new ArgumentNullException(nameof(type));
_expressions = expressions ?? throw new ArgumentNullException(nameof(expressions));
}
protected override Expression Accept(ExpressionVisitor visitor)
{
var visitedExpression = visitor.Visit(_expressions);
return NewArrayInit(Type.GetElementType(), visitedExpression);
}
}
// adds our DemoArrayTranslator to the others
public class DemoRelationalCompositeExpressionFragmentTranslator
: RelationalCompositeExpressionFragmentTranslator
{
public DemoRelationalCompositeExpressionFragmentTranslator(
RelationalCompositeExpressionFragmentTranslatorDependencies dependencies)
: base(dependencies)
{
AddTranslators(new[] { new DemoArrayTranslator() });
}
}
// Register the translator
services
.AddDbContext<DemoDbContext>(builder => builder
.ReplaceService<IExpressionFragmentTranslator,
DemoRelationalCompositeExpressionFragmentTranslator>());
For testing I introduced another overload containing Guid[] as parameter.
Although, this method doesn't make sense in my use case at all :)
public static long RowNumber(this DbFunctions _, Guid[] orderBy)
And adjusted the usage of the method
// Translates to ROW_NUMBER() OVER(ORDER BY Id)
.Select(c => new {
RowNumber = EF.Functions.RowNumber(new Guid[] { c.Id })
})
public static bool FullText(string[] propertyReferences, string fullText)– Whitehurstnew [] { … }is not supported inside expressions, so our translation won't be called at all. And unfortunately you can't create the array outside the query because you need access toxfromx =>. Creating a function with several overloads(string, string),(string, string, string),(string, string, string, string)etc. though is relatively easy. Let me know if that works for you. Because handlingnew [] {… }argument will require pugging deeply into EF Core infrastructure. – Dillon