LLBLGen Pro Build: 2.0.0.0 Final (August 3rd, 2006) Runtime Version: 2.0.0.060808 Template Group/.NET Version: Adapter, 1 class, .NET 2.0 Database: SQL Server 2005

I have a question regarding a many to many prefetch scenario. I have a ServiceProviderEntity and a ServiceItemEntity, with a many-to-many relationship between the two, a ServiceProviderServiceItemEntity. My ServiceProviderServiceItemEntity has an IsActive boolean on it.

When I load a ServiceItemEntity for editing, I need to pre-fetch the following:

• ServiceProviderServiceItemEntity Collection (Active and Inactive)
• ServiceProviderEntity Collection (where ServiceProviderServiceItem.IsActive == true)

I have the following code:

ServiceItemEntity serviceItem = new ServiceItemEntity(serviceItemID);

IPrefetchPath2 itemPath = new PrefetchPath2((int)Data.Model.EntityType.ServiceItemEntity);
itemPath.Add(ServiceItemEntity.PrefetchPathServiceProviderServiceItem);

IPrefetchPathElement2 providerPath = itemPath.Add(ServiceItemEntity.PrefetchPathServiceProviderCollectionViaServiceProviderServiceItem);
providerPath.Filter.AddWithAnd(ServiceProviderServiceItemFields.IsActive == true);
providerPath.FilterRelations.Add(ServiceProviderEntity.Relations.ServiceProviderServiceItemEntityUsingServiceProviderID);

this.Adapter.FetchEntity(serviceItem, itemPath);

This works like a charm, but there's something about it that I don't like, which is adding to the FilterRelations. This is resulting in the ServiceProviderServiceItem table being joined in the SQL twice.

Here's the SQL that was executed (cleaned up for clarity):

SELECT DISTINCT
        [MyDatabase].[dbo].[ServiceProvider].[ServiceProviderID]
--      All other ServiceProvider fields
,      [MyDatabase].[dbo].[ServiceProvider].[IsActive]
FROM    (
          (
            (
              [MyDatabase].[dbo].[ServiceItem] [LPA__1] 
              INNER JOIN [MyDatabase].[dbo].[ServiceProviderServiceItem] [LPA_S2]
              ON [LPA__1].[ServiceItemID] = [LPA_S2].[ServiceItemID]
            ) 
            INNER JOIN [MyDatabase].[dbo].[ServiceProvider]
            ON [MyDatabase].[dbo].[ServiceProvider].[ServiceProviderID] = [LPA_S2].[ServiceProviderID]
          )
          INNER JOIN [MyDatabase].[dbo].[ServiceProviderServiceItem]
          ON [MyDatabase].[dbo].[ServiceProvider].[ServiceProviderID] = [MyDatabase].[dbo].[ServiceProviderServiceItem].[ServiceProviderID]
        )
WHERE   (
          ( (( [LPA_S2].[ServiceItemID] = @ServiceItemID1))
          AND ([MyDatabase].[dbo].[ServiceProviderServiceItem].[IsActive] = @IsActive2))
        )

Without adding the FilterRelation, I get the following exception:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled Message="An exception was caught during the execution of a retrieval query: The multi-part identifier \"MyDatabase.dbo.ServiceProviderServiceItem.IsActive\" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

The query that was executed is:

SELECT DISTINCT
        [MyDatabase].[dbo].[ServiceProvider].[ServiceProviderID]
--      All other ServiceProvider fields
,      [MyDatabase].[dbo].[ServiceProvider].[IsActive]
FROM    (
          (
            [MyDatabase].[dbo].[ServiceItem] [LPA__1] 
            INNER JOIN [MyDatabase].[dbo].[ServiceProviderServiceItem] [LPA_S2]
            ON [LPA__1].[ServiceItemID] = [LPA_S2].[ServiceItemID]
          )
          INNER JOIN [MyDatabase].[dbo].[ServiceProvider]
          ON [MyDatabase].[dbo].[ServiceProvider].[ServiceProviderID] = [LPA_S2].[ServiceProviderID]
        )
WHERE   (
          ( (( [LPA_S2].[ServiceItemID] = @ServiceItemID1))
          AND ([MyDatabase].[dbo].[ServiceProviderServiceItem].[IsActive] = @IsActive2))
        )

As you can see, the ServiceProvider.ServiceItem.IsActive filter fails, because ServiceProviderServiceItem is aliased when it's joined.

I'm still very, very new to LLBLGen, so I thought I should post this up here to see if I'm on the right track with adding the FilterRelation, or if there's a more efficient way of pulling this off.

Thanks, Jeff

You should SubPath the ServiceProvider to the prefetched ServiceProviderServiceItem, with the appropriate filters and relations

The code should look like the following:

ServiceItemEntity serviceItem = new ServiceItemEntity(serviceItemID);

PrefetchPath2 itemPath = new PrefetchPath2((int)Data.Model.EntityType.ServiceItemEntity);
itemPath.Add(ServiceItemEntity.PrefetchPathServiceProviderServiceItem).SubPath.Add(v ServiceProviderServiceItemEntity.PrefetchPathServiceProvider, 0, (ServiceProviderServiceItemFields.IsActive == true), ServiceProviderEntity.Relations.ServiceProviderServiceItemEntityUsingServiceProviderID);

this.Adapter.FetchEntity(serviceItem, itemPath);

Using that code, I am getting a build error:

The best overloaded method match for 'SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPath2.Add(SD.LLBLGen.Pro.ORMSupportClasses.IPrefetchPathElement2, int, SD.LLBLGen.Pro.ORMSupportClasses.IPredicateExpression, SD.LLBLGen.Pro.ORMSupportClasses.IEntityFactory2)' has some invalid arguments

It appears that it wants a RelationCollection where I'm passing in ServiceProviderEntity.Relations.ServiceProviderServiceItemEntityUsingServiceProviderID.

I have altered the code to be:

IPrefetchPath2 providerPath = itemPath.Add(ServiceItemEntity.PrefetchPathServiceProviderServiceItem).SubPath;

IPredicateExpression activeProviders = new PredicateExpression(ServiceProviderServiceItemFields.IsActive == true);
IRelationCollection providerRelations = new RelationCollection();
providerRelations.Add(ServiceProviderEntity.Relations.ServiceProviderServiceItemEntityUsingServiceProviderID);
providerPath.Add(ServiceProviderServiceItemEntity.PrefetchPathServiceProvider, 0, activeProviders, providerRelations);

The query behind this seems to be much better, but it results in an undesired effect. I cannot get to the collection of ServiceProviders by using serviceItem.ServiceProviderCollectionViaServiceProviderServiceItem, which I was able to do before.

Is there a way to marry the two approaches so that I can get the more effecient query, while still getting a collection of service providers that can be accessed?

Thanks again, Jeff

If you want to filter on a field in the intermediate entity (A - AB - B, so AB is the intermediate entity) when fetching the m:n related entities, use the alias '_entityname_'. So in your case, use: "ServiceProviderServiceItem"

This then makes sure the filter fields get aliased properly.

Can someone help me with what that code would look like for putting the alias on the filter? I tried the following:

IPrefetchPathElement2 providerPath = itemPath.Add(ServiceItemEntity.PrefetchPathServiceProviderCollectionViaServiceProviderServiceItem);
providerPath.Filter.AddWithAnd(new FieldCompareValuePredicate(ServiceProviderServiceItemFields.IsActive, ComparisonOperator.Equal, true, "ServiceProviderServiceItem_"));

But I get the following build errors:

Error 1 The best overloaded method match for 'SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareValuePredicate.FieldCompareValuePredicate( SD.LLBLGen.Pro.ORMSupportClasses.IEntityFieldCore, SD.LLBLGen.Pro.ORMSupportClasses.IFieldPersistenceInfo, SD.LLBLGen.Pro.ORMSupportClasses.ComparisonOperator, object)' has some invalid arguments

Error 2 Argument '2': cannot convert from 'SD.LLBLGen.Pro.ORMSupportClasses.ComparisonOperator' to 'SD.LLBLGen.Pro.ORMSupportClasses.IFieldPersistenceInfo'

Error 3 Argument '3': cannot convert from 'bool' to 'SD.LLBLGen.Pro.ORMSupportClasses.ComparisonOperator'

Thanks, Jeff

I think I got it...

IPrefetchPathElement2 providerPath = itemPath.Add(ServiceItemEntity.PrefetchPathServiceProviderCollectionViaServiceProviderServiceItem);
providerPath.Filter.AddWithAnd(ServiceProviderServiceItemFields.IsActive.SetObjectAlias("ServiceProviderServiceItem_") == true);

This seems to work. Is there any way to avoid hard-coding the "ServiceProviderServiceItem_" string?

Yep, found it:

IPrefetchPathElement2 providerPath = itemPath.Add(ServiceItemEntity.PrefetchPathServiceProviderCollectionViaServiceProviderServiceItem);
providerPath.Filter.AddWithAnd(ServiceProviderServiceItemFields.IsActive.SetObjectAlias( ServiceItemEntity.PrefetchPathServiceProviderCollectionViaServiceProviderServiceItem.Relation.AliasFKSide) == true);

That's not so bad. And it results in the following query:

SELECT DISTINCT
        [MyDatabase].[dbo].[ServiceProvider].[ServiceProviderID]
--      Other Service Provider fields
,      [MyDatabase].[dbo].[ServiceProvider].[IsActive]
FROM    (
          (
            [MyDatabase].[dbo].[ServiceItem] [LPA__1] 
            INNER JOIN [MyDatabase].[dbo].[ServiceProviderServiceItem] [LPA_S2]
            ON [LPA__1].[ServiceItemID] = [LPA_S2].[ServiceItemID]
          )
          INNER JOIN [MyDatabase].[dbo].[ServiceProvider]
          ON [MyDatabase].[dbo].[ServiceProvider].[ServiceProviderID] = [LPA_S2].[ServiceProviderID]
        )
WHERE   (
          ( (( [LPA_S2].[ServiceItemID] = @ServiceItemID1))
          AND ([LPA_S2].[IsActive] = @IsActive2))
        )

(thanks to RedGate SQL Refactor, the SQL looks pretty)

Thanks for all of the help on this front!

Even better:

IPrefetchPathElement2 providerPath = itemPath.Add(ServiceItemEntity.PrefetchPathServiceProviderCollectionViaServiceProviderServiceItem);
providerPath.Filter.AddWithAnd(ServiceProviderServiceItemFields.IsActive.SetObjectAlias(providerPath.Relation.AliasFKSide) == true);

Cool stuff!