Efcore: Create database views in Migrations using SQL specified in the model

[sandersaares]

I enjoy using query types with views in order to perform advanced queries that I cannot do via EF transformations.

Right now, I am forced to use arbitrary SQL statements in migrations to build my views. This is very cumbersome. I wish to define my views as part of the query types in my model, in a similar way to how tables are built from the entity types.

I want migrations to pick up the view definition from my model and recreate the view as needed on any changes to it.

This would enable two things:

1) Have an authoritative definition of the view in one well known place.
2) Separate the delivery of query types and views from the delivery of migrations.

Perhaps the second deserves more explanation. I am making a library that supplies part of the entity/query model to the users of the library (e.g. imagine a "template" product from which customer-specific instances are created). The users of my library own the migrations. If I want to use views as the backing objects for my query types, my only choice right now seems to be to nicely ask my users to add some SQL statement into a migration to actually create this view. This does not seem like a path that makes for happy developers.

I am quite willing to define the view as just an opaque (to EF) string if it saves me from having to arbirarily paste SQL into migrations.

I am interested in this for SQL Server.

Related: https://github.com/aspnet/EntityFrameworkCore/issues/465

[tuespetre]

I would love to see this. Views are priceless for most of our applications and I wish we could just define them as 'part of the model.' I also feel that functions/sprocs/types/etc. should receive the same treatment.

[smitpatel]

While defining views in modelbuilding can be flowed through migrations.

I want migrations to pick up the view definition from my model and recreate the view as needed on any changes to it.

Can you share some ideas, how would we identify if there are any changes to the view which needs us to recreate it?

[tuespetre]

@smitpatel my first thought (just for views) was something like this:

modelBuilder.Query<MyQueryType>().ToView(
    schema: "dbo", 
    name: "MyQueryView", 
    sql: @"SELECT blah, blah, blah")

It takes an 'opaque (to EF) string' as @sandersaares said. This adds the definition of the view to the model, which would go into the model snapshot. When that string has changed, it will be compared with what is in the snapshot and that comparison will determine whether the migration should create a new view or alter the existing view. For any changes to the model like renaming or dropping a column or so forth, it would be the developer's responsibility to update the SQL for the view accordingly.

Where it gets fuzzy for me is the details of when they can be altered/dropped-and-recreated during the migration to avoid errors. I'd have to brush up on how that all works.

[sandersaares]

Yeah, exactly - the above matches my thoughts.

[bricelam]

This sounds like a dupe of #465...

[smitpatel]

@bricelam - Description of #465 reads more like define views using linq query on existing dbsets. Provider agnostic views. We can merge both if needed

[ajcvickers]

@divega to look for (different) dupe.

[ajcvickers]

@divega I was unable to find another duplicate and I agree the two issues are different so clarified this in their titles and leaving both on the backlog. Feel free to follow up if you do find the other dupe.

[sandersaares]

Given that this is assigned to backlog, I suppose a pertinent question to raise would be that of alternatives. What are the best approaches to defining views in a "migration-ish style"? I feel that my approach of "just paste some SQL in there" is not the best that can be achieved and perhaps participants in this thread might be able to offer useful references to alternatives that are usable already today.

[Atulin]

@sandersaares What I came up with as a workaround until full support for views is added in EF Core 11, is perhaps something like using this extension method and the following code:

```cs
var query = this.Blogposts
.Select(b => new BlogpostWithScore{
Title = b.Title,
Body = b.Body,
AuthorName = b.Author.UserName,
Score = b.Votes.Count
})
.ToSql();

this.Database.Execute($"CREATE VIEW {nameof(BlogpostWithScore)} AS {query}");
````