C# – Object mapping with LINQ and SubSonic

clinqsubsonicsubsonic3

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?

Best Answer

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()
             };
Related Topic