views:

83

answers:

0

When i query a collection of Persons from the database i want to select the list of positions each person has.

I chose to map Linq Entities to a domain model so i have to build a custom entity for each position. When the query is executed i see in the profiler that for each person loaded an additional select of positions is executed leading to a enormous count of statements.

Is there any chance to rebuild the query? I tried using an IEnumerable instead of a IList but with no good results.

Any advice is appreciated.

return from c in persons
 join cp in context.crm_Contacts.OfType<crm_Company>() on c.ParentCompanyKey equals cp.ContactId
 let p = GetPhones(GetCommunicationQuery(c.ContactId, context.crm_Communications, CommunicationType.Phone))
 let f = GetFaxes(GetCommunicationQuery(c.ContactId, context.crm_Communications, CommunicationType.Fax))
 let e = GetEmails(GetCommunicationQuery(c.ContactId, context.crm_Communications, CommunicationType.Email))
 let w = GetWebsites(GetCommunicationQuery(c.ContactId, context.crm_Communications, CommunicationType.Website))
 let positions = GetPositionsOfPerson(c.ContactId)
 where c.IsActive
 select new Person
 {
  Id = c.ContactId,
  CompanyId = c.ParentCompanyKey,
  CompanyName = c.ParentCompanyKey == null ? String.Empty : cp.Name,
  Name = c.Name,
  Firstname = c.Firstname,
  Surname = c.Surname,
  BusinessTitle = c.BusinessTitle,
  Mima = c.Mima,
  Title = LookupManager.Get<Title>(c.TitleTypeKey),
  Salutation = LookupManager.Get<Salutation>(c.SalutationTypeKey),
  SalutationLetter = LookupManager.Get<SalutationLetter>(c.LetterSalutationTypeKey),
  Phones = new LazyList<Phone>(p),
  Faxes = new LazyList<Fax>(f),
  Emails = new LazyList<Email>(e),
  Websites = new LazyList<Website>(w),
  Positions = GetPositionsOfPerson(c.ContactId)
 };

 private IQueryable<Position> GetPositionsOfPerson(int id)
 {
  return from p in context.crm_Positions
    join d in context.lu_PositionTypes on p.PositionTypeKey equals d.Value
    where p.PersonKey == id &&
    d.Language == Global.Culture.TwoLetterISOLanguageName
    select new Position(d.Value, d.Name, d.Language);
 }