Architectural Design DAL Layer

Question

I am working on a mid-size web application architecture and for my DAL layer I have 3 options

1) Traditional citation-based, stored architecture (using the NTiers codeite template)

2) LINQ To SQL (or PLINQO Template of codemith)

3) LINQ To Entity

On top of LINQ to Entity is out of reach, since we need to launch the application very quickly, and we don’t have enough skills for the same, and since the team never worked on any OR / M tools, this will be a steep learning curve for them ( this is what i read somewhere)

I prefer to use LINQ to SQL (but the only fear is that Microsoft will not support or improve LINQ to SQL further), from my point of view, if Microsoft is not going to improve it further, I do not have any like any element that I needed in my project, enough.

Now my problem is should I use linq for sql or should I stick with the traditional architecture?

OR otherwise, any other option is ...

EDIT: I am going to use SQL Server as a database and does not require interaction with any other database

One of the most important tasks in the development of DAL Layer is the accelerated development and maintenance of future changes to the database table, since there is a possibility that in the future the field may increase or decrease.

Also, if you feel that any ORM tool is really good and does not have a steep learning curve, we can also use

Please provide suggestions

Answer 1

Since you are working in a medium-sized project, I suggest you use LINQ-TO-SQL because of these advantages.

Benefits of using LINQ to SQL:

• No magic lines, as in SQL queries • Intellisense • Compile validation when the database changes • Faster development • Unit of work (context) • Autogenerated domain objects that are useful small projects • Lazy loading. • Learning how to write linq / lambdas queries is a must have for .NET developers. Regarding performance:

• Most likely, in most solutions, performance will not be a problem. Pre-optimization is an anti-pattern. If you later notice that some areas of the application will slow down, you can analyze these parts, and in some cases even exchange some linq queries using stored procedures or ADO.NET. • In many cases, lazy loading can speed things up, or at least simplify the code. Regarding debugging:

• In my opinion, debugging Linq2Sql is much simpler than stored procedures and ADO.NET. I recommend that you take a look at the Linq2Sql Debug Visualizer, which allows you to see the query and even run execution to see the result when debugging. • You can also configure the context for writing all sql queries to the console window, more information here As for the other layer:

• Linq2Sql can be considered as another level, but it is purely a data access layer. Stored procedures are also another layer of code, and I have seen many cases where part of the business logic has been embedded in stored procedures. This is much worse, in my opinion, because you then divide the business layer into two places, and it will be more difficult for developers to get a clear idea of ​​the business domain.

Answer 2

There is no absolutely preferred way to write DAL. These are all options. Which one to choose depends on your project, your skills and your inclinations.

Normally with LINQ you can expect more productive work. On the other hand, a DAL built with stored procedures can be faster.

The problem only arises when you need some specific queries that the LINQ to SQL provider will not be able to generate by default in order to be incredibly fast. In this case, you will have to use your LINQ code to add your own stored procedures if necessary.

As for LINQ to SQL support and further development, it was founded a long time ago. So no formal further development. Note: this is true for the LINQ to SQL relational solution (it will be adopted through EF), and not for the main LINQ functions.

Entity Framework in its v.1 only received massive criticism. You are advised to wait until v2 is released.

The most important limitation with LINQ (over Entity Framework or any other popular ORM) is that it does not support 1st mapping. That is, each of your LINQ classes can display only one table, and not represent a view over several others. It may not be important to you, but it may be. Depends on your project.

Answer 3

The argument to stored procedures against ORM is long-standing and is unlikely to be resolved any time soon. My recommendation would be to go with ORM (Linq-to-Sql in your case).

Yes, stored procedures will always be faster since the requests are precompiled. The real question that you should ask yourself is whether you have such a high-performance system that your users really notice the difference. Keep in mind that using stored procedures means that you will need to manually write all your own queries, where using ORM will do it for you. This usually means that ORM will speed up your development.

Since you mention that speeding up development time is one of your goals, I would recommend Linq-to-Sql - otherwise you basically write the entire DAL yourself.

Answer 4

All the parameters you provide have significant drawbacks. None of them meet the requirements that you specified.

You need to prioritize what matters most to you.

If the learning curve is your biggest problem, stay away from all ORMs if you are comfortable working with ADO.NET, DataTables, etc.

If development speed is your biggest problem, you should learn ORM and go this route. The easiest ORM recommended for NHibernate. All other ORMs have significant disadvantages. NHibernate works in the vast majority of projects, while other ORMs are much more situationally suitable (depending on your database design, model design, flexibility requirements, support for an outdated scheme, etc.). All ORMs have learning curves, they just come into play at different times and in different ways.

Answer 5

Just to expand on @Developer Art, using the traditional subroutine approach, you can put business logic in a database. Usually you need to avoid this, but sometimes you need to do this. Not to mention that you can also apply restrictions and permissions at the database level using this approach. It all depends on your requirements.

Answer 6

With limitations to mention, I would say just stick to adhoc / custom requests and ADO.NET, not for any jazz stuff. In addition, DALs with stored procedures are faster, concept-based arguments, such as stored procedures, are precompiled, but they are not. All they have is a request plan cache. The less investment in stored procedures, the better. My advice to ADO.Net and custom dynamic queries created from entity objects.