EF 6 Migration: How to execute sql SELECT?

Question

In our project we have necessity of adding some predefined data to DB. I think the best way and concept is using for that EF Migrations (not Seed method).

But we have a big troubles with adding related data to DB:

For Example:

Suppose we have 2 tables:

Users:

• Id (PK auto increment)
• Name
• RoleId

Roles:

• Id (PK auto increment)
• Name

Let's suppose that we need to add User(Name = 'John', RoleId = (Id of role that name is 'Admin')).

How can we do it? It would be great if we find a solution that allows us to execute pure SQL SELECT script which not uses Entities of Code First because they can be modified or removed.

For DELETE, INSERT, UPDATE can be used Sql(...) method but what about SELECT?

Answer 1

You cannot have a context into the migration.

Logically first are ran the migrations to Update the DB Schema, then you can have a context to work with the data via it. If your DB does not match the model, or even the table is still not there, you cannot use it in EF.

I had to look into the EF code (and also because was curious). Practically the Sql() method in the DbMigration class in several levels below just adds the SQL string into a list of queries that should be executed into the transaction and moves on. It does not executes it when it is called. So in short EF just fills in a list of codes lines that should be executed in the end at once. And it seems correct if you try to walk in all paths of what you can do with the C# code in the migration code.

The question is quite good actually, unfortunately still I didn't found any better solution rather than using pure ADO.

Another option is to generate more custom SQL queries, and use T-SQL more widely. For your case as you want to insert the user and set the groupId looking by the name, it can be used with inner select:

INSERT INTO Users (Name, GroupId)
VALUES ('John', RoleId = (SELECT Id FROM Roles WHERE Name = 'Admin')).

For my issue, I had to a bit do more sophisticated execution - the following does the same as the AddOrUpdate method of the DbSet, using the IF statement:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

I found it here: http://blogs.msdn.com/b/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx

Answer 2

I'm using good old LINQ for this:

public override void Up()
{
    using (var dc = new DbContext("your connection string or name"))
    {
        var ids = dc.Database.SqlQuery<int>("SELECT id FROM sometable WHERE somefield={0}", 42).ToArray();

        ...
    }
}

Using LINQ is better, even for usual migrations, because, there is a bug in DbMigration.Sql method, it ignores arguments: How to pass parameters to DbMigration.Sql() Method