Computed columns shouldn't require a column in the schema

[rschiefer]

PROBLEM: Currently, computed columns don't work if they aren't defined in the table schema. Raw SQL doesn't require a column for the computed result. So we have to artificially change our database schema for EF Core to handle a computed column correctly.

PROPOSED SOLUTION: Support computed columns without a real column on the table.

[ajcvickers]

@rschiefer It's not 100% clear to me what you mean by "computed columns" here. Could you explain in more detail, preferably showing what you are currently doing in code?

Related: #10768

[rschiefer]

In my particular use case I'm calculating distance for addresses in the database based on a passed in location. Here is a PR for another repo which is a good example.

scottschluer/geolocation@a3dc84d

[ajcvickers]

@rschiefer Can you post an example of a table and the SQL that you would like to run against that table?

[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.

[rschiefer]

A simplified example of the table would be:

TestCases Table:
x | y |

1 | 1 |
1 | 2 |
2 | 1 |
2 | 2 |

SQL:
SELECT x, y, x * y as [product]
FROM TestCases

SQL Results:

x | y | product |

1 | 1 | 1 |
1 | 2 | 2 |
2 | 1 | 2 |
2 | 2 | 4 |

[smitpatel]

@rschiefer - What you are showing is not "Computed column". Computed columns are part of table schema. See https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15

You are running query in which you are computing custom expression and generating a new column at query time. Write that in your LINQ query so Product does not become part of your table schema.

[rschiefer]

Correct. The problem is EF won't (at least not that I can figure out) return the calculated result unless you define the column as part of your entity which then requires you to set some default value that is a magic value for null (I'm currently using -1).

I want:

• Calculation performed in SQL
• Result returned by EF
• No table schema column on entity table for result column

[smitpatel]

LINQ: context.TestCases.Select(t => new { t.x, t.y, product = t.x * t.y });

[rschiefer]

I would prefer to bring back my entity class and specify how I want the "calculated" property set:

context.TestCases.Select(t => new TestCase { t.x, t.y, product = t.x * t.y });