LINQ to SQL Mapping...I think it could work

This coding sample was inspired by Rob Conery's ASP.NET MVC Storefront videos. So here are the reasons I decided to go this route rather than NHibernate for this project.

1. I can always change it...the beauty of programming to interfaces
2. LINQ to SQL is crazy easy...and yes I've heard...it's dead, but just not being supported
3. It's something different that I haven't really seen implemented successfully
4. I'm curious to see if my method strikes up any other new ideas in the community

So first off I create my base model, which looks like this:

Next I created my LINQ to SQL, which looks like this: ( of course this is assuming I've already written my tests :)


So now I create an ISchoolRepository like this:

public interface ISchoolRepository
{
School GetSchoolById(int schoolId);
IQueryable<school> GetAll();
IQueryable<student> GetEnrolledStudentsBy(int schoolId);
}

Okay, so now we're on to the fun stuff...the maybe innovative? I'm not sure, but I hope. Now be fair warned that I did some SQL profiling on this, but not a whole lot and I plan on doing more because I'm big on performance and not having a lot of connections. Now if anyone sees immediate holes or concerns, please let me know. The newest thing I added was instead of making my mappers static classes, I made them extension methods on the datacontext...I thought it made more sense that way. Okay, let's continue...

So here is my implementation of the ISchoolRepository:

public class SqlSchoolRepository : ISchoolRepository, IDisposable
{
private readonly LearningDataContext dc;
public SqlSchoolRepository()
{
dc = new LearningDataContext();
}

public Core.Model.School GetSchoolById(int schoolId)
{
return dc.GetSchoolById(schoolId); //EXTENSION METHOD
}

public IQueryable<Core.Model.School> GetAll()
{
return dc.GetAllSchools(); //EXTENSION METHOD
}

public IQueryable<Core.Model.School> GetEnrolledStudentsBy(int schoolId)
{
return dc.GetAllStudents() //EXTENSION METHOD
.WhereIsEnrolled(true) //FILTER
.WithSchoolId(schoolId);//FILTER
}

public void Dispose()
{
dc.Dispose();
}
}

Now you see how I have the filters added on too like Rob did in his videos? I think the filters are very cool! Mainly for the readability. Okay, so here are my extension methods on the datacontext:

internal static class SchoolMap
{
internal static School GetSchoolById(this LearningDataContext dataContext, int schoolId)
{
var s = dataContext.Schools.SingleOrDefault(x => x.SchoolId == schoolId);

return new School()
{
ContactNumber = s.ContactNumber,
County = s.LookupCounty.FullName,
FullName = s.FullName,
ShortName = s.ShortName
};
}

internal static IQueryable<School> GetAllSchools(this LearningDataContext dataContext)
{
var schools = from s in dataContext.Schools
orderby s.FullName
select new School()
{
ContactNumber = s.ContactNumber,
County = s.LookupCounty.FullName,
FullName = s.FullName,
ShortName = s.ShortName
};

return schools;
}
}

One thing you should notice is that the mapping is EXACTLY the same on the GetSchoolById & GetAllSchools...unfortunately I have not found a way to get around that problem. I think that is a limitation of my LINQ knowledge more than an issue with this type of structure though. Hopefully a LINQ expert can help me out. Ideally it'd be something like this:

private static Core.Model.School getSchoolFrom(Impl.School sqlschool)
{
return new School()
{
ContactNumber = sqlschool.ContactNumber,
County =sqlschool.LookupCounty.FullName,
FullName = sqlschool.FullName,
ShortName = sqlschool.ShortName
};

Surely something like that is possible. Anyhow, now the StudentMap looks exactly the same with the exception of it being WAY longer. So you'd do the same thing as above for the student mapping and then you'd create these filters:

internal static class StudentFilters
{
internal static IQueryable<Student> WhereIsEnrolled(this IQueryable<Student> qry, bool enrolledStatus)
{
return qry
.Where(x => x.IsEnrolled == enrolledStatus);
}

internal static IQueryable<Student> WithSchoolId(this IQueryable<Student> qry, int schoolId)
{
return qry
.Where(x => x.HomeSchool.Id == schoolId);
}
}

Now I'm sure you'll ask what do I do about many to many and how am I saving and all that other jazz. The answer to the many to many is on a per case basis for instance, I'm loading the attendancedata with the student because it's an attendance application and just about every page that I use the student class I need the attendance data too. I do not load the contacts with the student because I don't always need them. So I created a GetAllContactsByStudentId method on my student repository to get that information. This is obviously not as flexible as NHibernate and will probably never replace it, but I do think it's good for small apps that don't require a lot of transactions.

You should also notice that I keep all my LINQ to SQL objects internal and really only use them in the mapping extension methods. I noticed that Rob was not able to keep his internal and I think that started making the storefront way more confusing than it should've been. I need to go download the latest version and see how it has come along. As for saving and what not I have not gotten to that point. The app I'm working on is mostly read-only, but there are a few items that require updating & saving and I'll post again when I get to that point.

So my UI only has access to the repositories & the model...that's it. Since that's true, it seems like it'd be really easy to completely switch out LINQ to SQL to NHibernate if I decided to go that route. Again, I don't think I'd recommend this method if you had a lot of read/writes and needed HIGH performance as in you only want to load certain fields when required, etc.

Anyhow, I thought this was pretty cool and thought I'd share. Once I setup how I'm going to save, I'll post it on here too. Thanks and please provide feedback!

What page am I on?

So it's been 4 years since my last post and obviously I was right about it not being addicting...at least for me it's not. Anyhow, this is my first post as a developer learning from peers or at least hoping to. I keep seeing all these posts about automapper, dependency injection, TDD, MVC, being agile, etc, etc.

Apparently I'm just not seeing the big picture or something because I can get behind a couple of the things mentioned above, but not everything.

I see the benefit of dependency injection and I believe I'm doing it with StructureMap. I have a web forms app that seems to be working with it, but a lot of people have made it look a lot more complicated than the way I'm using it. I'll give an example in a bit.

I see the benefit of TDD and again, I believe I'm doing it with NUnit & ReSharper. I have only really gotten into the unit tests and not so much into regression or integration testing. I even have a few tests that use Rhino.Mocks, but I'm not 100% sure they're setup right. They pass and fail when I want them to so I guess they're right. Again...example in a bit.

I definitely see the benefit of agile development and I REALLY like the idea of user stories instead of requirements. My team is about to start our first true agile project from start to finish and it's definitely going to be interesting. There are a few people on my team that aren't crazy about change and tend to do things because that's the way they're done.

Now automapper is one thing I'm not sure I'll benefit from yet. I think Jimmy Bogard is probably the best developer I know at the moment and I'm sure he created something great, but I don't see the benefit for me and my team right now. I'm pretty confident that I will eventually slap myself in the face for posting this just because I'm sure it is an awesome tool that I will one day see as VERY cool, but right now...I don't. Maybe it's because I work for a school district and our projects just aren't big enough to warrant this type of implementation. I don't know, but I'm confident that the community will shine some light.

MVC is something else that I will be behind eventually, but right now I'm too busy learning the agile approach to things. I can definitely see the benefit of MVC especially when it comes to TDD, but it's pretty much exactly like going from classic ASP to ASP.NET. Hopefully Jeffrey, Jimmy, and Ben's book, ASP.NET MVC in Action sheds a lot of light on the topic for my team and me. I bought the early edition and I really liked what I read primarily because they're doing things the right way and not concerned with whether or not you understand. Seems like that last statement could be taken as a bad thing, but it's really not. I'm tired of buying books that go over the same crap, but using common practices instead of recommended methods. For example, if you pick up ANY ASP.NET book, they ALL have the same exact stuff and not one that I own shows you the correct way to do any of it. Like the using statement for disposing of objects. Jimmy showed me that and I own at least 8 .NET books. Maybe I was just buying the wrong books, but they all seem the same to me.

Okay...let's get to the examples. Hopefully Jimmy reads this at some point and he can point me in the direction of how he does his code samples on his blog. I'm sure there is some tool he uses...I don't see him doing anything by hand.

So dependency injection...I watched most of Rob Conery's videos on MVC and I think he did a great job of getting the right people on his videos to help out. Although I'm pretty sure my least favorite video is the one with his boss on the AJAX stuff. I think it's video 14..some where around there.

So this is how I handled my dependency injection using web forms:

First thing I did was created a static Bootstrapper class in the App_Code and it looks like this:

public static class Bootstrapper
{
public static void BootStrapStructureMap()
{
ObjectFactory.Initialize(x => x.AddRegistry(new SampleRegistry()));
}
}

The SampleRegistry is a class that inherits from StructureMap's Registry and it looks like this:

public class SampleRegistry : Registry
{
public SampleRegistry()
{
ForRequestedType<ISampleRepository>()
.TheDefaultIsConcreteType<SqlSampleRepository>();
}
}

So once I created my Registry class and static Bootstrapper, I throw it in the Application_Start of the global.asax, which looks like this:

void Application_Start(object sender, EventArgs e)
{
Bootstrapper.BootStrapStructureMap();
}

I think the syntax for StructureMap's GetInstance method is ugly and I didn't want to depend on them not changing that syntax, so I created a helper method that looks like this:

public class DI
{
public static ISampleRepository CreateSampleRepository()
{
return ObjectFactory.GetInstance<ISampleRepository>();
}
}

Now I can create my IndependentPage that has a protected field for my repository like this:

public class IndependentPage : Page
{
protected ISampleRepository samplerepository = DI.CreateSampleRepository();
}

And now I can use it in any page that inherits IndependentPage like so...

public partial class _Default : IndependentPage
{
protected void Page_Load(object sender, EventArgs e)
{
}

protected void GetAllOfSomething(object sender, EventArgs e)
{
gvList.DataSource = samplerepository.GetAll();
gvList.DataBind();
}
}

I'll show my example of my use of Rhino.Mocks in another post...this one has gotten WAY longer than I expected. So please give me some feedback. I'm really curious to see where I'm at in the community...newb...okay I guess...decent...good...great...who cares