如何将来自LINQ的2 SQL到LINQ 2实体？如何将、实体、LINQ、SQL

2023-09-02 02:05:55 作者：昨日的浪漫

我想开始对谁想要移动从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模型（从数据库中创建的）。重命名实体，前者的datacontext的名称

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

对于其他code段，我会跳过使用（...）部分和的SubmitChanges / SaveChanges的，因为它每一次都是一样的。照片要附加一个改变的对象一个DataContext /模型L2S （使用时间戳）：

For the other code snippets I'll skip the using (...) part and the SubmitChanges/SaveChanges, since it is the same every time. To attach a changed object to a datacontext/model in L2S (using timestamp):

mydc.MyTable.Attach(myRow);

在L2E：

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

要更改的对象附加到一个DataContext /模型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命令：

To show the created SQL commands for debugging in L2S:

mydc.Log = Console.Out;
// before mydc.SubmitChanges();

在 L2E可以显示的SQL查询（感谢TFD）

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

可悲的是，我发现没有办法输出调用的SaveChanges（）生成的SQL - 你需要使用 SQL事件探查器这一点。

到创建从该计划的数据库，如果不存在L2S

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

在L2E：

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

要针对L2S 数据库中执行SQL命令：

To execute an SQL command against the database in L2S:

mydc.ExecuteCommand("ALTER TABLE dbo.MyTable ADD CONSTRAINT DF_MyTable_ID DEFAULT (newid()) FOR MyTableID");

在L2E：

要执行ESQL命令针对EF数据库（注意，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();
}

命令文本是在实体SQL这是不是100％一样的T-SQL。（感谢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命令。没有pretty的，看看自己：

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
}

提供default对于价值新创建L2S级覆盖的部分方法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：

Inserting a Record into MyTable with corresponding MySubTable in L2S:

  MyTable myRow = new MyTable();
  myRow.MySubTable.Add(new MySubTable());
  mydc.MyTable.InsertOnSubmit(myRow);

在L2E非常相似：

  MyTable myRow = new MyTable();
  myRow.MySubTable.Add(new MySubTable());
  mydc.AddToSaLyWebsites(test);

在L2S 要搜索一个子表，你可以使用：

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

（当然你也可以使用）

(of course you could also use)

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使用自定义函数，我收到了 NotSupportedException异常。而不是这样，

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人会需要使用

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和实体SQL 之间差异 - 网 - ADO.NET实体框架和放大器; LINQ到实体 - 迈克Taulty约断开LINQ到实体（用于测试版2 L2E）

Some links, maybe helpful: - Difference between Transact-SQL and Entity-SQL - NET - ADO.NET Entity Framework & LINQ to Entities - Mike Taulty about Disconnected LINQ to Entities (for beta 2 of L2E)

如何将来自LINQ的2 SQL到LINQ 2实体？如何将、实体、LINQ、SQL

推荐答案