Do not map property to column in database but fetch property value by raw SQL query

[arturwv]

Need to have property in an entity which is computed every query execution.
This property should not be mapped to the DB column

now It works when you use
modelBuilder.Entity().Ignore(a => a.SomeData); //but it disables populating Property in class when I use SQLRaw query.

db is created, then you comment abowe-
and add
modelBuilder.Entity().Property(a => a.SomeData).HasComputedColumnSql();

but it is no a solution :/

So maybe add this future -> eg. HasComputedRawSqlProperty() -> dont create dbCollumn and expect the SQLRaw query will deliver a value or not.

[roji]

Duplicate of #10768

[roji]

@arturwv see especially this comment about virtual computed columns - what's your reason for wanting to specify a raw SQL property rather than just a computed column, which is almost the same thing?

[arturwv]

It is not the same,
With Hierarchical table, I want to have column generated by ... recursive query with path (eg. 1/3/7/9)

with a computed column, I need to make a function that will update this column every time I make an insert or update (when I change the parent, add a node, etc.) worse, I have to update this column for all children of that node.
Or the column is mapped and filled with nulls, and SQLRaw returns the computed value, but I have garbage in the database.
Can be done but why don't have a simple mechanism?

[roji]

I don't follow... You seem to be asking for HasComputedRawSqlProperty, which would mean that when you load the entity, that property is loaded from raw SQL that you specify. For example, you could do HasComputedRawSqlProperty("SOME RAW SQL"), and then EF Core would generate SQL like the following when loading the entity:

SELECT [Id], [SomeOtherField], <SOME RAW SQL> AS MyProperty ...

That same SOME RAW SQL can be passed to HasComputedColumn today, and will be computed on-demand whenever you query the database computed column:

SELECT [Id], [SomeOtherField], [MyProperty] ...

There's no need to update the column - the whole point of a computed column is that this is managed for you by the database; the value simply gets calculated when you query it (for virtual columns).

[arturwv]

Maybe I don't know how..
How would you "compute" HierarchyPath collumn in "compputed collumn"?

Id	Name	ParentId	HierarchyPath
1	Loc 1	NULL	        1
3	Loc 2	1		1/3
4	Loc 3	1		1/4
5	Loc 4	1		1/5
6	Loc 5	1		1/6
7	Loc 6	3		1/3/7
8	Loc 7	7		1/3/7/8
9	Loc 8	7		1/3/7/9

[roji]

How were you planning on computing it for HasComputedRawSqlProperty?

[arturwv]

by query which gives me all the data. (with computed HierarchyPath)

  WITH CTELocationsTree
	                    AS
	                    (
	                    SELECT [Id],[Name],[ParentId], Convert(NVARCHAR(MAX),[Id]) as HierarchyPath
	                    FROM [dbo].[Locations] WHERE Id = 1 

	                    UNION ALL

	                    SELECT loc.[Id],loc.[Name],loc.[ParentId], Convert(NVARCHAR(MAX),cloc.HierarchyPath +'/'+ Convert(NVARCHAR(20),loc.Id))  as HierarchyPath
	                    FROM [dbo].[Locations] loc
	                    INNER JOIN CTELocationsTree cloc  ON loc.ParentId = cloc.Id
	                    )
	                    SELECT * FROM CTELocationsTree cte 

[arturwv]

Maybe I can make function from recursive query and use it for computed column, but ... the same recursive is being done twice.
so it is better to save these values when insert update.

and in EF core 7.0.4 it works when you have DB/table created with no column but you have a class with property which is generated by SQLRaw :)

only there is no way to ignore property when map to db, and not ignore property when querying data by SQL.

[roji]

WITH CTELocationsTree

This doesn't work; SQL does not allow putting WITH in the middle of a query as so:

SELECT [Id], (WITH ...) AS x ...

WITH must appear at the top-level before the SELECT, so it wouldn't be usable with HasComputedRawSqlProperty.

so it is better to save these values when insert update.

Please read the difference between virtual and stored computed columns in the comment which I linked to above. You can define computed columns as either computed-on-select (virtual) or computed-on-update (stored).

[arturwv]

You can define computed columns as either computed-on-select (virtual) or computed-on-update (stored).

but what about this?? In that case it does not matter whether column is "virtual or stored"

[roji]

I don't understand what you're asking...

[arturwv]

in the described case, I don't see a place for solutions using a virtual column in db

[roji]

@arturwv I'm sorry, but I don't understand your argument. If what you want is to have a .NET property which, when loaded, evaluates arbitrary raw SQL in the database, than a database virtual column provides that capability. You haven't provided a reason against this standard way of doing things.

[arturwv]

database virtual column provides that capability

In a what way ?

1. Create Scalar function (with recursive query ) -func is not deterministic, so it cant be persisted.
2. add Computed column with this function
3. performance of that construction is zero - that is reason against > standard way

[roji]

As far as I know, there is no performance difference whatsoever between calling your scalar function directly in the query (which is what you're asking for), or creating a (non-persisted) computed column and fetching that column. In both cases the database has to invoke your function for each table row that's selected - it's exactly the same.

I'm going to go ahead and close this as a duplicate of #10768 - I don't see anything new here compared to that issue. Please feel free to continue posting here, but I recommend understanding exactly why you're asking for this - performance-wise there really should be a difference, and computed columns should already address the vast majority of needs here.

If you're aware of some performance advantage, please post actual SQL or link to a resource which shows this.