ORM made easy with Linq to SQL and Extension Methods
Few things get developers as passionate, angry, or roused as ORM (Object Relational Mapping) strategies, short of dissing Mountain Dew. This is probably because there are so many ways to do it and doing it wrong can result in big, big headaches. Nevertheless, this is an area that is really pretty straight forward. I’ve got some stuff over here that is shaped one way and some stuff over there that looks (maybe very) different, now map! I think what gets people in a lot of trouble is that they want a magic button they can press that will generate everything for them. Now, of course this might work on some situations: i.e. RAD (Rapid Application Development) or small app scenarios and fortunately there are some great tools out there that facilitate this process: NHibernate, L2S, EF, and many others. However, what I’m striving for here is something more... enterprisey.
Requirements
Before we get into our ORM example let’s start by defining some enterprisey requirements:
· POCOs - I want my Domain Model to be represented as Plain Old Clr Objects. That means no required Interfaces, base Classes, or Attributes. This allows the rest of my application to be loosely coupled to my DAL implementation.
· No Domain Model Limitations. This means my classes can “look” one way and my database structure can “look” completely different.
· Maintainability - I want the mapping logic for each entity to exist in only one place. I have seen developers using Linq To Sql do the mapping into their entities for every single operation. God forbid they ever have to change the way their entity maps! Or, furthermore, what happens when you have child entities, ouch!
· Readability - As always I want code to be as concise as possible.
Example Domain Definition
For this example we’ll use a simple Question and Answer application.
· There are 3 types of Answers
o Text, Numeric, and Multiple Choice
· When you define a Question you define what type of Answer it takes and if applicable, the multiple choice options
Here’s our Domain Model:
Now here’s our Database:
I highly recommend using Stored Procedures to do Inserts, Updates, and Deletes. T-SQL is really much better at this type of work than C#, despite many of the conveniences built into Linq To Sql (including optimistic concurrency). Furthermore, the SP support in Linq To Sql is one of my favorite features. On the other hand Linq queries combined with Property Initializers yield very readable code for mapping our data structure into our Domain Model. For this example I have defined 6 Stored Procedures:
· InsertAnswer
· InsertChoice
· InsertQuestion
· UpdateAnswer
· DeleteChoice
· DeleteQuestion
Solution Definition
I have 3 Data Access classes: QuestionRepository, AnswerRepository, and ChoiceRepository. Their responsibilities should be pretty obvious. Now for some code, here’s the QuestionRepository:
public class QuestionRepository
{
/*Instantiation Of My DataContext*/
private QuestionAndAnswer.DataAccess.QuestionAndAnswerDataContext dataContext = new QuestionAndAnswer.DataAccess.QuestionAndAnswerDataContext();
public void InsertQuestion(Question question)
{
/*I'll be getting a new Id and Timestamp back from the database after my insert*/
/*These are Output parameters on the Stored Procedure*/
int? id = null;
Binary timestamp = null;
/*Linq To Sql will map the values of the SP Output parameters into my id and timestamp variables*/
this.dataContext.InsertQuestion(ref id, question.Text, (int)question.AnswerType, ref timestamp);
/*Update my entity*/
question.Id = id;
question.Timestamp = timestamp.ToArray();
}
public void DeleteQuestion(Question question)
{
/*Does it get any easier than this?*/
this.dataContext.DeleteQuestion(question.Id, new Binary(question.Timestamp));
}
public Question GetQuestionById(int id)
{
//Notice the actual mapping logic has been encapsulated in the Map() function
//which is an Extension Method located in the QuestionMapper class.
//This allows us to write extremely short, concise code.
return (from q in this.dataContext.Questions
where q.QuestionId == id
select q.Map()).FirstOrDefault();
}
public IList<Question> GetAllQuestions()
{
//Here we are using the same Map() function from a different query.
return (from q in this.dataContext.Questions
select q.Map()).ToList();
}
}
Ok, so we’ve yet to get to some serious ORM logic. Here’s the QuestionMapper class which translates our Linq to Sql entity (QuestionAndAnswer.Data namespace) into the Question domain entity. Notice that in order to populate the Choices property, which is a collection of Choice objects, we call the .Map() extension method for the Choice entity.
internal static class QuestionMapper
{
public static Question Map(this QuestionAndAnswer.DataAccess.Question q)
{
return new Question()
{
AnswerType = (AnswerType) q.AnswerTypeEnum,
Choices = q.Choices.Select(i => i.Map()).ToList(),
Id = q.QuestionId,
Text = q.QuestionText,
Timestamp = q.Timestamp.ToArray()
};
}
}
Conclusions
· By using extension methods we have encapsulated our ORM logic, making that code easier to maintain and improving the readability of code that uses these operations.
· Using Stored Procedures for Inserts, Updates, and Deletes allows us to use the best tool for the job. In this case T-SQL is far better at performing these types of operations than C#. Furthermore, the Stored Procedure support in Linq to Sql allows us to utilize Output parameters for free. The result is very maintainable and concise code.
I’ve used this approach on a fairly large application composed of about 50 domain entities and somewhere around 75 database tables. I am very pleased with the results. The only drawback is that the mapping code does have to be coded. I know that some tools allow this sort of thing to be automated but I am yet to see anything that does so without placing serious limitations on the Domain Model. If you download the source I suggest taking a look at the AnswerMapper to see how the Inheritance hierarchy is resolved. In the next installment I’ll look at the next layer in the architectural stack, the Business Façade, where we’ll wrap our Data Logic with Business Logic and Transactions onwards toward the goal of building a Service Oriented Application.