Let Me Tell You About This Little Thing Called the Internet

I find it amazing that in 2009 there are still so many businesses that do not have even the most basic of web sites.  I tried to look up a couple of restaurants in Morristown, TN today.  One did have a MySpace page that I found after playing a few rounds of the “pick the right search terms” game, but the other had absolutely nothing that I was able to find except for review on third part sites.

 

I imagine that anyone reading this probably already know this, but a simple one page web site with your hours, contact info, and some basic information (menu for a restaurant, etc.) will cost you less than $10 a month.  That’s probably the cheapest advertising you’ll ever get and I think you’ll find it worth every penny.

IN Queries using Linq to SQL

    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.