Background info: Version: 2.6 Final (June 6th, 2008 ) Runtime: SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll: 2.6.08.0612 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll: 2.6.08.0709 Template: Adapter, General .Net Framework: 3.5 DB: SQL Server 2005

I'm trying to filter on a subtype in a TargetPerEntity model such that I only get certain child entities back (ClientPortfolio entities).

The basic hierarchy is: Portfolio: parent ClientPortfolio: child of portfolio

I've tried using the GetEntityTypeFilter, but that fails with this exception: The multi-part identifier "dbo.client_portfolio.portfolio_id" could not be bound. Looking at the generated SQL, the child table is not in the FROM statement but is referenced in the WHERE clause.

Is the GetEntityTypeFilter the correct way to filter on a sub-type?

Here's the C# code:

EntityCollection<AccountPortfolioEntity> clientAccounts = new EntityCollection<AccountPortfolioEntity>();
IRelationPredicateBucket bucket = new RelationPredicateBucket();                    
bucket.Relations.Add(AccountPortfolioEntity.Relations.PortfolioEntityUsingPortfolioId);                 
bucket.PredicateExpression.Add(ClientPortfolioEntity.GetEntityTypeFilter());

using (IDataAccessAdapter a = new DataAccessAdapter(false))
{
    a.FetchEntityCollection(clientAccounts, bucket);
}

And the SQL code:

SELECT DISTINCT [dbo].[account].[account_id] AS [F1_0], [dbo].[account].[account_type_id] AS [F1_1], [dbo].[account].[name] AS [F1_2], [dbo].[account].[active] AS [F1_3], [dbo].[account].[paan] AS [F1_4], [dbo].[account].[monthly_private_equity_draw_estimate] AS [F1_5], [dbo].[account].[monthly_private_equity_draw_day_of_month] AS [F1_6], [dbo].[account].[monthly_spending_estimate] AS [F1_7], [dbo].[account].[monthly_spending_day_of_month] AS [F1_8], [dbo].[account].[create_date] AS [F1_9], [dbo].[account].[create_user_id] AS [F1_10], [dbo].[account].[update_date] AS [F1_11], [dbo].[account].[update_user_id] AS [F1_12], [dbo].[account_portfolio].[account_id] AS [F3_13], [dbo].[account_portfolio].[portfolio_id] AS [F3_14], [dbo].[account_portfolio].[is_primary_cash_account] AS [F3_15] FROM (( [dbo].[account]  INNER JOIN [dbo].[account_portfolio]  ON  [dbo].[account].[account_id]=[dbo].[account_portfolio].[account_id]) INNER JOIN [dbo].[portfolio]  ON  [dbo].[portfolio].[portfolio_id]=[dbo].[account_portfolio].[portfolio_id]) WHERE ( ( ( [dbo].[client_portfolio].[portfolio_id] IS NOT NULL)) AND ( [dbo].[account_portfolio].[account_id] IS NOT NULL))

Thanks!

bucket.Relations.Add(AccountPortfolioEntity.Relations.PortfolioEntityUsingPortfolioId);

Mmm.. I wonder why did you add such relation. I think it's unnecessary. Please remove it and try again.

BTW, GetEntityTypeFilter method construct the filter with the necessary relations so you don't need to add the relation involved in the hierarchy.

daelmo wrote:

bucket.Relations.Add(AccountPortfolioEntity.Relations.PortfolioEntityUsingPortfolioId);

Mmm.. I wonder why did you add such relation. I think it's unnecessary. Please remove it and try again.

Sorry, I should have explained why that was there.

I'm fetching a collection of AccountPortfolio Entities. AccountPortfolio has a relationship to Portfolio. For this fetch, I only want AccountPortofolio Entities where there is corresponding Portfolio record/object of type ClientPortfolio (the child of Portfolio).

Does that clarify things?

Thanks.

I think you'd better do the following (more simple):

SELECT * FROM Account_Portfolio
WHERE Portfolio_Id IN (SELECT Portfolio_Id FROM Client_Portfolio)

Which can be implemented using a FieldCompareSetPredicate.

So, just to be clear, the GetEntityTypeFilter doesn't work in this case, right? Is that something that could be resolved in a future release? It would be a handy function.

Here's what I ended up doing, in case it helps others:

// the collection is already fetched with all records; now filter just for the subtype we need
var ivAcountsOnly = from ca in myEntityCollection
                    where (ca.Portfolio is PoolVehiclePortfolioEntity)
                    select ca;

return new EntityCollection<AccountPortfolioEntity>(ivAcountsOnly.ToList<AccountPortfolioEntity>());

So, just to be clear, the GetEntityTypeFilter doesn't work in this case, right?

Well I think you should try the following:

EntityCollection<AccountPortfolioEntity> clientAccounts = new EntityCollection<AccountPortfolioEntity>(); IRelationPredicateBucket bucket = new RelationPredicateBucket(); bucket.Relations.Add(AccountPortfolioEntity.Relations.PortfolioEntityUsingPortfolioId); bucket.Relations.Add(PortfolioEntity.Relations.ClientPortfolioEntity); bucket.PredicateExpression.Add(ClientPortfolioEntity.GetEntityTypeFilter());

using (IDataAccessAdapter a = new DataAccessAdapter(false)) { a.FetchEntityCollection(clientAccounts, bucket); }

Hey Walaa --

Walaa wrote:

Well I think you should try the following:

bucket.Relations.Add(PortfolioEntity.Relations.ClientPortfolioEntity);

That generated relationship doesn't exist, I believe because ClientPortfolio is a child of Portfolio.

(The database obviously has the FK between ClientPortfolio and Portfolio, but LLBLGen doesn't give me the option to use it.)

Thanks.

You can use your own custom relation:

bucket.Relations.Add(new EntityRelation(PortfolioFields.PortfolioId, ClientPortfolioFields.PortfolioId, RelationType.OneToOne));

Walaa wrote:

You can use your own custom relation:

bucket.Relations.Add(new EntityRelation(PortfolioFields.PortfolioId, ClientPortfolioFields.PortfolioId, RelationType.OneToOne));

Thanks, I'll give that a shot.