I kind of left my last post on this subject dangling, but now I’m ready to come clean. I was proceeding merrily along, removing the tightly coupled LINQ to SQL generated code into something nice and loose, but ran into some serious snags when trying to update the database objects that had joins associated with it.
The code became more and more unwieldy; I was creating extra loader classes for longer inheritance chains, and more and more abstractions to get everything to work properly. At some point, a little voice in the back of my head started saying, this is supposed to be EASIER? Finally it reached a point where it simply did not appear that I would be able to meet my goal of defining my class models separately from LINQ to SQL’s generated ones.
So, I gave up and let the idea die while I focused on other things. Occasionally I did revisit it, but only met with the same conclusion.
About a month ago I became interested in the new ASP.NET MVC framework. Rob Conery has been publishing a series of videos that detail his process of creating an online store. While watching those, I realized that he was implementing the very idea that I had wanted to do here. And amazingly, the code to do it was surprisingly simple and small.
After studying it and poking around, here I am today with the answer! It lies in what Rob calls the Repository pattern. It boils down to this: you have your model classes defined somewhere (in the sample app, I’m using LinqExample.Core), an IRepository interface that states the methods that will interact with the database, and the implementation (LinqRepository here). Rob goes on to create a Service class that sits between the UI and Repository, but for this example, I’m leaving that out.
public interface IPersonRepository {
IQueryable<Person> GetAll();
Person GetSingle(int personId);
IQueryable<TpsReport> GetReports(int personId);
int SavePerson(Person person);
int SaveReport(TpsReport report, Person forThisPerson);
}
This also makes testing easy. I can cook up a TestPersonRepository that returns dummy data, allowing any UI testing to avoid touching the database.
Next, we create the implementation of this interface using LINQ to SQL. Add the .dbml ![CropperCapture[13]](http://www.sogeti-phoenix.com/Blogs/image.axd?picture=WindowsLiveWriter/LINQToSQLandTightCouplingPart4_D0D2/CropperCapture%5B13%5D_thumb_1.png)
file and drag the two tables onto the workspace as you would normally. The key part here is to change the Context Namespace and Entity Namespace to something other than the default (an empty string). I used LinqExample.Data.Repository. This has the effect of namespacing each generated class. Thus, they will not conflict with the model classes we have already defined.
Our example actually looks similar to what I laid out in Part 3, however I do not use the Entity types for the joining objects, but rather IQueryable<T>. This allows a lazy evaluation of the objects, and let’s LINQ to SQL’s binding work better. Additionally, I reverted all of the properties in the classes to the basic auto-properties that C# 3.0 brings us.
public class Person {
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public IQueryable<TpsReport> Reports { get; set; }
}
The key piece from Rob’s code is how he pulls the data using LINQ to SQL. Rather than just returning the auto-generated classes, he selects the actual Person or TpsReport class and the initializers to populate them:
public IQueryable<LinqExample.Core.Person> GetAll() {
var people = from pe in this.db.Persons
select new Person {
Id = pe.id,
FirstName = pe.fname,
LastName = pe.lname,
Reports = this.GetReports(pe.id)
};
return people;
}
At this point you may be thinking "All you did is create a class and just do a bunch of right/left property setting." While that is true (and the right/left bit does get tedious), it does more than that by return IQueryable<T>.
Returning as an IQueryable<T> has an extra advantage of letting you do further queries without having to throw away parts of the initial return set. Thus, if you chose to implement the Service layer, you could keep GetAll() in the Repository implementation, and have GetSingle(), GetWithLastName(), and others in the Service assembly.
If you returned an IList<T> instead, you would need to call ToList(), which would run the whole query right then, and forsake all the lazy evaluation benefits from IQueryable<T>. Of course, you may want to do that, but that is what the Service Layer is for.
Updating the database is not quite as simple, but doesn’t involve black magic either. The gist of it is to check to see if this is an update or an insert call, and to perform accordingly. Here is SavePerson(), but SaveReport() is very similar
public int SavePerson(Person person) {
using (Repository.LinqExampleDbDataContext saver = new Repository.LinqExampleDbDataContext()) {
Repository.Person pe = saver.Persons.SingleOrDefault(p => p.id == person.Id);
bool isNew = false;
if (pe == null) {
isNew = true;
pe = new Repository.Person();
}
pe.fname = person.FirstName;
pe.lname = person.LastName;
if (isNew) {
saver.Persons.InsertOnSubmit(pe);
}
saver.SubmitChanges();
return pe.id;
}
}
And, you’re done! This is a little easier to swallow than what I had going in Part 3, no XML to muck around with, and is far simpler than the code soup I was cooking up before abandoning that approach.
Should you decide to go with another technology, be it the Entity Framework, NHibernate, or even a non SQL Server database, you would only need to create an IRepository implementation for that particular framework/database. It can be tedious for large quantities of tables, but that is the price you might have to pay for loose coupling and a friendlier design. Of course, you could refactor that to automatically set properties through Reflection or something, but that is another article altogether.
Source code for this post is
located here.
![CropperCapture[13]](http://www.sogeti-phoenix.com/Blogs/image.axd?picture=WindowsLiveWriter/LINQToSQLandTightCouplingPart4_D0D2/CropperCapture%5B13%5D_2.png)