Create database views in Migrations using SQL specified in the model

Open
#14,537 25 comments 30 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
25/100
Issue type
Feature
Clarity
Mostly clear
Activity status
Stale
Tech stack
csharp, sql
Domain
database

Research direction

Start by reading the related issue #465 and the discussion on this issue to understand the existing query-type and migration constraints. The requested outcome is for a model-defined, opaque SQL Server view definition to be discovered by migrations and recreated when it changes, without requiring users to paste SQL into migrations.

Written by the indexing model from the issue text.

Description

area-migrations area-model-building customer-reported

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

Dominant language
C#
Stars
14.8k
Forks
3.4k
Avg merge
2d 5h
Merged PRs (30d)
134

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from dotnet/efcore

All issues in dotnet/efcore

Similar issues

More C# issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.