cameronfletcher.com

random thoughts and discussions on the things that interest me

Stored Procedures vs. ORM

I was recently asked to give an explanation of my preference toward use of stored procedures vs. an ORM solution which happens to be a question that I have considered several times during development myself so I thought I’d throw in my 10 cents, so to speak.

Listing each of their respective benefits is not something I intend to do as there’s plenty out there in the developer community doing just that. Looking about you can find a couple of detailed post on StackOverflow.com (here and here) that describe some of the advantages and disadvantages of each solution. There was a response on one of those threads from an individual who came from a database background who was leaning toward ORM generally for reasons around the simplicity of certain operations and the reduced time in writing them. As for me, I have a tendency to lean a little the other way – whilst I agree with the reduced development time associated with an ORM solution I like the enforced separation of concerns imposed by accessing the database just through stored procedures (although I appreciate that if you’re implementing the repository pattern then there is a separation imposed there).

Knowing also that stored procedures can do much more than perform CRUD operations and queries plays a part in my opinion. Enterprise applications can benefit from stored procedures performing operations that ORM was never designed to replace like distributed transactions, message queuing etc. and remember: stored procedures can be changed\tweaked without requiring a re-release of compiled code.

In my opinion then, the factors that come in to play are speed of development vs. ease of update – and I’d go with ease of update FTW. That means if you’ve got a database team I suggest you use them.

Converting a CTE T-SQL Statement into Linq2Sql

Below is the SQL statement including the common table expression that I need to re-write using Linq2Sql for an application I’m working on. The query identifies the subset of most up-to-date records using a group by query in a CTE. It then performs a join back to the booking table to return the full list of most up-to-date records.What is noticeable about this particular piece of code is the join between table and CTE making use of the isnull keyword.

with cte (RootBookingID, CreatedTime)
as
(
    select
        isnull(ParentBookingID, BookingID),
        max(CreatedTime) -- get the latset version
    from [system].Booking
    where Deleted = 0
    group by isnull(ParentBookingID, BookingID)
)

select b.*
from [system].Booking b inner join cte
on cte.RootBookingID = isnull(b.ParentBookingID, b.BookingID)
    and cte.CreatedTime = b.CreatedTime;

When rewriting this in C#, we first declare the query for the CTE as its own variable.

//define the cte to use as an anchor
var cte = from b in this.Model.Bookings
    where b.Deleted == false
    group b by b.ParentBookingID ?? b.ID into g
    select new
    {
        BookingID = g.Key,
        CreatedTime = g.Max(b => b.CreatedTime)
    };

We can then reuse this variable within our core query (below). Notice how the join on isnull is created by explicitly defining the name of the anonymous type.

//perform a join on the cte to get the results
var qry = from b in this.Model.Bookings
    join c in cte
        on new
        {
            ID = (b.ParentBookingID ?? b.ID),
            b.CreatedTime
        } equals new
        {
            ID = c.BookingID,
            c.CreatedTime
        }
    order by b.PickUpTime
    select b;

The post here explains the stumbling blocks I encountered:

To join multi-valued keys you need to construct an anonymous type on both sides of the ‘equals’ that is the same type. The anonymous type initializer expression infers both type and name of members from the expressions that are supplied. Using the name = value syntax in the initializer you can specify the name the compiler uses when creating the type. If all members, types, and names are the same then the anonymous types are the same type.