Bill Morefield My thoughts, discoveries, and occasional rambiings.

July 2, 2009

IN Queries using Linq to SQL

Filed under: Uncategorized — Bill Morefield @ 12:30 am

    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:

  1. http://blog.wekeroad.com/blog/creating-in-queries-with-linq-to-sql/
  2.  http://www.thinqlinq.com/Default/Use-the-new-LINQ-Contains-extension-method-for-the-SQL-IN-clause.aspx
  3.  

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress