So my head is all turned around this morning and it honestly has nothing to do with our proximity to the new year.  I've been whacking around building the IRepository layer for an MVC + ESRI JS API application and SubSonic has been giving me fits trying to join across multiple tables with multiple T-SQL IN statements.  I've been test driving SubSonic 3 which is very LINQ-y indeed making sure it will handle some of the more hoopty portions of an existing data schema we're working in.

Well I wanted to do a query of the sort "SELECT * FROM MyTable INNER JOIN MyManyTable ON MYTable.IDColumn = MyManyTable.MyTableIDColumn WHERE MyManyTable.SomeOtherColumn IN (values)".  There's acutally a couple of INNERJOIN terms in there but you get the picture...clearly doing repetitive ORs over a values array is just ugly and frankly smells bad.  Well I went a googlin' and didn't find much...perhaps I'm loosing my touch with the search terms or something.   After a frustrating day yesterday trying to get LINQ to generate me an in statement, a colleague passed along a web link that revealed the marvels of the Contains operator.

Turns out the answer was under my nose the entire time, right on the blog of the SubSonic creator and mastermind...  The trick is to use the contains operator and turn your where clause upside down.  I won't plagiarize the code here but for my own future reference and sanity as well as the reader's here's Rob's solution to the problem along with Greg Duncan's epiphany.  Also note that both these guys beat their heads about this just like I did which is some small measure of consolation...

So my head hurts this morning

For a project I'm currently working on, we're going to be implementing the repository pattern to provide a layer of abstraction and separation between our Model/Domain and the data access logic.  One of the things we're being mindful of is that we don't want to go toting around objects that include logic on how to get information into or out of the database.  Essentially, while SubSonic generates a great little set of DAL objects using either ActiveRecord or the Repository Pattern, we don't want to be cluing in our Model or our Controller on how to deal with data persistance and querying.  That shall be the responsiblity of one or more IRepository interfaces, likely generated by domain object (IPersonRepository, IProjectRepository, IFooRepository, IBarRepository, etc.).  So what might an IPersonRepository hand up into the Model layer of our application for manipulation by the controller?..POCOs (Plain old CLR Objects or Plain Old C# Objects).  Essentially, Dave came up with a MyGeneration template to generate a set of POCOs for our model that are simply lightweight classes with an overloaded constructor and a bunch of properties that hold values for DAL objects including contained collections of other POCOs that participate in data schema relationships (a Person POCO might contain a collection of one to many Address POCOs for example).  We want no methods or business logic on our POCOs; their job is to get data values from one place to another.

Read the rest of this post »

Currently I'm working on an MVC project in which we're considering the use of SubSonic for the DAL, with POCOs sent up to the MVC Controller from the repository.  The default generated classes out of SubSonic use ActiveRecord templating and we really wanted to use the repository pattern...enter version 2.1 of SubSonic and the use of an extra attribute on the XML for SubSonic service providers.  Rob has a pretty good screencast of it over here...and provides a repository template update from his blog so that repository CRUD methods will also accept an IEnumerable<T> allowing you to batch update lists of items.  This is all very cool stuff and is making development life go much faster. Dave is wiring up some MyGeneration templates for some stripped down POCOs and once we get that in place we should have an end-to-end MVC proof of concept up and moving pretty quickly.