EF 6迁移：如何执行sql SELECT？ [英] EF 6 Migration: How to execute sql SELECT?
问题描述
但是，将相关数据添加到DB中有一个很大的麻烦：
例如：
假设我们有2个表：
用户
：- Id（PK自动增量）
- 名称
- RoleId
角色：
- Id（PK自动增量）
- 名称
假设我们需要添加User（Name ='John'，RoleId =（Id的角色名称为Admin））。
我们该怎么做？如果我们找到一个解决方案，那么我们可以执行纯SQL SELECT脚本，因为它们可以被修改或删除，因此它们不会首先使用实体。
可以使用 ，插入，更新 Sql （...）方法，但是 > SELECT ？
您不能在迁移中有上下文。 b
$ b
逻辑上首先运行迁移以更新数据库模式，然后可以通过上下文来处理数据。如果你的数据库与模型不符，或者表格还没有，那么你不能在EF中使用它。
我不得不研究EF代码也因为好奇）。实际上，以下几个级别的DbMigration类中的Sql（）方法仅将SQL字符串添加到应该执行到事务中并继续执行的查询列表中。它在调用时不执行。所以简而言之，EF只是填写最后一次执行的代码行列表。如果您尝试在迁移代码中使用C＃代码的所有路径中行走，似乎是正确的。
这个问题实际上很不错，不幸的是我没有找到任何更好的解决方案，而不是使用纯ADO。
另一个选项是生成更多的自定义SQL查询，并更广泛地使用T-SQL。
对于您想要插入用户并按名称设置groupId的情况，可以使用内部选择：
INSERT INTO Users（Name，GroupId）
VALUES（'John'，RoleId =（SELECT Id FROM Roles WHERE Name ='Admin'））。
对于我的问题，我不得不做更复杂的执行 - 以下与使用IF语句的DbSet的AddOrUpdate方法：
IF EXISTS（SELECT * FROM Table1 WHERE Column1 ='SomeValue'）
更新表1 SET（...）WHERE Column1 ='SomeValue'
ELSE
INSERT INTO Table1 VALUES（...）
我在这里找到： http://blogs.msdn.com/b/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx
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?
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
这篇关于EF 6迁移：如何执行sql SELECT？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
