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: #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:

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}");

[ajcvickers]

Support materialized views here.

[CallumBateson]

There doesn't seem to be much movement on this ticket. While workarounds can be found to handle views as a part of migrations, does anyone have any workarounds for how they should be created for integration testing that is setup using the EnsureCreated() function?

Considering just manually running the view creation SQL during test setup. But it feels messy.

[james-poulose]

I am currently evaluating EF core for a legacy migration project and the current 'workarounds' seem a bit messy. Eagerly waiting for this.

[roji]

@CallumBateson @james-poulose there's nothing particularly messy about just creating/managing your view by inserting SQL directly into migrations, as specified in the docs; that's the recommended way to do things at the moment.

Allowing inserting the SQL in the model - which is what this issue tracks - wouldn't provide a huge advantage over that.

[Atulin]

Sure there's nothing wrong with managing raw SQL in the migration files... until something goes horribly wrong with the db or migrations and the best solution is deleting all migrations and creating them from scratch.

I'm always wary of manually editing generated files, and migrations are generated.

[roji]

I'm always wary of manually editing generated files, and migrations are generated.

You generally shouldn't be; there's a potentially endless number of DDL options and things which are supported across all databases, and EF can't possibly model all of them via nice Fluent APIs etc. Inserting SQL in migrations is something that's occasionally necessary.

[CallumBateson]

@CallumBateson @james-poulose there's nothing particularly messy about just creating/managing your view by inserting SQL directly into migrations, as specified in the docs; that's the recommended way to do things at the moment.

Allowing inserting the SQL in the model - which is what this issue tracks - wouldn't provide a huge advantage over that.

Using the EnsureCreated() method is currently recommended in the docs for integration tests requiring a database. This method does not involve migrations in any way so inserting SQL there is not a solution (in fact migrations cannot even be run on a database created this way without modifications).

Since EnsureCreated() only looks directly at the model, this issue to specify SQL in the model is directly related.

[roji]

@CallumBateson if you're doing true integration tests that work against your production database system, you can (and probably should) be using the same migrations to ensure that your test database schema is identical to your actual production database (otherwise the tests have low fidelity). Otherwise, if you're not using migrations and setting up some sort of custom schema, then it really seems quite trivial to add those view view creation SQLs after calling EnsureCreated - once again, the value in doing that in the model isn't huge.

[CallumBateson]

@roji Adding view creation SQLs after EnsureCreated is the approach I went down.

It works, but I think we have different definitions of trivial. In order to get Code First migrations working alongside the EnsureCreated functionality for testing, developers are expected to implement a system where all versions of a view are known and handled.

For integration testing on seeded databases, logic to fetch the latest view definitions to run on a new database after ensure created.

For migrations, logic to fetch a new version of a view for the Up() and also logic to fetch previous view versions for Down() statements.

The value of having this handled within the model would be that it would be unnecessary for developers to do the above. It’s hard to say what counts as huge value. But in my opinion it certainly isn’t negligible nor trivial.

[roji]

@CallumBateson I'm curious, if you're using migrations (presumably for your production database), why not use them for your integration tests as well?

[CallumBateson]

@roji We’re moving from an SSDT managed database which was migrated with publish actions. For our integration tests, publish created a new database for us in a container which we then seeded so we could run integration tests on. In moving to Code First, using EnsureCreated felt like the analogous approach.

I’m under the impression (perhaps mistaken) that using migrations on a newly created integration test database would involve running every Up() migration sequentially on the database to get it up to date, which seems like it would have a negative performance cost as more and more migrations are produced.

[roji]

I’m under the impression (perhaps mistaken) that using migrations on a newly created integration test database would involve running every Up() migration sequentially on the database to get it up to date, which seems like it would have a negative performance cost as more and more migrations are produced.

That's true, although unless you're recreating the database for each and every test, it should hopefully not matter much... If you're doing that in order to ensure test isolation, it's generally worth ensuring isolation via other means (e.g. running the test in a transaction that gets rolled back, or clearing the data only between tests). In any case, I'd at least check to make sure that running the migrations is an actual problem in terms of test performance.

In any case, as I wrote above, there's no way EF can cover the entirety of what you can do with a database - people routinely need various special tweaks on table configuration, or modifying some database setting... You may have been lucky so far and not needed anything like that, but there's a good chance it might happen one day (view creation is just one specific case).

[Timovzl]

It's clear that EF can never cover all functionality of all databases. That said, I have to agree with @CallumBateson's argumentation here, and I see the value in the proposed feature.

Using a separate database per test is by far the easiest and safest form of isolation, especially when it comes to testing side effects: "this table should now contain exactly these 2 entities and nothing else". I'm not aware of any good and simple alternatives.

Running all migrations for each test quickly starts slowing things down, in my experience. I tend to run one test that runs all migrations, with the remainder of the integration tests simply using EnsureCreated(). I'm very satisfied with this approach, but imagine I would not be if views came into play.

[roji]

Using a separate database per test is by far the easiest and safest form of isolation, especially when it comes to testing side effects: "this table should now contain exactly these 2 entities and nothing else". I'm not aware of any good and simple alternatives.

I generally disagree there - while providing absolute isolation, that's a very heavy approach and there are alternatives. Read-only tests can be identified and executed in parallel with no isolation issues; tests which modify can use transactions which get rolled back (i.e. use the database's natively-supported isolation facilities!), or if that's not possible for some reason, at least share the database between tests which don't run in parallel and take care to reset the data only between tests. Yes, this does require a bit more work, but if test running time is important (which it is if you 're using EnsureCreated to avoid the load of running migrations), then it's well worth doing this.

BTW strategies for achieving test isolation are further discussed in this doc page.

[ajcvickers]

Issue #24710 is about adding hooks that run before/after migrations have run. These hooks would also apply to EnsureCreated, since it uses the migrations pipeline under the covers. This will provide a way to do something custom in the database before/after EnsureCreated is called.