About a year or so ago, I read some developer opinions against the usage of stored procedures. Jeremy Miller has several blog posts on the topic and Dino Esposito writes about the topic in his enterprise architecture book. At the time, every application I had ever worked on had utilized stored procedures, so I was a bit perplexed as to how complex data access could be accomplished without them.
On a recent project, I decided to explore the concept that the database should be used for storage only. I saw some validity in the idea that business logic should reside in the code, not the database (as stored procedures). The application to which I'm referring is built upon Entity Framework, and utilizes Linq-to-entities for data access.
For simple, straight-forward queries like retrieving a set of journal entries created by a user, you can simply use something like the following:
var query = from log in Db.LogEntry where log.UserId.Equals(1) select log;
However, what about complex logic, like searching these logs while allowing the user to supply and/or choose from a long list of criteria? Consider the following method signature:
public virtual IEnumerable<Logs> SearchLogs(int? userId, DateTime? beginDateIsAfter, DateTime? endDateIsBefore, bool? hasAttachments)
{
}
You need to search the table by not only the user id, but also a several other parameters that may or may not be provided. Linq supports this (fluently) through the use of the extension method syntax and method chaining, as seen below.
public virtual IEnumerable<Logs> SearchLogs(int? userId, DateTime? beginDateIsAfter, DateTime? endDateIsBefore, bool? hasAttachments)
{
var query = Db.LogEntry.Where(log => log.UserId.Equals(1));
if (beginDateIsAfter.HasValue)
{
query = query.Where(x => x.BeginDate >= beginDateIsAfter);
}
if (endDateIsBefore.HasValue)
{
query = query.Where(x => x.EndDate <= endDateIsBefore);
}
if(hasAttachments.HasValue)
{
query = query.Where(x => x.HasAttachments == hasAttachments.Value);
}
return query.AsEnumerable();
}
You can chain on as many "Where" extensions as you please, as well as "OrderBy" extensions. I have yet to encounter a sql query that could not be accomplished in Linq to Entities (granted, some require some mind-bending code).
My biggest concern regarding this usage of linq was the performance. How many queries does this code execute and how efficient can that really be? Fortunately, due to Linq's deferred execution, no sql is executed until the last possible minute. So, in the case above, no sql is executed until the query is enumerated (the last line of the method), and only a single, parameterized query is issued.
As a result of utilizing linq and and its built in method-chaining capabilities, all of the if/then logic that would have been in a stored procedure fits nicely within the code.