Bill Morefield My thoughts, discoveries, and occasional rambiings.

I’ve been working on my first “real” project using Linq to SQL for the last few nights.  So far I’ve been quite happy, but tonight I ran into my first real “how do I do this?” moment.

Simplifying the description a little, I have three types of objects.  One is a location, the second is a person, and the third we’ll call an object.  An object is assigned to a location.  Right now a person is assigned to a location, but may be assigned to more than one location.  What I need to find out is which objects are at a location or locations that a person is assigned to.

That to me in would be something like this as a SQL query:

SELECT Object.id FROM Objects WHERE Objects.location IN (SELECT PersonAssignments.location FROM PersonAssignments WHERE PersonAssignments.person = @PERSONID)

In my experience ‘IN’ queries tend to be where most simple ORM systems break down.  Usually the only options I’ve found are to either write a stored procedure in SQL or come up with some hack that looks even worse than it performs.  So I began skimming through documentation and then searching and came across an article by Rob Conry at http://blog.wekeroad.com/blog/creating-in-queries-with-linq-to-sql/.

In short, it can be done, but you have to think about the problem a little differently.  First we get the locations where the person is assigned.

MyDB db = new MyDB();

var locationList = from assignment in db.PersonAssignments

where assignment.location == personId

select assignment;

And now we can then write our query like this:

var objectList = from object in db.Objects

where locationList.Contains(object.location)

select object.id;

It seems a little backwards to me at first, but works.  A nice feature (see the blog post above for a more details explination) is that since a query is only executed when you enumerate the results, this generates a single query when you enumerate the objectList.  Also nice is that you can do the NOT IN query the same way by simply using a not (!) opeator on the conditional.  So to get all the objects not in a location assigned to a person we can do:

var objectList = from object in db.Objects

where !locationList.Contains(object.location)

select object.id;

References: