开发者

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?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜