LINQ to SQL or Entities, at this point?

Question

I'm a bit late to the game and have decided to spend some spare time learning LINQ. As an exercise, I'm going to rewrite a WebForms app in MVC 2 (which is also new to me). I managed to find a few topics regarding LINQ here (http://stackoverflow.com/questions/16322/learning-about-linq, http://stackoverflow.com/questions/8050/beginners-guide-to-linq, http://stackoverflow.com/questions/252683/is-linq-to-sql-doa), which brought the concern of Entities vs SQL to my attention.

The threads are all over a year old however, and I can't seem to find any definitive information on which ORM is preferable. Is Entities more or less LINQ to SQL 2.0 at this point? Is it still more difficult to use?

Is there any reason to use LINQ to SQL, or should I just jump into Entities? The applications I write at my present employer have a lengthy lifecycle (~10 years), so I'm trying to pick the best technology available.

Answer 1

In a recent post on extending NerdDinner, Scott Hanselman talked about this very thing. To quote at the end:

Conclusion

There's lots of choices for Database Access on .NET. You'll run into DataReaders in older or highly tuned code, but there's no reason it can't be hidden in a Repository and still be pleasant to use. LINQ to SQL is nice, lightweight and fast and has dozens of bug fixes in .NET 4, but Entity Framework is the way they are heading going forward. Plus, Entity Framework 4 is way better than EF 3.5, so I'm using it for any "larger than small" projects I'm doing and I'm not having much trouble.

Granted I haven't had a chance to play around with it much yet, but EF4 does seem to have gained the confidence of a number of pros.

Answer 2

I'd suggest going with Entities or NHibernate, Linq very tightly couples the database to your domain objects, which are then used by your presentation layer. This will end up with a tight coupling from the presentation layer to the database, which tends to not be advisable.

Personally I like the Entity Framework 4.0, you can use linq queries with it and POCO objects that you code yourself. It just handles all the database/SQL stuff for you.

Answer 3

This article is similar to Hanselman's article. It compares DataReader (which they call "connected" data access), typed DataSet ("disconnected"), LINQ to SQL and Entity Framework. Detailed code examples are given for all of the approaches, along with the pros and cons of each approach.

Answer 4

Is there any reason to use LINQ to SQL, or should I just jump into Entities?

I'm hardly objective (as a die-hard LinqToSql user), but this is answerable.

Object Relational Mapping technologies are attempts to solve or mitigate Object-relational impedance mismatch. They are bridges between the two worlds - the object world and the relational data world.

And it seems that the developers of these bridges generally call one side or the other, home.

LinqToSql is an ORM from the object side. It was developed by the C# team and lets you use objects to represent data. In LinqToSql there are no compiler-opaque strings, all queries are checked by the compiler against the mapping.

EF is an ORM from the data side. It was developed by the ADO team and lets you use data represented as objects. In EF, there are plenty of opaque strings.

Queries have to ultimately run against a database, and the compiler cannot certify that the database available at run-time matches the mapping (true in either ORM). Because of this reality of the data world, the data team doesn't value compiler guarantees as much as the C# team.

Having worked in a TSQL backend for years, without the protection of a compiler, I happen to value highly any help the compiler is going to give me. And so I side with the C# team on this.

---

For example, loading a Customer with its Orders.

  //linq to sql.
DataLoadOptions load = new DataLoadOptions();
load.LoadWith<Customer>(c => c.Orders);  //<-- strong typed
myDataContext.LoadOptions = load;

IQueryable<Customer> query = myDataContext.Customers
  .Where(c => c.CustomerId == 1);

  //entity framework
IQueryable<Customer> query = myObjectContext.Customers
  .Include("Orders")  // <-- opaque string
  .Where(c => c.Customer.Id == 1);

Answer 5

I looked at both technologies very closely in early 2009, and I listened to a lot of the rumblings about the "impending death" of LINQ-to-SQL. In the end, I still chose LINQ-to-SQL over Entity Framework, because I never enjoyed working with ADO.NET and EF reminded me too much of it, because it was so tightly bound to the database.

In my development projects, my history has always been:

1. build the schema
2. build the database objects that work with the schema
3. build the data layer that talks to the database objects
4. build the business layer that talks to the data layer

With EF, little would change. With LINQ-to-SQL, I was able to eliminate step #2 entirely so that the data layer would talk directly to the database without having to worry about dynamic SQL statements or potential injection vulnerabilities. In addition, I also gained the benefit of a much simpler change management process during the development cycle. I really don't care what Microsoft's "official" position is, because I've been hearing about the "impending death" of WinForms for about five years now, and it's still around.

Microsoft is a business, first and foremost. If they want to stay in business, they will give the customer what they desire or else the customer will find someone else more willing to service their needs. If only for the legacy applications that are still in active use today, WinForms is going to be supported for as long as the Windows operating system still supports Windows 95 applications. In the same vein, LINQ-to-SQL will continue to be supported in one form or another. Until it can be merged seamlessly into EF where existing applications will run as originally designed (or with minimal developer modification), I believe LINQ-to-SQL is also here to stay for the foreseeable future.