如何从 Linq 2 SQL 迁移到 Linq 2 实体?

问题描述

我想为想要从 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.

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();
}

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();
}    

mydc.MyTable.Attach(myRow);

在 L2E 中:

// you can use either
mydc.Attach(myRow);
// or (have not tested this)
mydc.AttachTo("MyTable", myRow);

mydc.MyTable.Attach(myRow, myOriginalRow);

在 L2E 中(MSDN - 应用对分离对象所做的更改):

mydc.Attach(myOriginalRow);
mydc.ApplyPropertyChanges(myOriginalRow.EntityKey.EntitySetName, myRow);

mydc.MyTable.DeleteOnSubmit(myRow);

在 L2E 中:

mydc.DeleteObject(myRow);

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.

if (!mydc.DatabaseExists())
  mydc.CreateDatabase();

在 L2E 中:

// according to TFD there are no DDL commands in L2E

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
}

partial void OnCreated()
{
  Name = "";
}

在 L2E 中，你可以为你的表类创建一个默认的构造函数:

In L2E you can just create a default constructor for your table class:

partial class MyTable
{
  public MyTable()
  {
    Name = "";
  }
}

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 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);

from u in adc.MySubTable 
where u.MyTableID == _searchForTableID && u.Name == _searchForName 
select u

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

from t in mydc.MyTable 
where t.Date >= _searchForDate && t.Date <= _searchForDate.AddHours(2) 
select t;

DateTime endDate = _searchForDate.AddHours(2);
from t in mydc.MyTable 
where t.Date >= _searchForDate && t.Date <= endDate 
select t;

using System.Data.Objects;
...
var sqlQuery = query as ObjectQuery<T>;
var sqlTrace = sqlQuery.ToTraceString();