I faced with an issue, where I can't reach the SQL script to apply the migration. Here is my migration code:
public partial class AddSomethingMigration : Migration
{
private const string MIGRATION_SQL_SCRIPT_FILE_NAME = @"Migrations\Scripts\20170710123314_AddSomethingMigration.sql";
protected override void Up(MigrationBuilder migrationBuilder)
{
string sql = Path.Combine(Directory.GetParent(Directory.GetCurrentDirectory()).FullName, MIGRATION_SQL_SCRIPT_FILE_NAME));
migrationBuilder.Sql(File.ReadAllText(sql));
}
}
So when I use the Package Manager Console on the local machine all works fine. But when I deploy to the environment I get the discrepancy to the file.
Can I run my static SQL scripts via EF migration automatically at all, or I should paste the SQL query inline in code?
I found the several answers for this question.
Add scripts as project resources and use it like:
string sql = Resources._20170630085940_AddMigration;
migrationBuilder.Sql(sql);
This option not so good, because the .sql will embed in the assembly.
If you use Net Core projects with .csproj structure, you can add the itemgroup to xml:
<ItemGroup> <Content Include="Migrations\**\*.sql" CopyToPublishDirectory="PreserveNewest" /><!-- CopyToPublishDirectory = { Always, PreserveNewest, Never } --></ItemGroup>
And then specify the path to file like:
Path.Combine(AppContext.BaseDirectory, relativePath)
What I like to do is to embed the SQL script as a resource in the assembly so that the assembly does not depend on any external file. I have tested this approach with Visual Studio Community 2019 16.4.2.
In my case the DbContext
is hold in .NET Standard 2.0 library and my web application is running .NET Core 2.2.
First you need to create a migration file:
Add a new migration:
Add-Migration RunSqlScript
Add a Sql Script in the migration folder (I name it with the same prefix as the migration file for convenience)
In the File properties window make sure the Build Action is "Embedded Resource" Note that we don't need to copy to the output folder as the sql script will be embedded in the assembly.
Update the Up method in the RunSqlScript
migration
var assembly = Assembly.GetExecutingAssembly();
string resourceName = typeof(RunSqlScript).Namespace + ".20191220105024_RunSqlScript.sql";
using (Stream stream = assembly.GetManifestResourceStream(resourceName))
{
using (StreamReader reader = new StreamReader(stream))
{
string sqlResult = reader.ReadToEnd();
migrationBuilder.Sql(sqlResult);
}
}
In my app I have re-factorize this code into a utility method. I have posted without this re-factorization for the sake of brevity.
UPDATE:
The re-factorized code I mentioned above:
public static class MigrationUtility
{
/// <summary>
/// Read a SQL script that is embedded into a resource.
/// </summary>
/// <param name="migrationType">The migration type the SQL file script is attached to.</param>
/// <param name="sqlFileName">The embeded SQL file name.</param>
/// <returns>The content of the SQL file.</returns>
public static string ReadSql(Type migrationType, string sqlFileName)
{
var assembly = migrationType.Assembly;
string resourceName = $"{migrationType.Namespace}.{sqlFileName}";
using (Stream stream = assembly.GetManifestResourceStream(resourceName))
{
if (stream == null)
{
throw new FileNotFoundException("Unable to find the SQL file from an embedded resource", resourceName);
}
using (var reader = new StreamReader(stream))
{
string content = reader.ReadToEnd();
return content;
}
}
}
}
Usage example:
string sql = MigrationUtility.ReadSql(typeof(RunSqlScript), "20191220105024_RunSqlScript.sql");
migrationBuilder.Sql(sql);