How do I return data from joined tables through subsonic's objects?

Question

I'm using ActiveRecord on Subsonic 3 and I effectively want to do this:

  select * from foo
   left outer join bar on bar.Id = foo.barId
where foo.someProperty = 2

I've written a stored procedure to fetch the data but Subsonic has only created objects to hold the columns from foo and bar.

What's the best way of returning the data into a single object so I can just bind it. Ideally I want it to be in a list<> but without writing my own class, there doesn't seem to be a way provided by subsonic.

Answer 1

You have a couple options here...

You could create a database view that does your join, and have SubSonic generate a data type for your view, then your select would be just like selecting from any other table.

Alternatively, you could use a Linq expression to do the join into an anonymous or dynamic type (if you are using .net 4) For example:

public List<dynamic> LoadData(int id)
{
  var data = from f in db.Foo
             from b in db.Bar.Where(x => x.Id == f.BarId).DefaultIfEmpty()
             where f.SomeProperty == id
             select new
             {
               SomeProperty = f.Something,
               AnotherProperty = b.SomethingElse
             };
  return data.Cast<dynamic>().ToList();
}

Of course another alternative is to do the Linq expression above, but define your own class to hold the returned data, and select into it.

public class MyData
{
  public string SomeProperty { get; set; }
  public string AnotherProperty { get; set; }
}

public List<MyData> LoadData(int id)
{
  var data = from f in db.Foo
             from b in db.Bar.Where(x => x.Id == f.BarId).DefaultIfEmpty()
             where f.SomeProperty == id
             select new MyData()
             {
               SomeProperty = f.Something,
               AnotherProperty = b.SomethingElse
             };
  return data.ToList();
}