Best option for dynamic queries?

Question

I am working on porting an old application from WebForms to MVC, and part of this process tears the existing data layer by moving logic from stored procedures to code. Since I initially only worked with the basic C # SQL functions (System.Data.SqlClient), I went with the easy pseudo-ORM ( PetaPoco ), which simply takes the SQL statement as a string and executes it. Creating dynamic queries will work the same way in SQL - a lot of conditional expressions that add and remove additional code (the average query has ~ 30 filters).

So, looking around a bit, I found several options:

• A bunch of strings and conventions that add request bits as needed. Really nasty, especially when the queries get complicated, not what I want to pursue if there is a better solution.
• A bunch of conventions using L2E . It looks more elegant, but I experienced L2E, too hyped overall, was a terrible experience. Can I do the same in L2S? If so, is L2S going to stick for the next 5-10 years?
• Use PredicateBuilder . Still considering this, the same questions regarding L2S.
• EDIT: I can also just stick to the existing model of stored procedures, but I still have to rewrite them, so it can't hurt other options, because I still have to do the footwork.

Are there any other options? Can someone weigh with some experience on any of the mentioned methods - basically, the method you choose makes you build a time machine and kill you for its implementation?

Answer 1

I would look at LLBLGen. The code he creates is pretty good and customizable. They also provide a reliable linq provider that can help with your requests. I used it for several large projects and was very pleased.

http://www.llblgen.com/

Answer 2

Not quite an answer, but too long for a comment:

I created a mid-sized web application using the "concatenate pieces of SQL" method, and I'm currently doing a similar job, but using L2E.

I found that with some self-control, the concatenate-pices-of-sql method is not so bad. Of course, use parameterized queries, do not try to directly insert user input into SQL.

I slowly upgraded the L2E method. This gives you type safety, although you need to do something “backward” from how you can do this with SQL — for example, the WHERE X IN (...) constructs. But so far I have not hit anything that L2E cannot handle.

It seems to me that the L2E approach will be a little easier to support if other people are actively involved.

Do you have actual use cases when the "bloat" L2E problem is a problem? Or is it just a general feeling of malaise when you feel that the wireframe is doing too much behind the scenes?

I definitely had this feeling at the beginning (well, anyway) and, of course, I don’t like reading the generated SQL (for example, compared to my handwritten SQL from the previous project), but so far I have found L2E pretty good considering only the database hit, when it is really necessary.

Another problem is that you are using the database and how relevant are its L2E bindings. If you are using SQL Server, then no problem. However, MySql may be more flaky. A piece of L2E slickness is a great integration with VStudio and the ability of VStudio to automatically create entity models from your database. Not sure how good support is for backends without MS.

Answer 3

In my opinion, neither L2S nor L2E can generate efficient SQL code, especially when it comes to complex queries. Even in some relatively simple cases, generating queries using one of two methods would lead to inefficient SQL code, here is an example: Why does this additional connection increase the number of queries?

However, if you use SQL Server L2S, this is the best option, since L2E is designed to handle any database; Because of what, L2E will generate inefficient SQL code. Also keep in mind that neither L2S nor L2E will use tempDB, i.e. Create temporary tables or table variables or CTEs.

I would overwrite the stored procedures, optimize them as much as possible, and use L2S / L2E for simple queries that will generate one round (this should be as small as possible) on the server, and also make sure that the execution plan used by SQL Server is the most efficient ( i.e. uses indexes, etc.).

Hasanain