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.  

Complex Object Databinding to a Gridview

    I’ve recently been taking the time to clean up a couple of projects before I hand them off to someone else to manage.  Both were done fairly quickly under a severe time pressure and a hard deadline so design was sacrificed to getting them working.

     

    The web application connects to a large application using an Oracle database that contains a lot of data, most of which is irrelevant to this application.  Originally the application connected and pulled information out using SQL calls embedded into the application itself.  As part of this cleanup, I wanted to move all these SQL calls into an assembly that can be reused in this and other related projects that will need to access this same database in the future.  It will also help us update when newer versions of that other system come out, which happens about every eighteen months.

     

    First I had to pull out the database code and build an assembly that wrapped objects around the database calls.  Now instead of a SQL statement, a simple repository pattern allows data to be queried and objects representing the data to be used.

     

    When I went to the original web application to change it to use the new complex objects being returned, I ran into my first real problem.  In a number of places I’m using a GridView to display data that matches a search.  It turns out that .NET does not work well with objects that contain properties which are other objects.  For a simple example, take the following two classes:

     

        public class Book

        {

            public string Title { get; set; }

            public Person Author { get; set; }

        }

     

        public class Person

        {

            public string Firstname { get; set; }

            public string Lastname { get; set; }

        }

     

    Now take a collection of Book objects and attempt to bind it to a GridView designed like this.

     

    <asp:GridView ID=”BookView” runat=”server” CssClass=”grid” Width=”90%”>

        <Columns>

            <asp:BoundField DataField=”Title” HeaderText=”Title” />

            <asp:BoundField DataField=”Author.Lastname ” HeaderText=”Last Name” />

        </Columns>

    </asp:GridView>

     

    When you run this the gridview will complain that it cannot dechipher Author.Lastname.  Instead of looking for an object that is a property named Author and then getting the Lastname property of it, it instead seems to look for a property named “Author.Lastname” which of course does not exist.  Why this doesn’t work I have no idea, but it doesn’t.

     

    After searching the Internet, I found three possible solutions:

  1. Turn the column into a TemplateField and use the DataBinder.Eval approach which will correctly look for an Author property and then the Lastname property of it.  It works, but leaves your code a bit messy.  The above example would now look like this:
     
  2. <asp:GridView ID=”BookView” runat=”server” CssClass=”grid” Width=”90%”>

        <Columns>

            <asp:BoundField DataField=”Title” HeaderText=”Title” />

            <asp:TemplateField DataField=”Author.Lastname ” HeaderText=”Last Name”>

    <ItemTemplate>

    DataBinder.Eval(Author.Lastname)

    </ItemTemplate>

            </asp:TemplateField>

        </Columns>

    </asp:GridView>

     

    While this works pretty well for one column, imagine if you had ten columns to do like this.

     

  3. Build properties into the parent object that allow you to access the children directly.
  4. To do this you would modify our Book class above to read:

        public class Book

        {

            public string Title { get; set; }

            public Person Author { get; set; }

            public AuthorLastname

     get

    {

    return Author.Lastname;

    }

    Public AuthorFirstname

    {

    Get

    {

    Return Author.Firstname;

    }

            }

        }

     

    This works, but seems to defeat the purpose of having classes in the first place.  Still for only one or two properties and a lot of bound objects, this is probably the approach that I’d use.

  5. The other option I found is to use something a little “smarter” than the built in BoundColumn that understands complex objects and handles them appropriately.  For two examples of doing this see:* ComplexBoundField – Databinding Complex Objects to a Gridview and ObjectField 1.1.
  6.  

    In my case I chose to do the third option and used the ObjectField class by James Gregory.  Only thing that I needed to do was add code to register the new assembly on the pages and change the BoundColumn to the new ObjectField column.  Once I did that, everything worked as expected.