Loading a List with LINQ

Clayton posted on Wednesday, August 29, 2007

I had a few spare minutes and was playing with LINQ to SQL. I had a BusinessListBase as my root and a Load method that would load a DataReader. It would then enumerate each row in the reader, passing the reader into a child object so the child object could load itself from the reader.

With LINQ to SQL, this doesn't look like it's going to be a feasible pattern anymore. The LINQ query returns a dynamically typed list that can't be passed into other methods. I was hoping to use LINQ to "strongly type" my database, but it's not looking good. Has anyone come up with something that would save me some effort before I put a lot of time into this?

I've put some pseudo code below to illustrate the pattern. Although not illustrated below, I like this pattern because I can have several different entry points for loading the object, but only one place where the object loads itself from the DataReader.

public class MyList: BusinessListBase<MyList, ListItem>
{
    public static MyList LoadList()
    {
       ...
       SafeDataReader reader = new SafeDataReader(command.ExecuteReader());
       while (reader.Read())
       {
          ListItem item = ListItem.LoadChild(reader);
          Add(item);
       }

       return list;
    }
}

public class ListItem: BusinessBase<ListItem>
{
    internal static ListItem LoadChild(SafeDataReader reader)
    {
       ListItem item = new ListItem();
       item.MarkAsChild();
       item._myId = reader.GetInt32("MyId");
       ...

       return item;
    }
}

RockfordLhotka replied on Wednesday, August 29, 2007

Look at the DeepData sample - it has a sub-section that uses LINQ to load the object graph.

The key is to realize that LINQ returns DTOs (data transfer objects), much like you'd get from a web service or WCF service call. You still need to map that data into your business object.

This is really easy in dynamic languages like VB, and somewhat harder in C# - welcome to the new world order   The same will likely be true if/when IronPython and IronRuby support LINQ too - all these dynamic languages have a big upside when dealing with LINQ projections.

However, if you use the strongly-typed design tools in VS 2008 things work pretty well in C# too. This is because the tools create a set of DTO classes that look like your database. When you run the LINQ for SQL query you need to avoid using projections, and instead get back data in those DTO types.

Because those types are not dynamic, C# can deal with them easily. You can pass the DTOs through like you would a DataReader from parent to child list to child.

DeepData uses this technique, so it is a valuable example for C#. Just ignore the clever bits where the same code works for DTOs from LINQ or web services interchangeably thanks to the dynamic language features 

JoeFallon1 replied on Wednesday, August 29, 2007

Check out the blogs for Scott Guthrie and Rick Strahl.

They both discuss LINQ in detail. Scott has all of MS prepping him.

Rick is just diving in and discovering stuff.

I definitely recall reading that you could get LINQ to return a datareader from a query.

So there will be many solutions to this problem in the near future.

I would definitely like to strong type my queries if possible.

Joe

 

Clayton replied on Wednesday, August 29, 2007

I was actually hoping that I could find a way that avoided DTOs. That feels like I'm almost writing my business objects twice. One to load the data from the database, and one to do the actual work. I know the DTO can get away with only have properties and zero business logic, but it somehow feels redundant to have them.

The DTOs created by the strongly-typed design tools are easy enough for me to justify because they're what I was wanting to use to strongly-type the database. The rub comes when I just want to query only a couple columns in one table and join with a couple of columns in a second table. It just isn't giving me the small objects I really want.

RockfordLhotka replied on Wednesday, August 29, 2007