4

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:

  • A bunch of strings and conditionals that add bits of the query as they are needed. Really nasty, especially when queries get complex, and not something I want to pursue if a better solution exists.
  • A bunch of conditionals using L2E. Looks more elegant, but I tested L2E is too bloated in general was an awful experience. Could I do the same thing in L2S? If so, is L2S going to stick around for the next 5-10 years?
  • Use a PredicateBuilder. Still looking into this, same questions regarding L2S.
  • EDIT: I can also just stick to the existing stored procedure model, but I have to rewrite them anyway, so it can't hurt to look at other options as I'm still going to have to do the leg work.

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?

Community
  • 1
  • 1
tennesseepusher
  • 41
  • 2
  • just my two cents, i was really excited about EF4 when it came out. Now after using it for the past year, i have been really unhappy with the bloat, the complexity in certain modelling scenarios, and the produced SQL. I'm never going to use EF again - stored procedures with a thin API over the top all the way. Doesn't answer your question - but thought id add my two cents. – RPM1984 Jun 01 '11 at 00:15
  • As of .Net 3.5, popular opinion held that L2S generated more efficient sql than L2E. I don't know if that has changed with the updates to L2E (including, and beyond .net 4.0). It should be fairly trivial to test your same conditions in L2S. **Have you considered just keeping the current stored procedures?** – jlnorsworthy Jun 01 '11 at 00:17
  • @jlnorsworthy - that's certainly an option, and I should have added it above (I'll edit it in). The important part is that no matter what option I go with, they need to be rewritten (they're incredibly over-complex and painfully slow), so now's a good time to shop around and see what the alternatives look like. – tennesseepusher Jun 01 '11 at 00:25
  • Could you elaborate on "L2E is too bloated in general was an awful experience" ? – jwd Jun 01 '11 at 00:51
  • Maybe this should be community wiki? Doesn't seem like it will have a concrete answer... – jwd Jun 02 '11 at 15:06
  • Are these conditionals just filters (in where clause) or other parts (select, from, join etc)?? – Schotime Jun 07 '11 at 12:00

3 Answers3

3

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/

rboarman
  • 8,248
  • 8
  • 57
  • 87
1

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.

jwd
  • 10,837
  • 3
  • 43
  • 67
1

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

Community
  • 1
  • 1
Hasanain
  • 925
  • 8
  • 16