LINQ to SQL

Lets start at the beginning:

Any Software Ideas? Get in touch!

The Origin of LINQ to SQL

There is a the question as to why Microsoft has both LINQ to SQL and the ADO.NET Entity Framework technologies on the go since they appear to address the same problem domain.

To try to answer this it might be worth looking at what Mike Pizzo (Microsoft) has said about Microsoft’s Data Access Strategy, and I quote from this article below:
What is the difference between LINQ to SQL and LINQ to Entities?

LINQ to SQL supports rapid development of applications that query Microsoft SQL Server databases using objects that map directly to SQL Server schemas. LINQ to Entities supports more flexible mapping of objects to Microsoft SQL Server and other relational databases through extended ADO.NET Data Providers.

If you are writing an application that requires any of the following features, you should use the ADO.NET Entity Framework:

· The ability to define more flexible mapping to existing relational schema, for example:

o Mapping a single class to multiple tables

o Mapping to different types of inheritance

o Directly Modeling Many to Many relationships

o Mapping to an arbitrary query against the store

· The ability to query relational stores other than the Microsoft SQL Server family of products.

· The ability to share a model across Replication, Reporting Services, BI, Integration Services, etc.

· A full textual query language

· The ability to query a conceptual model without materializing results as objects

If you do not require any of these features, LINQ to SQL may provide a simpler solution for rapid development. LINQ to SQL is targeted at rapid-development scenarios against Microsoft SQL Server and provides a simpler approach and richer design experience in the Orcas timeframe for applications with data classes that map directly to the schema of your Microsoft SQL Server database.

Microsoft is defining a migration plan for customers that start with LINQ to SQL and require additional functionality, such as richer mapping capabilities or access to other stores, to migrate to the ADO.NET Entity Framework.

Mike Pizzo,
Architect, Data Programmability

This article is dated April 2007 and maybe things have changed.

Introducing LINQ to Relational Data on MSDN says  that LINQ to SQL is an object-relational mapping (ORM) framework that allows the direct 1-1 mapping of a Microsoft SQL Server database to .NET classes, and query of the resulting objects using LINQ. More specifically, LINQ to SQL has been developed to target a rapid development scenario against Microsoft SQL Server where the database closely resembles the application object model and the primary concern is increased developer productivity.

So, Linq to SQL is portrayed as a RAD tool for simple projects where the database schema is the domain model and you are using SQL Server.

I should point out here that LINQ to SQL does support Table per Hierarchy (TPH) mapping, where multiple classes in a hierarchy are mapped to a single table using a discriminator column to determine the specific type of each row/instance (this is a very common database inheritance mapping), although this is the only object oriented mapping it does currently support and it may be that it was incorporated before MS had a clear data strategy regarding LINQ to SQL and EF.

We know that the LINQ to SQL does in fact have a provider model that was deliberately not exposed thus tying it to SQL Server. This is clearly a political decision. Technically it is possible to use LINQ to SQL with databases other than SQL Server.

It would seem from all this then that LINQ to SQL will be dropped at some stage in favour of EF and that EF is the furture direction of data access from Microsoft. However LINQ to SQL is now integrated with SQL Server 2008 where it makes a good replacement for T-SQL. Clearly, you would not really want to integrate EF into SQL Server, would you now?  

I am beginning to think of LINQ to SQL as a replacement for embedded SQL in our applications, when you want to talk directly to the database, and as a replacement to T-SQL in SQL Server. Clearly it is ideal for simple RAD applications and for applications where the “database schema is the domain model”. BUT the provider model really needs to be re-enabled!

Micosoft sums it up in this table for where to use LINQ to SQL:

I want to

LINQ to SQL is applicable

Use an ORM solution and my database is 1:1 with my object model

 

Use an ORM solution with inheritance hierarchies that are stored in a single table

 

Use my own plain CLR classes instead of using generated classes or deriving from a base class or implementing an interface

 

Leverage LINQ as the way I write queries

 

Use an ORM but I want something that is very performant and where I can optimize performance through stored procedures and compiled queries

 

Clearly not everybody is happy about this situation, and who knows perhaps it is not so cut and dry after all!

I think there is a place for both and I think some of the previous statements from Microsoft in this context have been misleading.

LINQ to SQL will live on, though probably won’t develop too much beyond what it is now.

See Ian Coopers article regarding the things he would and would not like to see in LINQ to SQL development: Architecting Linq to SQL, part 10. I think he will get the stuff that doesn’t conflict with the idea that “the database schema is the domain model”!

The big effort will be put into EF by the data platform team. We will have to wait and see. In the mean time I think LINQ to SQL is worthy of a look at because it is here to stay! It is very popular and has a role to play.

Some Links:

An Extensible LINQ to SQL DataContext - this is one way to get around the fact that the provider model was disabled for LINQ to SQL.

Building an IQueryable provider