How to move from Linq 2 SQL to Linq 2 Entities?

Question

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.

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.

---

Now, the differences. For example, to persist (save) changes in L2S I'd use:

using (MyDataClassesDataContext mydc = new MyDataClassesDataContext())
{
  // change data
  mydc.SubmitChanges();
}

In L2E this would have to be changed to:

using (MyDataClassesDataContext mydc = new MyDataClassesDataContext())
{
  // change data
  mydc.SaveChanges();
}

---

2nd example, to insert a new record in L2S you'd use:

using (MyDataClassesDataContext mydc = new MyDataClassesDataContext())
{
  MyTable myRow = new MyTable();
  mydc.MyTable.InsertOnSubmit(myRow);
  mydc.SubmitChanges();
}

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

---

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

In L2E:

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

---

To attach a changed object to a datacontext/model in L2S (using original object):

mydc.MyTable.Attach(myRow, myOriginalRow);

In L2E (MSDN - Apply Changes Made to a Detached Object):

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

---

To delete a record in L2S:

mydc.MyTable.DeleteOnSubmit(myRow);

In L2E:

mydc.DeleteObject(myRow);

---

To show the created SQL commands for debugging in L2S:

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

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

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.

---

To Create a database from the scheme if none exists L2S:

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

In L2E:

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

---

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

In L2E:

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

The command text is in Entity SQL which is not 100% the same as T-SQL.
(thanks to TFD)

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
}

---

To provide default values for a newly created L2S-Class override the partial method OnCreated:

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

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

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

---

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

---

Inserting a Record into MyTable with corresponding MySubTable in L2S:

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

Very similar in L2E:

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

---

In L2S to search in a subtable, you could use:

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

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

(strange side note: _searchForTable does not need to be attached to the EF for this to work.)

---

Miscellanous notes:

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;

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;

---

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)

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)

Answer 1

In L2S you can just use stored procedures like function calls. In EF the SP has to return an entity. This can cause problems if your SP only returns a subset of a full entity

Answer 2

To get the new identity value from an insert in EF

Create Table dbo.MyItem (
    Id int indentity(1, 1) Primary Key,
    Value varchar(100)
)

var newItem = new MyItem() { Value = "Hello" };
context.AddToMyItem(newItem);
context.SaveChanges(true);
var theNewIdentityValue = newItem.Id;

The EF folks just made this to easy, nice work :-)

Answer 3

To execute a SQL command against the database in EF

using System.Data.EntityClient;
...
EntityConnection conn = new EntityConnection(myContext.Connection.ConnectionString);
conn.Open();
EntityCommand cmd = conn.CreateCommand();
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();

The command text is in Entity SQL which is not 100% the same as T-SQL

Answer 4

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 there are no commands to create DB's or do any sort of DDL work. This is design limitation of the "Entity SQL" language

The EDMX design surface will map your current database schema, not the other way around