Automatic SQL Joins from a LINQ Query

When I write the following LINQ query Lightspeed will query the People table then it will run a individual query for each person on the PersonContactPhone and PersonContactGeneral which is really inefficient. I can manually create the query with LINQ Join statements but I would expect Lightspeed to identity that there needs to be a SQL Join statement (EF does this). This there something I'm missing here?

 IQueryable<PersonBasicViewModel> selectedPeople = from person in UnitOfWork.People
           let primaryPhone = person.PersonContactPhones.FirstOrDefault(p => p.IsPrimary)
           let primaryEmail = person.PersonContactGenerals.FirstOrDefault(p => p.IsPrimary)
           select new PersonBasicViewModel()
           {
              Id = person.Id,
              FirstName = person.FirstName,
              MiddleName = person.MiddleName,
              LastName = person.LastName,
              PrimaryPhoneNumber = primaryPhone != null ? primaryPhone.PhoneNumber : "",
              PrimaryEmail = primaryEmail != null ? primaryEmail.Field1 : ""
           };

Below are the resulting SQL queries

SELECT
  [Person].[PersonId] AS [Person.PersonId],
  [Person].[CommonOuId] AS [Person.CommonOuId],
  [Person].[FirstName] AS [Person.FirstName],
  [Person].[LastName] AS [Person.LastName],
  [Person].[MiddleName] AS [Person.MiddleName],
  [Person].[Prefix] AS [Person.Prefix],
  [Person].[Suffix] AS [Person.Suffix],
  [Person].[TenantId] AS [Person.TenantId]
FROM
  [Person]

The following queries is executed once for every Person:

SELECT
  [PersonContactPhone].[PersonContactPhoneId] AS [PersonContactPhone.PersonContactPhoneId],
  [PersonContactPhone].[HasBounced] AS [PersonContactPhone.HasBounced],
  [PersonContactPhone].[IsPrimary] AS [PersonContactPhone.IsPrimary],
  [PersonContactPhone].[Notes] AS [PersonContactPhone.Notes],
  [PersonContactPhone].[PersonContactLocationId] AS [PersonContactPhone.PersonContactLocationId],
  [PersonContactPhone].[PersonId] AS [PersonContactPhone.PersonId],
  [PersonContactPhone].[PhoneExtension] AS [PersonContactPhone.PhoneExtension],
  [PersonContactPhone].[PhoneNumber] AS [PersonContactPhone.PhoneNumber],
  [PersonContactPhone].[TenantId] AS [PersonContactPhone.TenantId]
FROM
  [PersonContactPhone]
WHERE
  ([PersonContactPhone].[PersonId] = 31)

SELECT
  [PersonContactGeneral].[PersonContactGeneralId] AS [PersonContactGeneral.PersonContactGeneralId],
  [PersonContactGeneral].[Field1] AS [PersonContactGeneral.Field1],
  [PersonContactGeneral].[Field2] AS [PersonContactGeneral.Field2],
  [PersonContactGeneral].[Field3] AS [PersonContactGeneral.Field3],
  [PersonContactGeneral].[HasBounced] AS [PersonContactGeneral.HasBounced],
  [PersonContactGeneral].[IsPrimary] AS [PersonContactGeneral.IsPrimary],
  [PersonContactGeneral].[Notes] AS [PersonContactGeneral.Notes],
  [PersonContactGeneral].[PersonContactLocationId] AS [PersonContactGeneral.PersonContactLocationId],
  [PersonContactGeneral].[PersonId] AS [PersonContactGeneral.PersonId],
  [PersonContactGeneral].[TenantId] AS [PersonContactGeneral.TenantId]
FROM
  [PersonContactGeneral]
WHERE
  ([PersonContactGeneral].[PersonId] = 31)

Hi,

You could eager load PersonContactPhone & PersonContactGeneral, so LS would batch up 3 SQL statements to get back all of the data you need.

have a look at http://www.mindscapehq.com/documentation/lightspeed/Performance-and-Tuning/Controlling-How-Entities-Load

Cheers, Sean

Aggregates is what I've started doing. I have a very complex model and 3 developers working on the software so creating aggregates for all of these types of queries could become unruly. I've been using EF for several years but made the decision to go to Lightspeed for this new project and I was surprised when I discovered how it compiles the Linq query.

Yesterday I built a tool that reads/writes the .lsmodel XML to manage the aggregates. This tool allows the developers to view/add/edit/delete the aggregates and manage which relationships they apply to.