Property defining expressions for query: mapping model properties to non-column expressions

[the-daniel-rothig]

Certain search strategies (full text search, spatial search, ...) leverage underlying database tech for better indexing and retrieval.

Scenario: I have "latitude" and "longitude" columns in my schema. I would like to map a property "Distance" to a column in the result set that evaluates the distance between those coordinates and user coordinates, i.e. SQL like:

SELECT 
    [latitude], 
    [longitude], 
    calculate_distance(latitude, longitude, @user_latitude, @user_longitude) AS [Distance]
FROM coordinates;

The easiest way to do this is to add a Distance to the model as an always NULL column, and then use context.Coordinates.FromSql("...", sqlParametes) when querying. The downside is that this creates an extraneous column in the schema. (I was hoping I could use NotMappedAttribute but that obviously also suppresses value mapping when querying).

As a workaround, I have to do the following steps:

1. Manually drop the columns in a migration script
2. Set Property("Distance").Metadata.BeforeSaveBehaviour and Property("Distance").Metadata.BeforeSaveBehaviour to PropertySaveBehaviour.Ignore
3. Ensure that all queries return a result set with a calculated [Distance] column, to satisfy EF's assumption that there is one.

A better approach would be to have something like [DynamicallyMappedAttribute] - similar to [NotMappedAttribute], it suppresses column creation and value storage, but has a different value retrieval behaviour - i.e. when querying I would like for EF to tolerate it these properties are not present as a column in the result set. Map them when they are there and if not, just set them to NULL.

[Tarig0]

would this fit your need?

https://docs.microsoft.com/en-us/ef/core/modeling/relational/computed-columns

[ajcvickers]

@the-daniel-rothig We talked about this in triage but the result was that we're not sure what is you are trying to achieve. Can you explain what your goal is in general terms, rather than in relation to specific EF APIs?

[the-daniel-rothig]

@Tarig0 Unfortunately not - computed columns are evaluated on INSERT/UPDATE, and stored in a physical column - see #6752. I want for my evaluation to happen at SELECT-time, without the need for a column in a table.

@ajcvickers Sure :) - let me give a toy example. Let's say I create a simple to-do list app with the following entity type:

   public class ToDo
   {
       public int Id { get; set; }
       public string Text { get; set; }
       public DateTime DueDate { get; set; }
       public bool? IsOverdue { get; set; }
   }

The IsOverdue bool should be true if DueDate is in the past. Let's pretend we can only evaluate that server-side! So to list my todos I would do something like:

_context.ToDos
    .FromSql("SELECT Id, Text, DueDate, DueDate < @now AS IsOverdue FROM [ToDo]"
        , new SqlParameter("@now", new DateTime());

This doesn't require a IsOverdue column to be in the table, in fact having one would be completely useless as the @now parameter is new for every retrieval. My goal is to prevent EF from creating a column in the table, but still map the IsOverdue property to the result set (if it's there).

Real-life use cases of that would be things like spatial indexes or full-text-search, or querying within a JSON column.

Does that make more sense?

[ajcvickers]

@the-daniel-rothig The usual way to do this kind of thing is with a projection. For example:

var results = context.ToDos.Select(
    e => new
    {
        e.Id,
        e.Text,
        e.DueDate,
        IsOverdue = e.DueDate > DateTime.Now
    }).ToList();

Which results in the following on SQL Server:

SELECT [e].[Id], [e].[Text], [e].[DueDate], CASE
    WHEN [e].[DueDate] > GETDATE()
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END AS [IsOverdue]
FROM [ToDos] AS [e]

You can use a nominal type instead of an anonymous type if needed. However, if that nominal type is in your model, then it may need to be ignored in the model. For example:

public class ToDo
{
    public int Id { get; set; }
    public string Text { get; set; }
    public DateTime DueDate { get; set; }
    [NotMapped]
    public bool? IsOverdue { get; set; }
}

var results = context.ToDos.Select(
    e => new ToDo
    {
        Id = e.Id,
        Text = e.Text,
        DueDate = e.DueDate,
        IsOverdue = e.DueDate > DateTime.Now
    }).ToList();

If you need to do this with FromSQL, then it may require a query type, which will be released as part of 2.1. Issue here: #1862. Marking it as NotMapped may also work:

var results = context.ToDos
    .FromSql(@"SELECT Id, Text, DueDate, CASE WHEN DueDate > {0} THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsOverdue FROM [ToDos]", DateTime.Now).ToList();

All that being said, it's not clear why you are creating the parameter in your example above, so we may still be missing the point of what you're trying to do.

Also, this only works if you don't want to compose over the IsOverdue property in other queries. Therefore, we are leaving this issue open to track "property expressions for query" which would allow the expression used in the projection above to be included in the model, which will then allow it to be queried more naturally and also composed upon.

[ajcvickers]

(Note for implementer: doors to manual and cross-check with #10862)

[the-daniel-rothig]

Hi @ajcvickers, your last example seems to be exactly what I'm looking for! (The in-code projection doesn't work for me because I would want to map my model properties to arbitrary non-linqable expressions, e.g. scalar-valued function results)

So to clarify, that last example doesn't currently work - since right now adding [NotMapped] means that the model property remains null and doesn't get assigned with the expression in the result set - but this will change with #1862?

[Tarig0]

Could you use dapper to construct the entity then attach the entity to the dbcontext?

[the-daniel-rothig]

Hi @Tarig0, yes but I'd say it's prefreable to have the results as an IQueryable which is apparently not planned for dapper. This allows filtering, ordering, limiting etc. etc with linq.

[Tarig0]

LINQ is an extension on IEnumerable not IQueryable so should still be able to all that if you mean it won't translate to SQL, it looked like you were OK with the fromsql as an option.

[ajcvickers]

@the-daniel-rothig Scalar functions can be called from within LINQ queries--see https://docs.microsoft.com/en-us/ef/core/what-is-new/

Yes, #1862 allows any shape to be queried with FromSql.

[AndriySvyryd]

Perhaps this could be implemented by allowing value generators to be translated.

Related to #12611 and #6999

[roji]

Note regarding the original request in #10768 (comment):

computed columns are evaluated on INSERT/UPDATE, and stored in a physical column - see #6752. I want for my evaluation to happen at SELECT-time, without the need for a column in a table.

Computed columns come in two types:

1. Persisted or stored, which are evaluated on INSERT/UPDATE and stored in a physical column
2. Virtual, which are evaluated on SELECT and aren't stored

EF Core 5.0 added the ability to specify which computed column type is desired (#6682). So if the goal is for the computation to occur at SELECT-time without taking up any space, virtual columns already address that.

Allowing mapping a property to a query could still allow doing the same thing without needing to define a virtual column, but since there's very little downside to doing the latter this doesn't seem very useful. Note that computed columns are restricted to referencing only columns in the row and using "deterministic' functions, so if this issue provided a way around those limitations, that might have some value.

[aradalvand]

Libraries like EntityFrameworkCore.Projectables may eliminate the need for a built-in solution for this kind of thing.