Hi,

I'm sure I'm missing a trick here but I can't see the proper way to do this. Any pointers would be most appreciated.

I have a Campaigns table which contains a SectionId and a PartnerId and I want to include SectionName and PartnerName in the Campaign Entity.

I have set this up in the designer using Fields on Related Fields but am unsure how to efficiently retrieve this.

Here is my current code:

                    EntityCollection<CampaignEntity> campaigns = new EntityCollection<CampaignEntity>();

                    // Sort by earliest campaign first
                    SortExpression sorter = new SortExpression(CampaignFields.StartDate | SortOperator.Ascending);
                    
                    // Get only active campaigns
                    IRelationPredicateBucket bucket = new RelationPredicateBucket();
                    bucket.PredicateExpression.Add(CampaignFields.CampaignActive == true);
                    bucket.PredicateExpression.AddWithAnd(CampaignFields.StartDate <= DateTime.Today);
                    bucket.PredicateExpression.AddWithAnd(CampaignFields.EndDate >= DateTime.Today);

                    IPrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.CampaignEntity);
                    // Get the CampaignSection details as well
                    bucket.Relations.Add(CampaignEntity.Relations.CampaignSectionEntityUsingCampaignSectionId);
                    prefetch.Add(CampaignEntity.PrefetchPathCampaignSection);

                    // Get the Partner Details as well
                    bucket.Relations.Add(CampaignEntity.Relations.PartnerEntityUsingPartnerId);
                    prefetch.Add(CampaignEntity.PrefetchPathPartner);
                    
                    // Retrieve and cache
                    adapter.FetchEntityCollection(campaigns, bucket, 10000, sorter, prefetch);

... however, this produces three SQL queries as follows:

SELECT DISTINCT TOP 10000 
[ReHome].[dbo].[Campaign].[CampaignId], 
[ReHome].[dbo].[Campaign].[PartnerId], 
[ReHome].[dbo].[Campaign].[CampaignName], 
[ReHome].[dbo].[Campaign].[CampaignSectionId], 
[ReHome].[dbo].[Campaign].[CampaignCode], 
[ReHome].[dbo].[Campaign].[CampaignType], 
[ReHome].[dbo].[Campaign].[StartDate], 
[ReHome].[dbo].[Campaign].[EndDate], 
[ReHome].[dbo].[Campaign].[MaxCost], 
[ReHome].[dbo].[Campaign].[CostPerClick], 
[ReHome].[dbo].[Campaign].[IsPremium], 
[ReHome].[dbo].[Campaign].[CampaignActive], 
[ReHome].[dbo].[Campaign].[AdvertImageURL] AS [AdvertImageUrl], 
[ReHome].[dbo].[Campaign].[SmallAdvertImageURL] AS [SmallAdvertImageUrl], 
[ReHome].[dbo].[Campaign].[BannerImageURL] AS [BannerImageUrl], 
[ReHome].[dbo].[Campaign].[OfferPageImageURL] AS [OfferPageImageUrl], 
[ReHome].[dbo].[Campaign].[RevealImageURL] AS [RevealImageUrl], 
[ReHome].[dbo].[Campaign].[RevealURL] AS [RevealUrl], 
[ReHome].[dbo].[Campaign].[PartnerTargetEmail], 
[ReHome].[dbo].[Campaign].[VoucherText], 
[ReHome].[dbo].[Campaign].[StaticCode], 
[ReHome].[dbo].[Campaign].[SequentialCodeStart], 
[ReHome].[dbo].[Campaign].[HasBanner] 
FROM (( [ReHome].[dbo].[CampaignSection]  
INNER JOIN [ReHome].[dbo].[Campaign]  ON  
[ReHome].[dbo].[CampaignSection].[CampaignSectionId]=[ReHome].[dbo].[Campaign].[CampaignSectionId]) 
INNER JOIN [ReHome].[dbo].[Partner]  ON  
[ReHome].[dbo].[Partner].[PartnerId]=[ReHome].[dbo].[Campaign].[PartnerId]) 
WHERE ( ( 
[ReHome].[dbo].[Campaign].[CampaignActive] = @CampaignActive1 AND 
[ReHome].[dbo].[Campaign].[StartDate] <= @StartDate2 AND 
[ReHome].[dbo].[Campaign].[EndDate] >= @EndDate3)) 
ORDER BY 
[ReHome].[dbo].[Campaign].[StartDate] ASC

SELECT [ReHome].[dbo].[CampaignSection].[CampaignSectionId], [ReHome].[dbo].[CampaignSection].[CampaignSectionName] FROM [ReHome].[dbo].[CampaignSection]  WHERE ( [ReHome].[dbo].[CampaignSection].[CampaignSectionId] IN (@CampaignSectionId1, @CampaignSectionId2, @CampaignSectionId3, @CampaignSectionId4))

SELECT [ReHome].[dbo].[Partner].[PartnerId], [ReHome].[dbo].[Partner].[UserId], [ReHome].[dbo].[Partner].[Balance], [ReHome].[dbo].[Partner].[LastUpdated], [ReHome].[dbo].[Partner].[PartnerName] FROM [ReHome].[dbo].[Partner]  WHERE ( [ReHome].[dbo].[Partner].[PartnerId] IN (@PartnerId1, @PartnerId2, @PartnerId3, @PartnerId4, @PartnerId5, @PartnerId6, @PartnerId7, @PartnerId8))

Since the first query has a JOIN to the two required tables, surely there is a way of including the required fields in the SELECT list and avoiding the two further prefetch queries?

I'm sure there is a way, but I just can't see it....

Thanks in advance Chris

Hi,

You have included the 2 fields in the entity, and you also fetch the related tables with adding related path in the prefetchpath. It is 2 different things, and LLBL can't merge it.

I suggest you to create a view based on Campaigns table with SectionName and PartnerName and use it only when you only need these fields.