CODEWITHSUNDEEP
c#petapoco
tennesseepusher
tennesseepusher

Reputation: 41

Best option for dynamic queries?

I'm working on porting an old application to from WebForms to MVC, and part of that process is tearing out the existing data layer, moving the logic from stored procedures to code. As I have initially only worked with basic C# SQL functions (System.Data.SqlClient), I went with a lightweight pseudo-ORM (PetaPoco), which just takes a SQL statement as a string and executes it. Building dynamic queries would work about the same in SQL - lots of conditionals that add and remove additional code (average query has ~30 filters).

So after looking around a bit, I found some choices:

Are there any other options out there? Can anyone weigh in with some experience on any of the mentioned methods - mainly, did the method you choose make you want to build a time machine and kill past you for implementing it?

Upvotes: 4

Views: 844

Answers (3)

jwd
jwd

Reputation: 11114

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

I have built a mid-sized web app using the 'concatenate pieces of SQL' method, and am currently in the process of doing a similar job but using L2E.

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

I have been slowly growing an appreciation for the L2E method though. It gives you type safety, though you do have to do some things "backwards" from how you might do it with SQL -- such as WHERE X IN (...) constructs. But so far I haven't hit anything that L2E can't handle.

I feel like the L2E approach would be a little easier to maintain if other people were to be heavily involved.

Do you have actual use cases where the "bloat" of L2E is a problem? Or is it just a general sense of malaise where you feel the framework is doing too much behind the scenes?

I definitely had that feeling at first (ok, still do), and certainly don't like reading the generated SQL (esp. compared to my handwritten SQL from the previous project), but so far have found L2E pretty good with regard to only hitting the DB when it is actually necessary.

Another concern is what DB you're using, and how up-to-date its L2E bindings are. If you're using SQL Server, then no problem. MySql might be more flaky though. A chunk of L2E's slickness comes from its nice integration with VStudio, and VStudio's ability to build entity models from your DB automagically. Not sure how good the support is for non-MS DB backends.

Upvotes: 1

Hasanain
Hasanain

Reputation: 935

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 via either of the two methods would yield inefficient SQL code, here's an example: Why does this additional join increase # of queries?

That being said, if you're using SQL Server L2S is a better option, as L2E is meant to handle any database; Because of which L2E will generate inefficient SQL code. Also another point to keep in mind is neither L2S or L2E will leverage the tempDB, i.e. generating temp-tables or table variables or CTEs.

I would re-write the stored procedures, optimizing them as much as possible, and use L2S/L2E for simple queries, that would generate one round-trip (this should be as low as possible) to the server, and also ensure that the execution plan SQL Server uses is the most efficient (i.e. uses indexes etc).

Hasanain

Upvotes: 1

rboarman
rboarman

Reputation: 8214

I'd look at LLBLGen. The code that it generates is quite good and customizable. They also provide a robust linq provider which may help with your queries. I used it for a couple large projects and was quite happy.

http://www.llblgen.com/

Upvotes: 3

Related Questions