How do I use the SqlResource method in EF Migrations?

Question

MSDN says this method "Adds an operation to execute a SQL resource file". Its signature is:

protected internal void SqlResource(
    string sqlResource,
    Assembly resourceAssembly = null,
    bool suppressTransaction = false,
    object anonymousArguments = null
)

And the sqlResource parameter is described as The manifest resource name of the SQL resource file to be executed. Is a "SQL resource file" the same as a normal .resx resource file, and if so, it can contain many files, so how do I specify the name of the resource file, and the file within that resource, in this one parameter? Or is a "SQL resource file" a different type of file, that only contains one SQL script, and I just pass the name of that file for the sqlResource parameter?

Answer 1

Since EF is an open source framework, the easiest way to find this out is look at source code:

protected internal void SqlResource(string sqlResource, Assembly resourceAssembly = null, bool suppressTransaction = false, object anonymousArguments = null)
{
    Check.NotEmpty(sqlResource, "sqlResource");

    resourceAssembly = resourceAssembly ?? Assembly.GetCallingAssembly();

    if (!resourceAssembly.GetManifestResourceNames().Contains(sqlResource))
    {
            throw new ArgumentException(Strings.UnableToLoadEmbeddedResource(resourceAssembly.FullName, sqlResource));
    }

    using (var textStream = new StreamReader(resourceAssembly.GetManifestResourceStream(sqlResource)))
    {
        AddOperation(
            new SqlOperation(textStream.ReadToEnd(), anonymousArguments)
                {
                    SuppressTransaction = suppressTransaction
                });
    }
}

You can see here that this method first reads manifest resource with the name sqlResource from assembly resourceAssembly (if null - it uses calling assembly). Then text from this resource is treated as sql and regular SqlOperation is added with it and parameters you provide.

There are many ways to embed arbitrary file as mantifest resource into your assembly. Easy way for such a particular situation is to just add this file to the project with build action "Embedded Resource". So you have sql file named something like "CreateTables.sql". Right click on your Visual Studio project, click "Add > Existing Item", choose your "CreateTables.sql" file, then right click on it in Visual Studio project and choose Build Action as "Embedded Resource". This will result in manifest resource with name DefaultNamespaceOfYourAssembly.Folder.SubFolder.CreateTables.sql (if you put it in the root, without folders, then obviously skip Folder.SubFolder part), which you then can use as sqlResource in the method above.

As for resx file you mentioned - that file itself is manifest resource, but it cannot be used here, because you cannot use just a part of it (so some resource with the given key) - you can only use it as a whole, but it's xml file and not sql.