如何从 Linq 2 SQL 迁移到 Linq 2 实体? [英] How to move from Linq 2 SQL to Linq 2 Entities?
问题描述
我想为想要从 linq2sql 迁移到 linq2entities 和 ADO.net 实体框架(此处称为 L2E)的人们提供参考.我不想讨论这两个哪个更好.我只想为想要从一个过渡到另一个的人创建一个列表，列出这两者之间的差异.
I'd like to start a reference for people who want to move from linq2sql to linq2entities and the ADO.net Entity Framework (in here called L2E). I don't want to discuss which of these two is better. I just want to create a list of differences between these two for people who want to transition from one to the other.
基本的东西很简单:删除 linq2sql 数据类，添加 ado.net 模型(从数据库创建).将实体"重命名为之前数据上下文的名称.
The basic stuff is easy: remove the linq2sql data classes, add ado.net model (created from database). Rename 'Entities' to the name of the former datacontext.
现在，差异.例如，为了在 L2S 中持久化(保存)更改，我会使用:
using (MyDataClassesDataContext mydc = new MyDataClassesDataContext())
{
// change data
mydc.SubmitChanges();
}
在 L2E 中，这必须更改为:
In L2E this would have to be changed to:
using (MyDataClassesDataContext mydc = new MyDataClassesDataContext())
{
// change data
mydc.SaveChanges();
}
第二个示例，要在 L2S 中插入新记录，您可以使用:
using (MyDataClassesDataContext mydc = new MyDataClassesDataContext())
{
MyTable myRow = new MyTable();
mydc.MyTable.InsertOnSubmit(myRow);
mydc.SubmitChanges();
}
在 L2E 中，这必须更改为:
In L2E this would have to be changed to:
using (MyDataClassesDataContext mydc = new MyDataClassesDataContext())
{
MyTable myRow = new MyTable(); // or = MyTable.CreateMyTable(...);
mydc.AddToMyTable(myRow);
mydc.SaveChanges();
}
对于其他代码片段，我将跳过 using (...) 部分和 SubmitChanges/SaveChanges，因为它每次都是相同的.
要将更改的对象附加到 L2S 中的数据上下文/模型(使用时间戳):
mydc.MyTable.Attach(myRow);
在 L2E 中:
// you can use either
mydc.Attach(myRow);
// or (have not tested this)
mydc.AttachTo("MyTable", myRow);
要将更改的对象附加到 L2S 中的数据上下文/模型(使用原始对象):
mydc.MyTable.Attach(myRow, myOriginalRow);
在 L2E 中(MSDN - 应用对分离对象所做的更改):
mydc.Attach(myOriginalRow);
mydc.ApplyPropertyChanges(myOriginalRow.EntityKey.EntitySetName, myRow);
要删除 L2S 中的记录:
mydc.MyTable.DeleteOnSubmit(myRow);
在 L2E 中:
mydc.DeleteObject(myRow);
要显示已创建的用于 L2S 调试的 SQL 命令:
mydc.Log = Console.Out;
// before mydc.SubmitChanges();
在 L2E 中，您可以显示查询的 SQL(感谢 TFD):
In L2E you can show the SQL for a query (thanks to TFD):
using System.Data.Objects;
...
var sqlQuery = query as ObjectQuery;
var sqlTrace = sqlQuery.ToTraceString();
遗憾的是，我发现无法输出为调用 SaveChanges() 生成的 SQL - 您需要使用 用于此目的的 SQL 分析器.
Sadly, I found no way to output the SQL generated for a call to SaveChanges() - you'd need to use a SQL profiler for this.
为了如果不存在 L2S，则从该方案创建一个数据库:
if (!mydc.DatabaseExists())
mydc.CreateDatabase();
在 L2E 中:
// according to TFD there are no DDL commands in L2E
要在 L2S 中对数据库执行 SQL 命令:
mydc.ExecuteCommand("ALTER TABLE dbo.MyTable ADD CONSTRAINT DF_MyTable_ID DEFAULT (newid()) FOR MyTableID");
在 L2E 中:
要对 EF 中的数据库执行 eSQL 命令(请注意，eSQL 尚不支持 DDL 或 DML(更改、插入、更新、删除)命令):
To execute an eSQL command against the database in EF (beware, eSQL does not support DDL or DML (alter, Insert, update, delete) commands yet):
using System.Data.EntityClient;
...
EntityConnection conn = this.Connection as EntityConnection;
using (EntityCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = @"Select t.MyValue From MyEntities.MyTable As t";
var result = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
result.Read();
var myValue = result.GetValue(0);
...
conn.Close();
}
命令文本在 Entity SQL 中，与 T-SQL 不是 100% 相同.
(感谢 TFD)
The command text is in Entity SQL which is not 100% the same as T-SQL.
(thanks to TFD)
如果您需要在同一连接上使用 DDL/DML 命令，您可能需要自己创建数据库连接，使用您自制的 db 连接连接 EF，并将此连接用于您的 DML 命令.不漂亮，自己看看:
If you need DDL/DML commands on the same connection, you might need to create the database connection yourself, connect the EF using your selfmade db connection, and use this connection for your DML commands. Not pretty, have a look for yourself:
MetadataWorkspace workspace = new MetadataWorkspace(new string[] { "res://*/" }, new Assembly[] { Assembly.GetExecutingAssembly() });
using (SqlConnection sqlConnection = new SqlConnection("Data Source=salsa;Initial Catalog=SamAlyza;Integrated Security=True"))
using (EntityConnection econ = new EntityConnection(workspace, sqlConnection))
using (AlyzaDataClassesDataContext adc = new AlyzaDataClassesDataContext(econ))
{
// now you can use the SqlConnection like always
}
提供新创建的 L2S-Class 的默认值 覆盖部分方法 OnCreated:
To provide default values for a newly created L2S-Class override the partial method OnCreated:
partial void OnCreated()
{
Name = "";
}
在 L2E 中，你可以为你的表类创建一个默认的构造函数:
In L2E you can just create a default constructor for your table class:
partial class MyTable
{
public MyTable()
{
Name = "";
}
}
以下示例是关于两个表之间的 1:n 关系.我在这里用 SQL 定义了表，所以你知道我在写什么:
The following examples are about a 1:n relation between two tables. I define the table here in SQL, so you know what I'm writing about:
CREATE TABLE dbo.[MyTable]
(
[MyTableID] uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT [PK_MyTable] PRIMARY KEY,
[Name] nvarchar(100) NOT NULL,
) ON [PRIMARY]
ALTER TABLE dbo.[MyTable] ADD CONSTRAINT [DF_MyTable_ID] DEFAULT (newid()) FOR [MyTableID]
CREATE TABLE dbo.[MySubTable]
(
[MySubTableID] uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT [PK_MySubTable] PRIMARY KEY,
[MyTableID] uniqueidentifier NULL,
[Subname] decimal(18,2) NOT NULL,
) ON [PRIMARY]
ALTER TABLE dbo.[MySubTable] ADD CONSTRAINT [DF_MySubTable_ID] DEFAULT (newid()) FOR [MySubTableID]
ALTER TABLE dbo.[MySubTable] ADD CONSTRAINT [FK_MySubTable_MyTable] FOREIGN KEY
(
[MyTableID]
) REFERENCES dbo.[MyTable]
(
[MyTableID]
) ON DELETE CASCADE
将一条记录插入到 MyTable 中，并在 L2S 中插入相应的 MySubTable:
MyTable myRow = new MyTable();
myRow.MySubTable.Add(new MySubTable());
mydc.MyTable.InsertOnSubmit(myRow);
在 L2E 中非常相似:
Very similar in L2E:
MyTable myRow = new MyTable();
myRow.MySubTable.Add(new MySubTable());
mydc.AddToSaLyWebsites(test);
在 L2S 中在子表中搜索</strong>，您可以使用:
In L2S to search in a subtable, you could use:
from u in adc.MySubTable
where u.MyTableID == _searchForTableID && u.Name == _searchForName
select u
在 L2E 中，您无法访问关系列:
In L2E you can't access the relation columns:
from u in adc.MySubTable
where u.MyTable.MyTableID == _searchForTableID && u.Name == _searchForName
select u
(当然你也可以用)
from u in _searchForTable.MySubTable
where u.Name == _searchForName
select u
(奇怪的旁注:_searchForTable 不需要附加到 EF 才能工作.)
(strange side note: _searchForTable does not need to be attached to the EF for this to work.)
其他注意事项:
在 L2S 中，我可以在 LINQ 中使用其他功能.如果我在 L2E 中使用自定义函数，我会得到一个不支持异常.所以，而不是
In L2S I can use miscellanous functions in LINQ. If I use custom functions in L2E I get a NotSupportedException. So, instead of
from t in mydc.MyTable
where t.Date >= _searchForDate && t.Date <= _searchForDate.AddHours(2)
select t;
在 L2E 中需要使用
In L2E one would need to use
DateTime endDate = _searchForDate.AddHours(2);
from t in mydc.MyTable
where t.Date >= _searchForDate && t.Date <= endDate
select t;
虽然 L2S 可以从数据库中读取自动生成的值，例如自动生成的 ID，但在 L2E 中，这似乎只能使用 sql 类型标识.
While L2S can read autogenerated values from the database, like, for example the autogenerated ID, in L2E this seems to only work using sql type identity.
(当我偶然发现它们时，或者有人在答案中添加它们时，我会在这篇文章中收集更多差异)
(I'll collect more differences in this post as I stumble upon them, or as someone adds them in answers)
一些链接，也许有用:
- Transact-SQL 和 Entity-SQL 的区别
- NET - ADO.NET Entity Framework &LINQ 到实体
- Mike Taulty 关于断开连接的 LINQ to Entities(测试版 2L2E)
推荐答案
在 EF 中显示创建的用于调试的 SQL 命令
To show the created SQL commands for debugging in EF
using System.Data.Objects;
...
var sqlQuery = query as ObjectQuery<T>;
var sqlTrace = sqlQuery.ToTraceString();
AFAIK 没有创建数据库或执行任何类型的 DDL 工作的命令.这是Entity SQL"语言的设计限制
AFAIK there are no commands to create DB's or do any sort of DDL work. This is design limitation of the "Entity SQL" language
EDMX 设计图面将映射您当前的数据库架构，而不是相反
The EDMX design surface will map your current database schema, not the other way around
这篇关于如何从 Linq 2 SQL 迁移到 Linq 2 实体?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
