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…
[...] dealt with the inner join issue in a previous post here. But let’s consider the recursive foreign key issue. Consider a portion of our data model [...]