StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to move from Linq 2 SQL to Linq 2 Entities?
text
Body
copied!<p>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). <strong>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.</strong></p> <p>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.</p> <p><hr/>Now, the differences. For example, <strong>to persist (save) changes in L2S</strong> I'd use:</p> <pre><code>using (MyDataClassesDataContext mydc = new MyDataClassesDataContext()) { // change data mydc.SubmitChanges(); } </code></pre> <p>In L2E this would have to be changed to:</p> <pre><code>using (MyDataClassesDataContext mydc = new MyDataClassesDataContext()) { // change data mydc.SaveChanges(); } </code></pre> <p><hr/>2nd example, <strong>to insert a new record in L2S</strong> you'd use:</p> <pre><code>using (MyDataClassesDataContext mydc = new MyDataClassesDataContext()) { MyTable myRow = new MyTable(); mydc.MyTable.InsertOnSubmit(myRow); mydc.SubmitChanges(); } </code></pre> <p>In L2E this would have to be changed to:</p> <pre><code>using (MyDataClassesDataContext mydc = new MyDataClassesDataContext()) { MyTable myRow = new MyTable(); // or = MyTable.CreateMyTable(...); mydc.AddToMyTable(myRow); mydc.SaveChanges(); } </code></pre> <p><hr/>For the other code snippets I'll skip the using (...) part and the SubmitChanges/SaveChanges, since it is the same every time.<br> <strong>To attach a changed object to a datacontext/model in L2S</strong> (using timestamp):</p> <pre><code>mydc.MyTable.Attach(myRow); </code></pre> <p>In L2E:</p> <pre><code>// you can use either mydc.Attach(myRow); // or (have not tested this) mydc.AttachTo("MyTable", myRow); </code></pre> <p><hr/>To <strong>attach a changed object to a datacontext/model in L2S (using original object)</strong>:</p> <pre><code>mydc.MyTable.Attach(myRow, myOriginalRow); </code></pre> <p>In L2E (<a href="http://msdn.microsoft.com/en-us/library/bb896248.aspx" rel="nofollow noreferrer">MSDN - Apply Changes Made to a Detached Object</a>):</p> <pre><code>mydc.Attach(myOriginalRow); mydc.ApplyPropertyChanges(myOriginalRow.EntityKey.EntitySetName, myRow); </code></pre> <p><hr/>To <strong>delete a record in L2S</strong>:</p> <pre><code>mydc.MyTable.DeleteOnSubmit(myRow); </code></pre> <p>In L2E:</p> <pre><code>mydc.DeleteObject(myRow); </code></pre> <p><hr/>To <strong>show the created SQL commands for debugging in L2S</strong>:</p> <pre><code>mydc.Log = Console.Out; // before mydc.SubmitChanges(); </code></pre> <p>In <a href="https://stackoverflow.com/questions/137712/sql-tracing-linq-to-entities">L2E you can show the SQL for a query</a> (thanks to TFD):</p> <pre><code>using System.Data.Objects; ... var sqlQuery = query as ObjectQuery; var sqlTrace = sqlQuery.ToTraceString(); </code></pre> <p>Sadly, I found no way to output the SQL generated for a call to SaveChanges() - you'd need to use <a href="http://sqlprofiler.googlepages.com/" rel="nofollow noreferrer">a SQL profiler</a> for this.</p> <p><hr/>To <strong>Create a database from the scheme if none exists L2S</strong>:</p> <pre><code>if (!mydc.DatabaseExists()) mydc.CreateDatabase(); </code></pre> <p>In L2E:</p> <pre><code>// according to TFD there are no DDL commands in L2E </code></pre> <p><hr/>To <strong>execute an SQL command against the database in L2S</strong>:</p> <pre><code>mydc.ExecuteCommand("ALTER TABLE dbo.MyTable ADD CONSTRAINT DF_MyTable_ID DEFAULT (newid()) FOR MyTableID"); </code></pre> <p>In L2E:</p> <p>To execute an eSQL command against the database in EF (beware, eSQL does not support DDL or DML (alter, Insert, update, delete) commands yet):</p> <pre><code>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(); } </code></pre> <p>The command text is in Entity SQL which is not 100% the same as T-SQL.<br> (thanks to TFD)</p> <p>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:</p> <pre><code>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 } </code></pre> <p><hr/>To provide <a href="https://stackoverflow.com/questions/82409/is-there-a-way-to-override-the-empty-constructor-in-a-class-generated-by-linqtosq"><strong>default values for a newly created L2S-Class</strong></a> override the partial method OnCreated:</p> <pre><code>partial void OnCreated() { Name = ""; } </code></pre> <p>In L2E you can just create a default constructor for your table class:</p> <pre><code>partial class MyTable { public MyTable() { Name = ""; } } </code></pre> <p><hr/>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:</p> <pre><code>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 </code></pre> <p><hr/>Inserting <strong>a Record into MyTable with corresponding MySubTable in L2S</strong>:</p> <pre><code> MyTable myRow = new MyTable(); myRow.MySubTable.Add(new MySubTable()); mydc.MyTable.InsertOnSubmit(myRow); </code></pre> <p>Very similar in L2E:</p> <pre><code> MyTable myRow = new MyTable(); myRow.MySubTable.Add(new MySubTable()); mydc.AddToSaLyWebsites(test); </code></pre> <p><hr/>In L2S <strong>to search in a subtable</strong>, you could use:</p> <pre><code>from u in adc.MySubTable where u.MyTableID == _searchForTableID && u.Name == _searchForName select u </code></pre> <p>In L2E you can't access the relation columns:</p> <pre><code>from u in adc.MySubTable where u.MyTable.MyTableID == _searchForTableID && u.Name == _searchForName select u </code></pre> <p>(of course you could also use)</p> <pre><code>from u in _searchForTable.MySubTable where u.Name == _searchForName select u </code></pre> <p>(strange side note: _searchForTable does not need to be attached to the EF for this to work.)</p> <p><hr/><strong>Miscellanous notes:</strong> </p> <p>In L2S I can use miscellanous functions in LINQ. If I use custom functions in L2E I get a NotSupportedException. So, instead of</p> <pre><code>from t in mydc.MyTable where t.Date >= _searchForDate && t.Date <= _searchForDate.AddHours(2) select t; </code></pre> <p>In L2E one would need to use</p> <pre><code>DateTime endDate = _searchForDate.AddHours(2); from t in mydc.MyTable where t.Date >= _searchForDate && t.Date <= endDate select t; </code></pre> <p><hr/>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.</p> <p>(I'll collect more differences in this post as I stumble upon them, or as someone adds them in answers)</p> <p>Some links, maybe helpful:<br> - <a href="http://msdn.microsoft.com/en-us/library/bb738573.aspx" rel="nofollow noreferrer">Difference between Transact-SQL and Entity-SQL</a><br> - <a href="http://www.scip.be/index.php?Page=ArticlesNET16#Dump" rel="nofollow noreferrer">NET - ADO.NET Entity Framework & LINQ to Entities</a><br> - <a href="http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/09/19/9825.aspx" rel="nofollow noreferrer">Mike Taulty about Disconnected LINQ to Entities (for beta 2 of L2E)</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload