Entity Framework 6 Migrations with views

c# entity-framework entity-framework-6

Question

In order to use EF6 with migrations, I must map to a view.

In order to provide a straightforward summary view of the underlying data so we may utilise it in a summary index view, the view pivots two additional tables.

My problem is that I can't build a migration that either delivers the view (which is the desired outcome) or publishes the database without the view for a later manual deployment.

Most of the time, after answering previous SO questions, I either block theAdd-Migration and Update-Database generating an error that breaks one or both of the instructions or generally.

What is the current best practise for using EF6 to access views, even if I lose the ability to deploy them automatically with migrations and avoid migration problems.

More information

Reports and ReportAnswers are two tables in the database. These two are combined in the view ReportView, which pivots ReportAnswers to convert part of the rows into columns for this summary view.

Through EF Migrations, Reports and ReportAnswers were depolished. Currently, the view is a script that must be somehow incorporated to the deployment.

The database context provides access to Reports, ReportAnswers, and ReportView.

public virtual DbSet<ReportAnswer> ReportAnswers { get; set; }
public virtual DbSet<Report> Reports { get; set; }
public virtual DbSet<ReportView> ReportView { get; set; }

I've tried usingAdd-Migration Name -IgnoreChanges to start with a new migration, add the view by hand, and thenUp() and Down() the migration and update commands get stuck in a stalemate because they both want to execute first.

I've also experimented withmodelBuilder.Ignore<ReportView>(); to conduct the migrations without taking into account the type, however this was very error prone, although seeming to succeed at least once.

1
1
1/15/2018 2:54:49 PM

Popular Answer

I just read a fascinating post on utilizing views with EF Core, however I also came across the similar thing with EF 6.

You may prefer to utilizeSeed technique as opposed to migrationUp and Down methods.

protected override void Seed({DbContextType} context)
{
    string codeBase = Assembly.GetExecutingAssembly().CodeBase;
    UriBuilder uri = new UriBuilder(codeBase);
    string path = Uri.UnescapeDataString(uri.Path);
    var baseDir = Path.GetDirectoryName(path) + "\\Migrations\\{CreateViewSQLScriptFilename}.sql";

    context.Database.ExecuteSqlCommand(File.ReadAllText(baseDir));
}

Your SQL command has to resemble the one below.

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[{ViewName}]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[{ViewName}]
AS
SELECT {SelectCommand}

Although it is not flawless, I hope it is at least useful.

I saw another blog entry regarding this subject, and the author advises usingSql(@"CREATE VIEW dbo.{ViewName} AS...") in Up approach andSql(@"DROP VIEW dbo.{ViewName};") in Down method. Since you omitted to provide the code from, I added it.Up and Down migration strategies. It could be a good idea to add SqlFile rather than Sql method.

There is also the opportunity to write unique code or integrate a sql generator with migrations, but I assume that is not what you are looking for.

If you need any more assistance, please let me know in the comments.

Related websites

Using Entity Framework Code First and Views

Views and stored procedures for EF CODE First

Using Entity Framework's views

(String, "Boolean," "Object," DbMigration.Sql Method)

Method DbMigration.SqlFile (String, "Boolean," "Object")

2
1/15/2018 10:48:31 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow