Entity Framework Core in Action, Second Edition by Jon P Smith;

Author:Jon P Smith; [Smith, Jon P]
Language: eng
Format: epub
Publisher: Simon & Schuster
Published: 2021-06-15T22:00:00+00:00

A more complex but more versatile approach is to extend the MigrationBuilder class to add your own commands. This approach allows you to access methods to build commands that work for many database providers.

In this section, I discuss only the first approach. The second is an advanced version that is well described in the EF Core documentation at http://mng.bz/xGBe.

As an example, you are going to create an extension method that will allow you to create SQL Views more easily. The extension method takes in the class that will be mapped to the View so that it can find the properties to map to the columns (assuming that you are using only properties and By Convention column naming). The following listing shows the extension method that will create a view within a migration.

Listing 9.6 Extension method to add/alter an SQL view in an EF Core migration

public static class AddViewExtensions ❶ { public static void AddViewViaSql<TView>( ❷ this MigrationBuilder migrationBuilder, ❸ string viewName, ❹ string tableName, ❹ string whereSql) ❺ where TView : class ❻ { if (!migrationBuilder.IsSqlServer()) ❼ throw new NotImplementedException(“warning...”) ❼ var selectNamesString = string.Join(", ", ❽ typeof(TView).GetProperties() ❽ .Select(x => x.Name)); ❽ var viewSql = $"CREATE OR ALTER VIEW {viewName} AS " + ❾ $"SELECT {selectNamesString} FROM {tableName} " + ❾ $"WHERE {whereSql}"; ❾ migrationBuilder.Sql(viewSql); ❿ } }

❶ An extension method must be in a static class.

❷ The method needs the class that is mapped to the view so that it can get its properties.

❸ The MigrationBuilder provides access to the migration methods—in this case, the Sql method.

❹ The method needs the name to use for the view and the name of the table it is selecting from.

❺ Views have a Where clause that filters the results returned.

❻ Ensures that the TView type is a class

❼ This method throws an exception if the database isn’t Server because it uses an SQL Server view format.

❽ Gets the names of the properties in the class mapped to the view and uses them as column names

❾ Creates the SQL command to create/update a view

❿ Uses MigrationBuilder’s method to apply the created SQL to the database

You would use this technique in a migration by adding it to the Up method (and a DROP VIEW command in the Down method to remove it). Here is a code snippet that creates a view for the MyView class, which has the properties MyString and MyDateTime:

migrationBuilder.AddViewViaSql<MyView>( "EntityFilterView", "Entities", "MyDateTime >= '2020-1-1'");

The resulting SQL looks like this snippet:

CREATE OR ALTER VIEW EntityFilterView AS SELECT MyString, MyDateTime FROM Entities WHERE MyDateTime >= '2020-1-1'

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.