Sql – Entity Framework & LINQ To SQL – Conflict of interest

entity-frameworkentity-sqllinqlinq-to-entitieslinq-to-sql

I've been reading on the blogosphere for the past week that Linq to SQL is dead [and long live EF and Linq to Entities]. But when I read the overview on MSDN, it appeared to me Linq to Entities generates eSQL just the way Linq to SQL generates SQL queries.

Now, since the underlying implementation (and since SQL Server is not yet an ODBMS) is still a Relational store, at some point the Entity framework has to make the translation into SQL queries. Why not fix the Linq to SQL issues (m:m relationships, only SQL server support etc.) and use Linq to SQL in as the layer that generates these queries?

Is this because of performance or EF uses a different way of transforming the eSQL statement into SQL?

It seemed to me – at least for my unlearned mind – a natural fit to dogfood Linq to SQL in EF.

Comments?

Best Answer

It is worth noting that Entity Framework has (at least) three ways of being consumed:

LINQ to Entities over Object Services over Entity Client
Entity SQL over Object Services over Entity Client
Entity SQL using Entity Client command objects (most similar to classic ADO.NET)

Entity Client ultimately spits out a representation of the ESQL command (in a canonical, database agnostic form) which the ADO.NET Provider for the specific RDBMS is responsible for converting into store specific SQL. This is the right model IMHO as over the years a lot of time has been invested (and will continue to be invested) in producing great ADO.NET Providers for each store.

As Entity Framework needs to work with many stores and therefore many ADO.NET Providers there is less scope for them to easily optimise what the Entity Client generates on a per store basis (at least - thats where we are with v1). The LINQ to SQL team had a much smaller problem to solve - "works only with SQL Server" and hence could do store specific stuff more easily. I know the EF team are aware that there are cases where EF to SQL Server is producing TSQL less efficiently than L2S and are working on improving this for V2.

Interestingly this model allows new capabilities to be added between the Entity Client and the ADO.NET Provider for a store. These "wrapping providers" can add services such as logging, auditing, security, caching. This is discussed as a V2 feature over at http://blogs.msdn.com/efdesign/archive/2008/07/09/transparent-caching-support-in-the-entity-framework.aspx

If you look therefor at the bigger picture you can see that it would be horribly difficult and indeed restrictive to try and somehow retrofit L2S TSQL generation into the archiecture of the Entity Framework.

Related Solutions

.net – Entity Framework vs LINQ to SQL

LINQ to SQL only supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's a great API to use for quick data access construction to relatively well designed SQL Server databases. LINQ2SQL was first released with C# 3.0 and .Net Framework 3.5.

LINQ to Entities (ADO.Net Entity Framework) is an ORM (Object Relational Mapper) API which allows for a broad definition of object domain models and their relationships to many different ADO.Net data providers. As such, you can mix and match a number of different database vendors, application servers or protocols to design an aggregated mash-up of objects which are constructed from a variety of tables, sources, services, etc. ADO.Net Framework was released with the .Net Framework 3.5 SP1.

This is a good introductory article on MSDN: Introducing LINQ to Relational Data

C# – LINQ query on a DataTable

You can't query against the DataTable's Rows collection, since DataRowCollection doesn't implement IEnumerable<T>. You need to use the AsEnumerable() extension for DataTable. Like so:

var results = from myRow in myDataTable.AsEnumerable()
where myRow.Field<int>("RowNo") == 1
select myRow;

And as @Keith says, you'll need to add a reference to System.Data.DataSetExtensions

AsEnumerable() returns IEnumerable<DataRow>. If you need to convert IEnumerable<DataRow> to a DataTable, use the CopyToDataTable() extension.

Below is query with Lambda Expression,

var result = myDataTable
    .AsEnumerable()
    .Where(myRow => myRow.Field<int>("RowNo") == 1);

Best Answer

Related Solutions

.net – Entity Framework vs LINQ to SQL

C# – LINQ query on a DataTable

Related Topic