We implement our operator in the Entity Framework Core

Once, on an overcast March Saturday morning, I decided to see how things are going with Microsoft in the good work of transforming the Entity Framework mastodon into the Entity Framework Core. Exactly a year ago, when our team started a new project and selected ORM, it was itching to use everything as stylish and youthful as possible. However, after looking at the EFC, we realized that it was still very far from production. There are a lot of problems with N + 1 queries (greatly improved in version 2), nested select curves (fixed in 2.1.0-preview1 ), Many-to-Many support (still not) and cherry on the cake - no support for DbGeometry, that in our project was very critical. It is noteworthy that the last feature is in the road map of the project since 2015 in the list of high-priority. Our team even has a joke on this topic: "We will add this task to the list of high-priority ones." And now, one year has passed since the last EFC revision, the second version of this product has already been released and I decided to check how things are going.

In my opinion, one of the best ways to test a product is to try to extend it with some custom features. This immediately sheds light on: a) the quality of the architecture; b) the quality of the documentation; c) community support.

A quick look at the first page of Google’s issue showed that full-text search in EFC is not yet supported, but there are plans. Great, this is what we need, you can try to implement the CONTAINS predicate from T-SQL yourself.

We invent API

I did not bother with complex ways and just declared an extension method for strings:

 public static class StringExt { public static bool ContainsText(this string text, string sub) { throw new NotImplementedException("This method is not supposed to run on client"); } } 

In the body of the method, we simply throw an exception, because it is just a marker, not intended to run on the client. In custom code, it should look something like this:

 dbContext.Posts.Where(x => x.Content.ContainsText("egg")); 

left to figure out how to implement it.

Search for extension points

This is more complicated. Google on request "ef core create custom operator" provides only a link to the topic from the project github, ending with a message like "hey, any updates on that?". It is also proposed to run the SQL query with your hands, which would certainly work, but this is not our option.

The best way to do something new is to do it by analogy. What is the closest operator that we want to implement? That's right, LIKE . The LIKE operator is translated from the String.Contains method. All we need to do is peek at how this is done by the EFC developers.

Download the repository, open it in Visual Studio 2017 and ... Visual Studio goes into a dead spin. Well, ok, fat IDE for amateurs, we take Visual Studio Code, everything flies there. Moreover, Code Lens works out of the box, just amazing.

Find the files that contain the Contains in the name, SqlServerContainsOptimizedTranslator.cs is our candidate. I wonder what is so optimized in it? It turns out that EFC, in contrast to EF, uses CHARINDEX > 0 instead of LIKE '%pattern%' .

This post on SO questions the decision of the EFC team.

Code Lens tells us that SqlServerContainsOptimizedTranslator used only in one place - SqlServerCompositeMethodCallTranslator.cs . Bingo! This class is inherited from RelationalCompositeMethodCallTranslator and, judging by its name, translates the call to .NET methods into a SQL query, which is what we need! You just need to expand this class and add another our custom translator to its list.

We write your translator

The translator must implement the IMethodCallTranslator interface. The contract he has to execute in the Expression Translate(MethodCallExpression methodCallExpression) method Expression Translate(MethodCallExpression methodCallExpression) is quite simple: if the input expression is not known, we return null, otherwise we convert the expression to Sql.
This is what the class looks like:

 public class FreeTextTranslator : IMethodCallTranslator { private static readonly MethodInfo _methodInfo = typeof(StringExt).GetRuntimeMethod(nameof(StringExt.ContainsText), new[] {typeof(string), typeof(string)}); public Expression Translate(MethodCallExpression methodCallExpression) { if (methodCallExpression.Method != _methodInfo) return null; var patternExpression = methodCallExpression.Arguments[1]; var objectExpression = (ColumnExpression) methodCallExpression.Arguments[0]; var sqlExpression = new SqlFunctionExpression("CONTAINS", typeof(bool), new[] { objectExpression, patternExpression }); return sqlExpression; } } 

It remains only to connect it using CustomSqlMethodCallTranslator:

 public class CustomSqlMethodCallTranslator : SqlServerCompositeMethodCallTranslator { public CustomSqlMethodCallTranslator(RelationalCompositeMethodCallTranslatorDependencies dependencies) : base(dependencies) { // ReSharper disable once VirtualMemberCallInConstructor AddTranslators(new [] {new FreeTextTranslator() }); } } 

DI to EFC

EFC uses the DI pattern to the fullest, I would even say too much. Feels the influence of the Kestrel team (or vice versa). If you are already working with ASP.NET Core, then you will not have problems understanding the implementation and resolution of curls in the EFC. The extension method UseSqlServer installs a couple of dozen dependencies necessary for the library to work. Sources can be viewed here . There is our ICompositeMethodCallTranslator , which we will overwrite using the ReplaceService helper ReplaceService

 optionsBuilder.ReplaceService<ICompositeMethodCallTranslator, CustomSqlMethodCallTranslator>(); 

Install and run.

 var textContains = dbContext.Posts.Where(x => x.Content.ContainsText("egg")).ToArray(); 

SQL generation issues

After launch, we discover 2 news: good and not so good. The good thing is that our custom translator was successfully picked up by the EFC. Bad - the request was wrong.

 SELECT [x].[Id], [x].[AuthorId], [x].[BlogId], [x].[Content], [x].[Created], [x].[Rating], [x].[Title] FROM [Posts] AS [x] WHERE CONTAINS([x].[Content], N'egg') = 1 

Obviously, the resulting SQL generator, which transforms the intermediate expression tree into a ready-made query, expects some value from the SQL function. But CONTAINS is a predicate that returns bool, which the SQL generator does not pay attention to. After googling, many unsuccessful attempts to create a crutch, I gave up. I even tried to use SqlFragmentExpression , which inserts the SQL string into the final query as is. Generator imported added = 1 . Before I go to sleep, I left a bug report on project # 11316 github . And, about a miracle, they pointed out to me the problem and the demand for its solution within 24 hours.

Problem and solution

My guess was that the SQL generator wanted the return value to be correct. To solve this problem, it was necessary in SqlVisitor'e to change VisitBinary to VisitUnary, since CONTAINS is a unary operator. Here is a realized idea. We act by analogy, create our custom generator, connect it in a container and run it again.

 public class FreeTextSqlGenerator : DefaultQuerySqlGenerator { internal FreeTextSqlGenerator(QuerySqlGeneratorDependencies dependencies, SelectExpression selectExpression) : base(dependencies, selectExpression) { } protected override Expression VisitBinary(BinaryExpression binaryExpression) { if (binaryExpression.Left is SqlFunctionExpression sqlFunctionExpression && sqlFunctionExpression.FunctionName == "CONTAINS") { Visit(binaryExpression.Left); return binaryExpression; } return base.VisitBinary(binaryExpression); } } 

Everything worked, generated the correct SQL. The ContainsText method can participate in various expressions, in general, is a full participant in the EFC.

findings

Architecturally, the EFC has gone far ahead of the classic EF. Expanding it is not a problem, but be prepared to look for solutions in the source code. For me, this is one of the main ways to learn something new, even though it takes a lot of time.

Maintainers of the project are ready to give a detailed answer to your question. I noticed that 4 days after I fixed my bug, ~ 20 issues were still open. Most of them were answered.

The ready code is here . To run it, you will need the latest VS and docker on linux containers, or SQL Server with Full-Text Search. Unfortunately, localdb comes without linguistic services and it is not possible to connect them. I used the docker-file from the Internet. Building and running the docker image is located in the database-create.ps1 file.

Also do not forget to start the migration using update-database cmdlet.