Removing Business Logic From DAL RRS feed

  • Question

  • My coworkers and I keep getting stuck in a situation where our database SELECT statements end up being insanely long and complex.  Our DBA's usually normalize our database tables to the millionth degree and in order to get anything out, we do all sorts of complex joins and add a lot of logic into our SELECT statements to get the data out as we'd like it.  We create these complicated SELECT statements because we really don't know how to do the same thing in code, so we let the database handle it.  The DBA's have tendency to mess-up the complicated SELECT statements when they create their deployment scripts which becomes an obvious nightmare for us to debug.

    We'd love to be able to use an ORM tool to generate simple CRUD statements for the data access layer.  This way, the DBA's would only have to work with very simple queries and the complicated logic would theoretically be in code where it's easier to debug.  The following article is seems to be what I'm after... i.e., putting business logic in the middle tier rather than in the database:

    Code Project: Dude, where's my business logic?

    While the above article sounds great in theory, how does one go about working with large amounts of data in code rather than in the database itself?   I.e., when SELECT statements require a lot of complicated joins and the like, how does one accomplish this same thing in code instead of as a SELECT statement?  Do you essentially pull a large chunk of the data into in-memory datasets and then work with the in-memory data?

    Knowledge of how to do this is keeping my entire dev team from being able to use an ORM tool and I have yet to see any decent resources for explaining techniques for working with data in code rather than in SELECT statements / sprocs.

    Todd M. Taylor
    Monday, March 21, 2011 2:08 PM

All replies

  • First off.

    Your DBA's shouldn't be normalising so much that it makes using the database impossible.  Maybe you need to have a chat with them and some manager or other.

    It kind of depends how complicated your requirements are.

    LINQ is something you should take a look at.  Combined with the pipes and filters pattern you can break complex selects into simpler steps.

    I spent some time as a stand in DBA myself so I'm confident with SQL.  At first I didn't really warm to LINQ at all. 

    Then I saw the light - shining out the pipes and filters pattern.

    The weird thing about LINQ is that the SQL isn't emitted until you iterate the collection.  So what?

    Well you can so of add where clauses or whatever in a series of steps.

    So I think it's worth your while considering at least.

    Then of course there's entity framework 4.  You can edit the t4 templates and make it generate plain classes or pretty much whatever you like.  So I think EF4 is something you also probably ought to take a look at.

    Then there's that CQRS.  Paul will be a long in a bit to tell you about that. 

    Monday, March 21, 2011 3:36 PM
  • Thanks for the reply, Andy.

    I have become a bit familiar with LINQ... actually, I love it even though I'm not quite an expert yet. 

    From my understanding, LINQ to SQL is dead (or soon will be), so LINQ is used for querying objects or collections of objects but means that another means must be used to actually retrieve the data from the database and hydrate the objects.  If the database has millions of rows of data, how does one avoid pulling the entire database into memory just so it can be converted to objects to be queried by LINQ?  I.e., part of the reason why my team likes to write really complicated stored procedure is to limit the data being retrieved, figuring that we'd bring the server to it's knees if we used up too much memory.

    I am very interested in the Entity Framework.  I've only dabbled with it a little bit, but using something like the Entity Framework is the direction our development team would like to go.   I think another fear our team has (or at least I do) is that if we decide to dive into using EF, we might get to a point mid-way into a project and then discover it isn't flexiable enough and we've backed ourselves into a corner.  My experiences with past Microsoft ORM solutions, such as typed datasets, seemed like they've added so much overhead, complexity, and limitations to data access that I've kept resorting to home-grown methods.


    Todd M. Taylor
    Monday, March 21, 2011 4:10 PM
  • I think you have hold of the wrong end of the LINQ stick mate.  I recommend more LINQ reading. 

    You avoid pulling the entire database into memory to be queried by linq just by the way it works.   You write a LINQ query.  When you iterate the collection, it's translated the LINQ into an SQL query that only then pulls what you tell it to into memory.  So there is no huge collection in memory, there is only the collection your sql specifies.  I mentioned this above but it is admittedly counter intuitive.

    So if you tell it to read a record with a given Id it only reads one record out the database. 

    If you create a "collection" for everything and then use another LINQ query to specify take the first entry out that collection.... then it STILL only reads one record out the database so long as you haven't iterated through the first collection somehow.  Note that some conversions iterate everything.

    The SQL is generated when you iterate the collection.  ONLY when you iterate the collection does it goe get any data into memory.

    I probably haven't explained everything and I probably haven't explained what I have totally clearly because that'd be a fair sized article on LINQ and outside the scope of a forum post.


    LINQ to sql works and will continue to work for quite some time.  Some developers prefer it to EF.  But anyhow LINQ to SQL is an option until you decide it isn't. 

    You can use EF4 to generate your classes  for any ORM stylee solution if you wanted.

    EF4 and LINQ are not mutually exclusive. 

    I think if you put your mind to it then you can persuade EF4 to do pretty much anything.  By persuade, I meand over-riding some code if you're doing something obscure.

    Tuesday, March 22, 2011 8:47 AM
  • if you need to have business logic processing , their is no need of business layer, that is no functional requirement of processing the business rules. 

    Above the database layer , you need to have data acccess layer. That is required.


    Tuesday, March 22, 2011 12:27 PM
  • I rarely work on applications which have no business rules at all.

    Binding straight to a datatable only works on pretty simple applications.  At least in my experience.

    Tuesday, March 22, 2011 12:49 PM
  • Andy;

    You're right that I didn't have a firm grasp of how LINQ works with the Entity Framework.  I literally spent the entire day on Monday watching the 10 part EF series by PluralSight on MSDN... very informative:


    In particular, "eager loading" vs. "lazy loading" is described in one of the videos.  I already knew what lazy loading was, but I didn't understand how or when LINQ did it (until now).

    During a discussion with a co-worker, one conclusion we came to is that too much logic likely gets crammed into sprocs because the database expert knows SQL and therefore puts the logic there.  Case-in-point, on the project we recently completed, a consultant had the best understanding of how the system was supposed to work because he was on the project since it's inception.  He happened to be an SQL guru and not a programmer, so it's no wonder that most of the complicated logic got stuck in sprocs.  Well, he's now working for a different company and we're now stuck with sprocs we barely understand!

    I think it would be a good exercise for my team to *pretend* that we can only use simple SELECT statements to retrieve data from the database and thus force ourselves to code the solutions rather than hoping the database experts can solve all our problems for us ;-)  

    Todd M. Taylor
    Tuesday, March 22, 2011 10:02 PM
  • Hi Todd,

    I think you also need to be aware of what SQL is being generated.  It's possible to write LINQ which generates a zillion sql statements from one linq thingummy. 

    There seems to be a tendency for developers to assume SQL is just for the database guys and they don't need to know about it.  I think that's a mistake.

    Does rather sound like your database experts might be causing you some of the problems they solve.

    Wednesday, March 23, 2011 8:35 AM