Object mapping with LINQ and SubSonic

Question

I'm building a small project with SubSonic 3.0.0.3 ActiveRecord and I'm running into an issue I can't seem to get past.

Here is the LINQ query:

var result = from r in Release.All()
             let i = Install.All().Count(x => x.ReleaseId == r.Id)
             where r.ProductId == productId
             select new ReleaseInfo
             {
                 NumberOfInstalls = i,
                 Release = new Release
                 {
                     Id = r.Id,
                     ProductId = r.ProductId,
                     ReleaseNumber = r.ReleaseNumber,
                     RevisionNumber = r.RevisionNumber,
                     ReleaseDate = r.ReleaseDate,
                     ReleasedBy = r.ReleasedBy
                 }
             };

The ReleaseInfo object is a custom class and looks like this:

public class ReleaseInfo
{
    public Release Release { get; set; }
    public int NumberOfInstalls { get; set; }
}

Release and Install are classes generated by SubSonic.

When I do a watch on result, the Release property is null.

If I make this a simpler query and watch result, the value is not null.

var result = from r in Release.All()
             let i = Install.All().Count(x => x.ReleaseId == r.Id)
             where r.ProductId == productId
             select new Release
             {
                 Id = r.Id,
                 ProductId = r.ProductId,
                 ReleaseNumber = r.ReleaseNumber,
                 RevisionNumber = r.RevisionNumber,
                 ReleaseDate = r.ReleaseDate,
                 ReleasedBy = r.ReleasedBy
             };

Is this an issue with my LINQ query or a limitation of SubSonic?

Answer 1

I think the issue might be that you're essentially duplicating the functionality of the ORM. The key thing to understand is this line:

from r in Release.All()

This line returns a list of fully-populated Release records for every item in your database. There should never be a need to new up a release anywhere else in your query - just return the ones that SubSonic has already populated for you!

Using this logic, you should be able to do the following:

 var result = from r in Release.All()
              select new ReleaseInfo {
                  Release = r,
                  NumberOfInstalls = Install.All().Count(x => x.ReleaseId == r.Id)
              };

That being said, you should look at the Install.All() call, because that's likely to be tremendously inefficient. What that will do is pull every install from the database, hydrate those installs into objects, and then compare the id of every record in .NET to check if the record satisfies that condition. You can use the .Find method in SubSonic to only return certain records at the database tier, which should help performance significantly. Even still, inflating objects may still be expensive and you might want to consider a view or stored procedure here. But as a simple first step, the following should work:

var result = from r in Release.All()
             select new ReleaseInfo {
                 Release = r,
                 NumberOfInstalls = Install.Find(x => x.ReleaseId == r.Id).Count()
             };

Answer 2

We have a bug with projections that trips on certain occassions - I think it's been patched but I need to test it more. I invite you to try the latest bits - I think we've fixed it... sorry to be so vague but a bug worked it's way in between 3.0.0.1 and 3.0.0.3 and I haven't been able to find it.

Answer 3

I think I've found the actual answer to this problem. I've been rummaging around in the SubSonic source and found that there are two types of object projection that are used when mapping the datareader to objects: one for anonymous types and groupings and one for everything else:

Here is a snippet: Line 269 - 298 of SubSonic.Linq.Structure.DbQueryProvider

IEnumerable<T> result;
Type type = typeof (T);
//this is so hacky - the issue is that the Projector below uses Expression.Convert, which is a bottleneck
//it's about 10x slower than our ToEnumerable. Our ToEnumerable, however, stumbles on Anon types and groupings
//since it doesn't know how to instantiate them (I tried - not smart enough). So we do some trickery here.
    if (type.Name.Contains("AnonymousType") || type.Name.StartsWith("Grouping`") || type.FullName.StartsWith("System.")) {
    var reader = _provider.ExecuteReader(cmd);
    result = Project(reader, query.Projector);
    } else
    {
        using (var reader = _provider.ExecuteReader(cmd))
        {
            //use our reader stuff
            //thanks to Pascal LaCroix for the help here...
            var resultType = typeof (T);
            if (resultType.IsValueType)
            {
                result = reader.ToEnumerableValueType<T>();
            }
            else
            {
                result = reader.ToEnumerable<T>();
            }
        }
    }
    return result;

Turns out that the SubSonic ToEnumerable tries to match the column names in the datareader to the properties in the object you're trying to project to. The SQL Query from my Linq looks like this:

SELECT [t0].[Id], [t0].[ProductId], [t0].[ReleaseDate], [t0].[ReleasedBy], [t0].[ReleaseNumber], [t0].[RevisionNumber], [t0].[c0]
FROM (
  SELECT [t1].[Id], [t1].[ProductId], [t1].[ReleaseDate], [t1].[ReleasedBy], [t1].[ReleaseNumber], [t1].[RevisionNumber], (
    SELECT COUNT(*)
    FROM [dbo].[Install] AS t2
    WHERE ([t2].[ReleaseId] = [t1].[Id])
    ) AS c0
  FROM [dbo].[Release] AS t1
  ) AS t0
WHERE ([t0].[ProductId] = 2)

Notice the [t0].[c0] is not the same as my property name NumberOfInstalls. So the value of c0 never gets projected into my object.

THE FIX: You can simply take out the if statement and use the 10x slower projection and everything will work.

Answer 4

Has this been fixed in 3.0.0.4? I was so peeved to find this post. After 2 days of trying to figure out why my projections were not working - except when the property names matched the query exactly - I ended up here. I am so dependant on SS SimpleRepository that it is too late to turn back now. A bug like this is crippling. Any chance it is sorted out?

I went the 10x slower route for now so I can at least release to my client. Would much prefer the faster method to work correctly :)