Materialised views

[TehWardy]

I've been following the discussion on #1368 for some time and I just had a thought that might be good for some "complex" scenarios.

SQL Servers have the concept of a "materialised view" which is basically a regular view but with some added ability to map that back to the source fields, meaning you can interact with it just like a regular table.

Given that the implementation of Many-To-Many joins when completed will ultimately involved some abstraction or proxying or mapping within the model so that for example ...

"UserRoles" is a hidden join that exists in the db but not in the exposed entities instead many "Users" each have a collection of many "Roles" and the same is true the other way, (a-la EF6 way of handling the join).

Given this will require some mapping and abstraction, could we do something like this using the same API ...

Typical example ...

I have tables for Invoices and for Credits and for Payments.
They all derive from a common "Transaction" type which for now I have told EF to ignore.

It would be really cool if I could say to EF "Build me an entity set " which is defined from this "aggregation / mapping set" and when I interact with that set EF deals with the mapping back to the source tables from where the complex set got the data automatically.

In my case I could expose "Transactions" as such a set and insert Invoices or Credits or Payments in to that set and context would map that back to the correct destination table for me, with of course "full CRUD" supported as per the usual expectation on EF Set even if I have to declare it as ComplexSet or something and do some wiring up.

Under the bonnet, EF could use (for SQL server) a Materialised View, or it could do the whole lot in memory, that's an implementation detail I guess the framework would solve.

For me ... the net benefit here is that I could provide aggregations or complex reports and leverage the framework / underlying DB servers features to optimise the performance of some complex scenarios that today would involve say ... a very long OData query or a complex LINQ query in my business logic that I have to marry up sets of data prior to my aggregation.

[roji]

I don't see a connection between views (materialized or otherwise) and the many-to-many join entity... For many-to-many to work, an actual table is needed to hold the associations. While a materialized view stores data on disk, it's not a table - all it does is cache data selected from other tables (as opposed to a regular view which doesn't cache).

Note that we already have a pretty clear plan on how to support many-to-many without a CLR type for the join entity (see the plan on many-to-many), so nothing like this would be necessary anyway.

Regardless of many-to-many, allowing users to create materialized views is something we could support, and would be part of #14537 (if the user provides SQL) or #465 (if the user provides a LINQ expression). On the other hand, it's trivial for you to create them yourself by adding the SQL in your migrations - at least for the SQL version, there simply isn't a huge amount of value in having EF do it for you; in any case you're providing raw SQL for the view definition.

[TehWardy]

Ah ok what I had in mind was an extension of something like the sample here ...

#14537 (comment)

... the added benefit of being able to do full CRUD on the result.

Views like this are typical old school views rather than Materialised Views which allow us to write changes back. I'm not really suggesting "specifically that what's used here is a materialised view" more like "something in the model that behaves like one".

Some versions of SQL of curse don't support Materialised Views hense my thinking this be a model defined thing that doesn't result in anything that actually lives in the DB.

As a somewhat simplified example perhaps something like this ...

builder.ComplexSet<Transactions>().From(
    schema: "dbo", 
    name: "Transactions", 
    as:  builder.Invoices()
              .Union(builder.Credits)
              .Union(builder.Payments)
              .Select(i => (Transaction)i)
);

... using the SQL equiv would be perhaps an option too.

At this point though I accept there's likely some overlap with the features you've referenced.

This somewhat ties in to TPT in my case as I have told the model up to this point to ignore the Type transactions as it's a base type for the sets I want in my complex set.

The reasoning for this particular use is to be able to do something like ...

var financialPosition  =Db.Transactions.Where(...).Sum(t => t.Value);

Where EF computed what my transaction set was from the "set of sets" defined in my union query.
Other scenarios opened up b this being writable would also prove useful in my complex workflow scenarios that i'm happy to go in to more detail about if it's useful here.

[roji]

... the added benefit of being able to do full CRUD on the result.
Views like this are typical old school views rather than Materialised Views which allow us to write changes back. I'm not really suggesting "specifically that what's used here is a materialised view" more like "something in the model that behaves like one".

Materialized views aren't the same as updatable views - both materialized and non-materialized views can be updatable under certain conditions (by no means like a full table). All that a materialized view means, is that instead of evaluating the view's SQL definition for each query, that SQL definition gets evaluated once and cached; that means that subsequent queries are faster, but the view can get stale as backing data changes, and must be refreshed.

Regarding the rest, I do agree that when we allow users to create views via the model (#14537), we should also provide the option to make them materialized.

[TehWardy]

Oh ok ... my understanding of materialised views was obviously not quite right.
I thought were sort of like a computed table that stored all it's columns in other tables.

Interesting, thanks for the lesson :)

Did you understand my wanting to have a computed set that could be fully CRUD'ed on though?
Is it a valid concept / idea or is this something you guys would rather avoid?

[roji]

Materialized views may be able to provide part of what you're thinking about - take a look at creating these yourself via SQL. There really isn't much extra value EF Core could provide here, at the end of the data it's just creating the view by sending your SQL to the database.

[TehWardy]

Maybe i'm over thinking this and I should layer some business logic on top of TPT setups.

[ajcvickers]

Added a note to #827 for materialized view support pointing to this issue.

[pfdsilva]

@ajcvickers
Is scaffolding of Materialised views supported in Sql Server's EFCORE provider?

[ErikEJ]

@pfdsilva Yes.