EF 6 Migration: how to execute sql SELECT?

Question

In our project, we need to add some predefined data to the database. I think the best method and concept is used for this EF Migrations (not Seed) method.

But we have big problems adding related data to the DB:

Example:

Suppose we have two tables:

Users

• Identifier (PK auto increment)
• Name
• Role id

Roles

• Identifier (PK auto increment)
• Name

Suppose we need to add a user (Name = 'John', RoleId = (role identifier called 'Admin')). A.

How can we do this? It would be great if we find a solution that allows us to execute a pure SQL SELECT script that does not use First code entities because they can be changed or deleted.

You can use the Sql (...) method for DELETE, INSERT, UPDATE, but what about SELECT ?

Answer 1

You cannot have a context in the migration.

Logically, migrations are first started to update the database schema, then you may have a context for working with data through it. If your database does not match the model, or even the table still does not exist, you cannot use it in EF.

I had to look into the EF code (and also because it was curious). In practice, the Sql () method in the DbMigration class at several levels below simply adds an SQL string to the list of queries that must be executed in the transaction and moved. He does not fulfill it when he is called. In short, EF just populates a list of lines of code that should be executed at the end right away. And it seems right if you try to go through all the paths of what you can do with the C # code in the jump code.

The question is pretty good, unfortunately, until I found a better solution, and did not use pure ADO.

Another option is to generate additional custom SQL queries and use T-SQL more widely. For your case, when you want to insert a user and set groupId, looking by name, it can be used with internal selection:

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

For my problem, I had to do more complicated execution: the following does the same as the AddOrUpdate method for 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 use the 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 regular migrations, because there is an error in the DbMigration.Sql method: it ignores the arguments: How to pass parameters to the DbMigration.Sql () method