Difference between LINQ to SQL and the Entity Framework

I was browsing through the usual newsgroups I visit and came up with this post about LINQ to SQL vs EDM and thought I should share it here.

LINQ to SQL and the Entity Framework have a lot in common, but each have features targeting different scenarios.

LINQ to SQL has features targeting "Rapid Development" against a Microsoft SQL Server database. Think of LINQ to SQL as allowing you to have a strongly-typed view of your existing database schema. LINQ to SQL supports a direct, 1:1 mapping of your existing database schema to classes; a single table can be mapped to a single inheritance hierarchy (i.e., a table can contain persons, customers, and employees) and foreign keys can be exposed as strongly-typed relationships.  You can build LINQ queries over tables/views/table valued functions and return results as strongly typed objects, and call stored procedures that return strongly typed results through strongly typed methods.  A key design principle of LINQ to SQL is that it "just work" for the common cases; so, for example, if you access a collection of orders through the Orders property of a customer, and that customer's orders have not previously been retrieved, LINQ to SQL will automatically get them for you.  LINQ to SQL relies on convention, for example default insert, update, and delete logic through generated DML can be overwritten by exposing appropriately named methods (for example, "InsertCustomer", "UpdateCustomer", "DeleteCustomer").  These methods may invoke stored procedures or perform other logic in order to process changes.

The Entity Framework has features targeting "Enterprise Scenarios".  In an enterprise, the database is typically controlled by a DBA, the schema is generally optimized for storage considerations (performance, consistency, partitioning) rather than exposing a good application model, and may change over time as usage data and usage patterns evolve.  With this in mind, the Entity Framework is designed around exposing an application-oriented data model that is loosely coupled, and may differ significantly, from your existing database schema.  For example, you can map a single class (or "entity") to multiple tables/views, or map multiple classes to the same table/view. You can map an inheritance hierarchy to a single table/view (as in LINQ to SQL) or to multiple tables/views (for example, persons, customers, and employees could each be separate tables, where customers and employees contain only the additional columns not present in persons, or repeat the columns from the persons table).  You can group properties into complex (or “composite”) types (for example, a Customer type may have an “Address” property that is an Address type with Street, City, Region, Country and Postal code properties). The Entity Framework lets you optionally represent many:many relationships directly, without representing the join table as an entity in your data model, and has a new feature called "Defining Query" that lets you expose any native query against the store as a "table" that can be mapped just as any other table (except that updates must be performed through stored procedures).  This flexible mapping, including the option to use stored procedures to process changes, is specified declaratively in order to account for the schema of the database evolving over time without having to recompile the application.

The Entity Framework includes LINQ to Entities which exposes many of the same features as LINQ to SQL over your conceptual application data model; you can build queries in LINQ (or in “Entity SQL”, a canonical version of SQL extended to support concepts like strong typing, polymorphism, relationship navigation and complex types), return results as strongly typed CLR objects, execute stored procedures or table valued functions through strongly-typed methods, and process changes by calling a single save method.

However, the Entity Framework is more than LINQ to Entities; it includes a "storage layer" that lets you use the same conceptual application model through low-level ADO.NET Data Provider interfaces using Entity SQL, and efficiently stream results as possibly hierarchical/polymorphic DataReaders, saving the overhead of materializing objects for read-only scenarios where there is no additional business logic. 

The Entity Framework works with Microsoft SQL Server and 3rd party databases through extended ADO.NET Data Providers, providing a common query language against different relational databases through either LINQ to Entities or Entity SQL.

So while there is a lot of overlap, LINQ to SQL is targeted more toward rapidly developing applications against your existing Microsoft SQL Server schema, while the Entity Framework provides object- and storage-layer access to Microsoft SQL Server and 3rd party databases through a loosely coupled, flexible mapping to existing relational schema.

 

Read More

Entity Framework and Lazy Loading

Microsoft's Entity Framework is a new, powerful tool bringing data modeling, O/RM (object relational mapping) functionality and more. One expected feature of major ORMs is 'Lazy Loading'. Learn how the Entity Framework provides this functionality in a different way. This article will explain the design reasons behind why EF is different than what you would expect, as well as how to achieve the lazy-load functionality you're looking for.

It is important to realize that that ORMs are not a new concept. There are a lot of excellent ORMs out there for Ruby, Python and even for the .Net framework (NHibernate). As a result, when you "jump in" to EF, you may run into an 'issue' similar to what I did (with lazy loading not working the way I thought it would). Because I was used to LINQ to SQL, I was expecting implicit lazy loading, and therefore I thought that EF was broken. This misconception was wrong, and I'll explain more as we go on.

Before I go into detail about the difference in design between the Entity Framework and LINQ to SQL (or other ORMs like NHibernate), I want to show you the code that threw me for a loop. The scenario is simple, I had a "Customers" table and an "Orders" table in my SQL database. I used the LINQ to SQL designer and then the Entity Framework designer (Entity Data Model designer, or EDM). Both designers easily generated my c# code for me that will interact with my database.

My SQL tables had only a few records in there. The "Customers" table had one record, and the "Orders" table had three records that were linked to the customer. So, here's the code I wrote where I initially misjudged EF:

// First, using LINQ to SQL
L2SDataContext linqToSqlContext = new L2SDataContext();

this.MyDataGrid1.DataSource = linqToSqlContext.Customers.First().Orders;

// 3 records in my data grid!!!
this.MyDataGrid1.DataBind();

The above code worked exaclty as I thought it would. The first customer object was recieved from the database, and then when I accessed that customer's orders, LINQ to SQL went back to the database and got the orders. Now, here is what I thought was the same thing using the Entity Framework. Notice how many rows were in my datagrid:

// Using the Entity Framework
EFEntities entityFrameworkContext = new EFEntities();

this.MyDataGrid2.DataSource = entityFrameworkContext.Customers.First().Orders;

// 0 records in my data grid???
this.MyDataGrid2.DataBind();

And that was it. Something so simple as that caused me to spiral into a dark room of confusion. In fact, there are people out there who have 'given up' on EF right there. The problem here is not with the Entity Framework, but with my lack of understanding the underlying design of EF.

More

Read More