Multiple join in the selection of the object which is the parent in inheritance

Hi Guys,

We implemented the class table inheritance, based on the following ER
Diagram (see screenshot here: http://screencast.com/t/YmQ1YTIwZTAt http://screencast.com/t/OWJhMDcx), What we found out is that ORM builds very ineffective SQL joins when we try to get a class instantiated:

<code>
            var UoW = new LightSpeedContext<LightSpeedUnitOfWork>();
            UoW.ConnectionString = this.connectionString;
            UoW.CommandTimeout = new TimeSpan(0, 0, this.CommandTimeout);
            UoW.DataProvider = DataProvider.MySql5;
            using (var context = UoW.CreateUnitOfWork())
            {
                return context.Projects.FirstOrDefault(o => o.Id == EntityID)                    
            }
</code>

where our Project object is a child of Registry class. See the SQL we're
getting here:


SELECT
  Registry.registryID AS "Registry.registryID",
  Registry.ConsumerId AS "Registry.ConsumerId",
  Registry.Created AS "Registry.Created",
  Registry.CreatedBy AS "Registry.CreatedBy",
  Registry.Deleted AS "Registry.Deleted",
  Registry.DeletedBy AS "Registry.DeletedBy",
  Registry.StatusId AS "Registry.StatusId",
  Registry.TypeId AS "Registry.TypeId",
  Registry.Updated AS "Registry.Updated",
  Registry.UpdatedBy AS "Registry.UpdatedBy",
  files.Description AS "files.Description",
  files.Path AS "files.Path",
  files.RelatedTo AS "files.RelatedTo",
  files.Size AS "files.Size",
  files.Title AS "files.Title",
  notes.Description AS "notes.Description",
  notes.ParentNoteId AS "notes.ParentNoteId",
  notes.RelatedTo AS "notes.RelatedTo",
  notes.Title AS "notes.Title",
  projects.Billable AS "projects.Billable",
  projects.Budget AS "projects.Budget",
  projects.Description AS "projects.Description",
  projects.Title AS "projects.Title",
  tasks.Billable AS "tasks.Billable",
  tasks.Description AS "tasks.Description",
  tasks.DueDate AS "tasks.DueDate",
  tasks.LimitHours AS "tasks.LimitHours",
  tasks.RelatedTo AS "tasks.RelatedTo",
  tasks.Title AS "tasks.Title",
  users.Email AS "users.Email",
  users.FirstName AS "users.FirstName",
  users.LastName AS "users.LastName",
  users.Password AS "users.Password"
FROM
  Registry
LEFT OUTER JOIN   files files  ON   Registry.projectID = files.fileID
LEFT OUTER JOIN   notes notes ON   Registry.projectID = notes.noteID
LEFT OUTER JOIN   projects projects ON  Registry.projectID = projects.projectID
LEFT OUTER JOIN   tasks tasks ON  Registry.projectID = tasks.taskID
LEFT OUTER JOIN   users users ON   Registry.projectID = users.userID
WHERE
  (projects.registryID = 195054 AND Registry.TypeId = 'PRJ')




Why it join tables for all child of Registry?

This is to do with the way inheritance works internally in LightSpeed: essentially LightSpeed has a model of the whole inheritance hierarchy, and does not unpick a subhierarchy when querying on a derived class.  We're aware that there's scope for optimisation here -- especially where hierarchies are very broad -- and it's on our wish list, but I can't give any indication of when it might be implemented.