What is the best choice LINQ TO SQL (.DBML) or using ADO.NET with procedures for a database with 29 tables and about 30 concurrent users that will run the system that I am going to build?

I know that ADO.NET is faster than LINQ TO SQL but it is so much simpler to work with LINQ TO SQL. Will LINQ TO SQL handle all the concurrency? Or will there be problems in performance?

The system I am going to build will be a WCF service using multiple layers:

  • Service layer
  • Business
  • Repositories
  • Data Access Layer

closed as not constructive by Michael K, gnat, Wyatt Barnett, Joel Etherton, Arseni Mourzenko Feb 10 '12 at 19:24

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Note: The users will Insert, Select and update the db very intensively. – Tan Feb 10 '12 at 16:04
  • I would say build Prototype. You will have to figure out if in your usage if there will be a problem or not. – Ramhound Feb 10 '12 at 16:06
  • /I know that ADO.NET is faster than LINQ TO SQL/ - what do you mean by faster in this context? Coding or execution time? – JBRWilkinson Feb 10 '12 at 16:33
  • ADO.NET isn't much faster if the LINQ you are writing doesn't end up being transformed into messy TSQL. If you are concerned about performance I suggest writing the more complex queries in a stored procedure and then interacting with them via LINQ to SQL. – The Muffin Man Feb 10 '12 at 23:03

I would recommend to use an Entity Framework ORM platform with Linq To Entities for working with data.

Since you are just starting a project (it seems), you should have no problem using the latest tools right from the start.

  • It depends on the structure of the database though. I know that EF has given me plenty of headaches with certain layouts (e.g lack of FKs) and made me wish I had just done ADO.NET – Jetti Feb 10 '12 at 16:36
  • 1
    If he is using an existing database - yes. If he is free to create it using code-first, no (almost) no headaches are most likely ahead. – Maxim V. Pavlov Feb 10 '12 at 16:39
  • +1, for not recommending Linq-To-SQL – NoChance Feb 10 '12 at 17:55

It depends on what your system is like in terms of accessing and manipulating the data. ORMs (LINQ to Entities, etc.) are great at simplifying simple data manipulations like CRUD. What they aren't especially good at are complex and flexible reporting.

So the answer depends on the emphasis of your system. If it's mostly entering data then use an ORM. If it's mostly querying and reporting then write stored procs to extract and manipulate the data and pick your favourite access layer to output the data.

Not the answer you're looking for? Browse other questions tagged or ask your own question.