Linq to SQL, Linq to Entites or Ado.net for web 2.0 project

[bilgehan]

Hi,

My question is if you want to develop a web2.0 project (social network) would you use Linq to SQL, Linq to Entites or ADO.NET directly? Assuming that only choice is Microsoft stack at the moment.

My main concerns are performance and ability to switch to MySql or any other open source database if needed.

As an embedded C developer I am a bit stuck about how to start basically..

Thanks.

[d000hg]

I'm fairly sure Linq will work against any sensible DB, certainly not MS-only.

MS have stopped supporting one (at least) of the Linq versions IIRC so you might want to check up on that, probably some .NET bod like DimPrawn or Gentile will chip in.

I thought Linq was wonderful when I used it in a small way.

[mudskipper]

If changing the backend database is a real possibility, you want to separate the data access layer as much as possible from the application.

I'd go with Entity Framework - there will be a lot less to change if/when you do switch dbs.

[Gentile]

Hi,

My question is if you want to develop a web2.0 project (social network) would you use Linq to SQL, Linq to Entites or ADO.NET directly? Assuming that only choice is Microsoft stack at the moment.

My main concerns are performance and ability to switch to MySql or any other open source database if needed.

As an embedded C developer I am a bit stuck about how to start basically..

Thanks.

I'll preface the advice below by saying that there's nothing you can do to ensure your project will be able to switch to an alternative DBMS at a later stage. You can only ever hope to make it less painful to do so. I worked on a Public Sector project years ago that involved an ASP.Net application integrating with an Oracle 10g database. The Technical Director on that project was fond of saying that the ASP.Net app and the separate Oracle components were each interchangeable. He no longer works there, and they're still using ASP.Net and Oracle now, some seven years later.

The reality is that tight coupling does happen in any solution involving components from different vendors coming together. Whichever UI (User Interface) and DBMS (Database Management System) technologies you use, and whichever ORM (Object Relational Mapping) tool you happen to use to facilitate communication between them. Suggesting otherwise is like saying the Channel Tunnel need not necessarily connect France with England. Just because you could theoretically use the same techniques as were deployed in the construction of the Chunnel to connect two other countries with similar physical separations for approximately equivalent effort as the initial build doesn't make that first statement true.

That said, let's go through the three approaches you've mentioned, and identify the strengths and weaknesses of each.

ADO.Net is the oldest of the three. It's a reliable, but outdated technology. Whichever way you look at it, using that technology is going to involve you writing SQL Statements by hand, and embedding those statements in code. Even if each of the DBMS that you start with and later change to are both SQL-2011 compliant, with the best will in the world there are going to be enough differences between them to make it difficult to switch DBMS vendors at a later stage in a pain-free way. I therefore wouldn't go that way.

Linq to SQL is a technology that's specific to SQL Server. It therefore wouldn't help you in the slightest for anything other than a straight .Net / SQL Server solution.

Linq to Entities allows you to communicate with a range of data types. However, how you map to those data types is still going to be a matter to be addressed separately. So, whilst it is probably the right technology to use if you want to implement business logic in a way that is as DBMS-vendor independent as possible, using it still won't guarantee that a solution you produce for one DBMS will work efficiently or as intended in another DBMS that you switch to at a later stage. Still, if you can limit which DBMS vendors you intend to facilitate switching to at a later stage, Linq to Entities is probably the technology that will involve the least tweaking to facilitate such a chance. The trade-off is that it's not quite as versatile or easy to code for as Linq-to-SQL, because you're limited to using more general features of Linq that are guaranteed to work in a range of DBMS.

If you're starting out mapping to SQL Server (which it sounds like you are since you said you're working within the Microsoft stack), you can use the Entity Framework to map tables/entities from your database schema, and use Linq to Entities to manipulate those entities. If you do that, later on if you switch to a compatible alternative DMBS, all you *should* need to do is re-map to the DBMS concerned using Entities with the same Names, Properties and Property Types, and all of your business logic should still work. I'd make sure you write plenty of unit tests as you go, though. And don't merely use mocking frameworks such as Moq or RhinoMocks to confirm that your business logic is sound, but instead actually point at a test database to confirm that what you expect to have happened to the entities and underlying data structures your code manipulates remains consistent after you change DBMS.

There are some alternative solutions out there that allow you to map tables in various DBMS schemas to .Net entities under the Entity Framework. e.g., this vendor here supports a number of alternative DBMS such as Postgres and MySQL, and so does this vendor. Those tools aren't free, and they're not without hassle to use I'm sure. But that's the price you pay for wanting to design a project that is adaptable enough to actually be able to use an undefined alternative DBMS at some point in the future. Remember that Channel Tunnel analogy I mentioned earlier: there's a reason they decided to make it connect only England and France, rather than trying to make it a general-purpose tunnel to connect undefined countries to be decided at some point in the future! I'm sure it'd have been possible to solve the latter engineering problem if they'd wanted to. However, when you explain the cost and time implications of solving a problem that is easy to define but difficult to solve, most end users end up concluding that they didn't really want a solution that is more complex and costly than their present needs demand.

[d000hg]

Linq to SQL is a technology that's specific to SQL Server

I stand corrected. Now I'm confused what LINQ technology it was I used in the past since it definitely wasn't against MSSQL!

[garethevans1986]

Neither....I would use NHibernate.

[eek]

Neither....I would use NHibernate.

I'd use whichever system allows for easy updating of queries and source tables.

To be honest no-sql is the winner unless you really need transactions. I've saved days of working using linq to Mongodb on my personal stuff.

[Gentile]

Neither....I would use NHibernate.

And how do you think that would help in moving to an alternative DBMS at a later stage if you needed to?

I've used NHibernate. I'd say it's less easy to use than the Entity Framework, generates less efficient SQL when mapping to SQL Server, and would be even more difficult to adapt to an alternative DBMS at a future point than a solution built using ADO.Net would be.

A lot of people that have only ever used NHibernate seem to think it's a Golden Hammer that can/should be used to solve every type of ORM problem. The fact is, though, that whilst it may have been the best of a bad bunch of alternatives 5/6 years ago, the lack of tooling support for it and lack of integration with Visual Studio makes it an extremely unattractive option now.

[d000hg]

Gentile when you talk about inability to [easily] switch DBMS are you talking about literal "it won't work", or are you talking about performance issues? If we're considering an application which isn't DB-intensive - just a regular app that uses a DB and doesn't have to worry about hundreds of simultaneous users - does that change your answers at all? Because surely [N]Hibernate et al are specifically designed to abstract you from the raw SQL and should at least work against different RDBMS?

[farout117]

And how do you think that would help in moving to an alternative DBMS at a later stage if you needed to?

I've used NHibernate. I'd say it's less easy to use than the Entity Framework, generates less efficient SQL when mapping to SQL Server, and would be even more difficult to adapt to an alternative DBMS at a future point than a solution built using ADO.Net would be.

A lot of people that have only ever used NHibernate seem to think it's a Golden Hammer that can/should be used to solve every type of ORM problem. The fact is, though, that whilst it may have been the best of a bad bunch of alternatives 5/6 years ago, the lack of tooling support for it and lack of integration with Visual Studio makes it an extremely unattractive option now.

I'm with Gentile on this one. I have used EF for the past 3 years now, and there is improvements in every edition that comes out. I am using it with MVC, as an ORM, and I have never had any problems whatsoever. I have used NHibernate also in the past, and I feel more comfortable using EF now, and do not even consider moving back to NHibernate.

Linq2SQL is dead, I would only use it for a very simple app that is using a simple db. For more hardcore stuff, especially MS related, then EF (and LINQ2Entities) for me is a winner anytime.