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);
}