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!