From old DataLayer to LINQ to SQL / Entity Framework

Question

There are a few "should I chose this or that" questions on SO and Google, as well as a lot comparing LINQ2SQL and LINQ2E. I've seen drawbacks, differences, cons, pros, limitations, etc.

I cannot say I'm an expert but I'd like to know "what would you do" if you faced this scenario and why.

I have to add stuff to an "old" 2.0 app that has been recently migrated to 3.5 (it kind of compiled out of the box, with some warnings here and there). As I have to add new stuff I want to start using LINQ (2SQL or Entities).

I've worked with Linq2SQL in the past and I like the fact that it's a fast, easy to use solution… if you don't want to do anything too weird. The natural 1..1 relationship between tables and such is godsend if you come from the old "Recordsets". Also nice is the ability to have different datacontexts depending upon what set of tables you want to work with.

As the amount of things and new features grows, I've started considering the possibility to try the Entity Framework instead of Linq2SQL. What I don't like about the latter is the inability to handle Many2Many relationships without resorting to a hack.

That's where the Entity Framework comes in. But, you can't have multiple "small models" like you do with Linq2Sql and the "datacontexts", unless you sacrifice some stuff and some other stuff.

The database has 218 tables right now, and it doesn't dramatically grow. A new table every now and then but we're not talking about 10 tables a day. I'd say that we'll have a "hard time" reaching 250 tables during the following year of development.

There are Many 2 Many relationships, some with the PK/PK only and some with aggregates (which Entity Framework doesn't magically handle, but it can deal with them. This is the reason why I wouldn't want to start with Linq2SQL unless the hack is "ok" and an accepted method.

What would you do? Compromise N..N relationships (knowing that even when there's a hack, it will not be as elegant nor as "supported" as an integrated solution) and go with Linq2SQL and its "have many small datacontexts that you create and destroy" philosophy or, on the other hand, go on with Entity Framework, have a gigantic datamodel with all the tables or relationships and start from there? Even when creating multiple models have drawbacks (see links up there) and having one giant model has cons.

There are also unofficial comments about Microsoft "dropping" Linq2SQL (I doubt but you never know). Our database is MSSQL and I don't see that changing soon.

Any experience in the subject will be appreciated.

Answer 1

I am dealing with many-to-many relationships just fine in Linq2SQL.

See here for a neato extension method to solve your pains!

Personally, I find using one monolithic model is the easiest to deal with.

Answer 2

What would I do in your situation? I would use NHibernate and probably Castle Active Record.

Why? Because that solution offers the best support for "real world" scenarios, especially the ones you mention. NHibernate scales with your project and doesn't limit you. It's basically free. NHibernate is a very mature, well tested solution.

You don't mention NHibernate as something you are looking into, so I'm not sure what doesn't appeal to you in that regard (it seems like you've read enough that you would have come across that option).

LinqToSql/EntityFramework don't offer a whole lot that you can't also get with NHibernate.