Efcore: Add custom query translation for model properties and methods

[mojtabakaviani]

At now query translation in EF Core base of predicted senarios, so just specific queries will be translated successfuly. Some scenarios maybe not yet supported or not add to EF Core very soon, additionally some scenarios are very spatial that never supported in EF Core. an Idea is that allow access any parts of query translation and can customize it. for example, a query not translate in the select, join, where, grouping or others but work it if replace with corrected statements, or can add spatial features to EF Core. #23352, #23375 are issues that customize translation help to become a reality. please add APIs to EF Core that allow customize query translation.

[smitpatel]

There are already hooks to extend/customize query translation in EF Core from various different perspectives. Currently we have following examples which have successfully extended query translation pipeline.

• npgsql
• MySql
• Spatial support for Sqlite/SqlServer
• NodaTime support in npqsql
• JSON support in MySql

You will need to describe your scenario in detail what you are trying to do in LINQ query, what is the error message you are getting and how are you trying to customize query translation.

23352 has nothing to do with query customization, it is purely question from type mapping/storage perspective.

23375 is flat wrong way to do things. Anyone can customize query pipeline to suit their needs but that does not mean that it will be extended in exactly the way user wants. SqlFragmentExpression is outright wrong kind of expression to represent that translation.

So please provide details of your issue and a concrete proposal.

[smitpatel]

@mojtabakaviani - I have studied #23375 in detail and provided response there. That issue also does not have any error relating to extending EF Core query translation. The article and the issue both had multiple errors on how to use EF Core.

[mojtabakaviani]

@smitpatel EF Core extend/customize query translation very complex and not easy for customers. think about APIs for anybody. for example if can custom conversion base of #23352 and #23375 issues, I want add custom query translation for Contains method
```c#
public class Polygan
{
public bool Contains(Point point)
{
throw new NotImplementedException();
}
}

builder.Entity().Property(p=> p.Location).HasDbFunction(p=> p.GetMethod(nameof(p.Contains))).HasTranslation(args => SqlExpression.Create(p=> "[{0}].STContains(geography::Parse('POINT({p.Lat} {p.Lng})')) = 1", args));

and linq query:
```c#
var point = new Point(52.2323,26.32323)
from c in db.Cities
where c.Location.Conations(point)

and sql script

SELECT * FROM [dbo].[Cities] AS c WHERE [c].STContains(geography::Parse('POINT(52.2323 26.32323)')) = 1

[smitpatel]

The SQL script you have posted is not correct. [c] is not geography instance to call STContains on it.
Further, the method is instance method which is not supported by DbFunction API. The main reason for the restriction is about how to translate the instance to server. In most cases the instance is of a complex, unmappable type.

As a customer if you want to add custom translation, then you can add IMethodTranslator through a IMethodCallTranslatorPlugin and we will call into it. You are using string translation which is incorrect. STContains is a SqlFunctionExpression with instance. Translation would be
new SqlFunctionExpresson(instance, "STContains", args, true, true, new [] { true }, typeof(bool), null)
The type mapping for Point is supposed to print out the "Parse" string.

[mojtabakaviani]

Yes, it's just example

SELECT * FROM [dbo].[Cities] AS c WHERE [c].Location.STContains(geography::Parse('POINT(52.2323 26.32323)')) = 1

another example, translation for model properties or methods
an invoice with items delivery
```c#
public class Invoice {
public int Id { get; set; }
public int Number { get; set; }
public string Customer { get; set; }
public bool IsDelivered { get; set; }
}

public class InvoiceItem {
public int Id { get; set; }
public int InvoiceId { get; set; }
public int Qty { get; set; }
public bool IsDelivered { get; set; }
public void Delivered(){
}
}

builder.Entity().Property(p=> p.IsDelivered).HasTranslation("(SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END FROM [dbo].[InvoiceItems] AS [i2] WHERE i2.[InvoiceId] = [{0}].[Id] AND i2.[IsDelivered] = 0)")

and linq:
```c#
var invoices = db.Invoices.ToList();

script that generated:

SELECT i1.[Id], i1.[Number], i1.[Customer], (SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END FROM [dbo].[InvoiceItems] AS [i2] WHERE i2.[InvoiceId] = i1.[Id] AND i2.[IsDelivered] = 0) AS [IsDelivered]
FROM [dbo].[Invoices] AS i1

add translation for Delivered method:

```c#
builder.Entity().Method(m=> m.Delivered).HasTranslation("UPDATE [dbo].[InvoiceItems] SET [IsDelivered] = 1 WHERE [Id] = @p0",i => i.Id)

then if call Delivered method, update script with item id generated and executed:

```c#
item.Delivered();

[roji]

@mojtabakaviani your example with IsDelivered is already covered by computed columns (which aren't the same thing as method translators). Check out the docs.

To summarize:

• End users can already define custom method translations via HasDbFunction - this already seems to be a very easy API, more or less as accessible as it can be. This is currently an undocumented API, but it's very high up on our list of things to document (https://github.com/dotnet/EntityFramework.Docs/issues/500).
• Provider (or plugin) writers have the more complex IMemberTranslator/IMethodTranslator APIs. These aren't meant to be user-facing, but rather provider-facing.
• Properties on mapped entities can be configured as computed columns.

Where exactly do you see a further need here?

[mojtabakaviani]

@roji my goal is that can customize/extend query translation easy for every developer and many purposes. computed columns very limited, plugin APIs very complex. all most projects using EF Core in real world have some scenarios not supported. but such solutions, same custom query translation help to easy developing advanced and customized scenarios.

[roji]

@mojtabakaviani you're making some broad statements there, but I'm looking to understand exactly what you find problematic with the current options; can you provide concrete problems?

computed columns very limited

How so?

plugin APIs very complex

As I wrote above, the plugin APIs (IMemberTranslator/IMethodTranslator) aren't generally meant for user consumption. This is why HasDbFunction exists, what do you find insufficient with that API?

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.