Linq to Entities paging on flat hierarchy
I have a schema that is somewhat denormalised. consider the following tables:
Address
Contact
Company
ContactAddress (ContactId, AddressId)
Director (CompanyId, ContactAddressId)
If a Contact has more than one address, and they are a Director of a company, they will have two rows in the Director table (one for each address - potentially but not always).
I need to return a list of Contacts that are Directors. Each Contact has a collection of ContactAddresses for this Company.
The problem is there's 100ks rows (so I'm not keen on loading everything from the db), but paging is required i.e. on the distinct directors (not their addresses). Each Director in the grid will have sub-rows one for each address.
e.g.
Bob Smith
Acme Company, 1 The Street, London
Acme Company, 3 The Terrace, Scarborough
Julie Hurts
Bobbies Bits, 5 Somewhere Land
Sarah Saysno
BikesRUs, 99 Nowhere land, Nowhere
Struggling to see how I could do that with pure Linq to Entities.
Anyone?
Edit In domain language terms - a contact can have more than one address. a contact can be a director of a company at more than one of his addresses. a company can have multiple directors at multiple addresses
Show all contacts at a company grouped by the contacts address where they are a director at the company for that address.
Closest Ive got is a two query pass:
var directors = (
from companyDirector in ctx.CompanyDirectors
join contactAddress in ctx.ContactAddresses
on companyDirector.ContactAddress equals contactAddress
join contact in ctx.Contacts
on contactAddress.Contact equals contact
开发者_JAVA技巧 where contact.DisplayName.Contains(searchText)
&& companyDirector.TypeId == CompanyDirector.DirectorTypeId
orderby contact.DisplayName
group companyDirector by new {companyDirector.Company, companyDirector.ContactAddress.Contact}
into companyContacts
select companyContacts
).Page(pageNumber, pageSize).ToList();
var query = (
from director in directors
select new CompanyDirectorLocations
{
CompanyId = director.Key.Company.Id,
ContactDisplayName = director.Key.Contact.DisplayName,
Locations = (
from companyDirector in ctx.CompanyDirectors
where companyDirector.Company == director.Key.Company
&& companyDirector.ContactAddress.Contact == director.Key.Contact
&& companyDirector.TypeId == CompanyDirector.DirectorTypeId
select companyDirector.ContactAddress.Address.City
).ToList()
}
);
This "just works." If you do:
var q = (from c in Context.Contacts
where c.Director != null
select new ContactPresentation
{
Name = c.Name,
Addresses = from a in c.Addresses
select new AddressPresentation
{
Company = a.Company,
// etc.
}).Take(3);
...then you get the top 3 contacts, not the top 3 addresses.
Have you tried it?
精彩评论