Hello i have read a lot about this error in this forum but i can not use to answers to get a solution.

The database i use is from a customer with strange m:n relations in it. This is the first time for me i have to deal with a n:m relationship.

llblgen version = 2.5 final

the sql(2005 express)server script i use: see attachement

The code i use:

    private static void ShowMailPiecesJob(XmlNodeList aMailPieceList, decimal aIdentityCommon) {

        JobsEntity job = new JobsEntity();
        IPredicateExpression filter = new PredicateExpression().Add(JobsFields.IdentityCommon.SetObjectAlias("JobsIdentityCommon") == aIdentityCommon);
        MailPiecesCollection mailPieces = job.GetMultiMailPieces(true, filter);

        foreach(MailPiecesEntity mailPiece in mailPieces){
            Console.Write(mailPiece.JobsId.ToString());
        }


    }

The error i get: 31-1-2008 9:55:40: SD.LLBLGen.Pro.ORMSupportClasses.NET20

An exception was caught during the execution of a retrieval query: The multi-part identifier "aIdentityCommon.IdentityCommon" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

An exception was caught during the execution of a retrieval query: The multi-part identifier "aIdentityCommon.IdentityCommon" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery, IFieldPersistenceInfo[] fieldPersistenceInfos) at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at StaterDal.DaoClasses.MailPieceDAO.GetMulti(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IPredicateExpression filter, IEntity jobInstance, Int32 pageNumber, Int32 pageSize) in C:\Stater\DAL\DaoClasses\MailPieceDAO.cs:line 77 at StaterDal.CollectionClasses.MailPieceCollection.GetMultiManyToOne(IEntity jobInstance, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicateExpression filter, Int32 pageNumber, Int32 pageSize) in C:\Stater\DAL\CollectionClasses\MailPieceCollection.cs:line 115 at StaterDal.CollectionClasses.MailPieceCollection.GetMultiManyToOne(IEntity jobInstance, IPredicateExpression filter) in C:\Stater\DAL\CollectionClasses\MailPieceCollection.cs:line 78 at StaterDal.EntityClasses.JobEntity.GetMultiMailPiece(Boolean forceFetch, IEntityFactory entityFactoryToUse, IPredicateExpression filter) in C:\Stater\DAL\EntityClasses\JobEntity.cs:line 446 at StaterDal.EntityClasses.JobEntity.GetMultiMailPiece(Boolean forceFetch, IPredicateExpression filter) in C:\Stater\DAL\EntityClasses\JobEntity.cs:line 413 at Stater.Program.SaveJobsMailPieces(XmlNodeList aMailPieceList, Decimal aIdentityCommon) in C:\Stater\Stater\Program.cs:line 52 at Stater.Program.Main(String[] args) in C:\Stater\Stater\Program.cs:line 41

I try to get the mailpieces with a give identityCommon. I generated SelfServing classes.

Probably i make a beginner mistake i hope you know which one.

Thanx for reading this.

Jos

Why do you use an alias in the predicate? (SetObjectAlias("JobsIdentityCommon"))

Try without it.

Thank you for the quick response. I already did tha with this code. then I had the same error. I visited this forem and tried it the why i send you earlier. The result is the same.

private static void SaveJobsMailPieces(XmlNodeList aMailPieceList, decimal aIdentityCommon) {

        JobsEntity job = new JobsEntity();
        IPredicateExpression filter = new PredicateExpression();
        filter.Add(JobsFields.IdentityCommon == aIdentityCommon);

        MailPiecesCollection mailPieces = job.GetMultiMailPieces(true, filter);

        foreach(MailPiecesEntity mailPiece in mailPieces){
            Console.Write(mailPiece.JobsId.ToString());
        }


    }

}

Jos

I have the same error. So I'm also waiting for a solution or hint...

Kind regards TomV

I simplified the database by making most fields nullable. I added a Rowguid.

See the document for the relations. See the sql for the new script.

Jos

I simplified the database by making most fields nullable. I added a Rowguid.

See the document for the relations. See the sql for the new script.

Jos

I simplified the database by making most fields nullable. I added a Rowguid.

See the document for the relations. See the sql for the new script.

Jos

Sorry firewall problems here.

private static void SaveJobsMailPieces(XmlNodeList aMailPieceList, decimal aIdentityCommon) {

        JobsEntity job = new JobsEntity();
        IPredicateExpression filter = new PredicateExpression();
        filter.Add(JobsFields.IdentityCommon == aIdentityCommon);

        MailPiecesCollection mailPieces = job.GetMultiMailPieces(true, filter);

        foreach(MailPiecesEntity mailPiece in mailPieces){
            Console.Write(mailPiece.JobsId.ToString());
        }           
    }

}

When you call: MailPiecesCollection mailPieces = job.GetMultiMailPieces(true, filter); Automatically a filter is used to filter the MailPieces returned by comparing the MailPieces.JobsId (FK) to the value of the PK of the job entity.

And that's it. Now you want to add another filter to the GetMulti of the MailPieces but this filter is referencing another table, which by the way is the JobsEntity itself. But then you will need to pass the relation (JOIN) to let the GetMulti filter upon another table.

And that's not available in the builtIn job.GetMultiMailPieces() overloads.

Also note that you are using a new Jobs Entity, which doesn't have a PK set.

Anyway what you can do is the following:

private static void SaveJobsMailPieces(XmlNodeList aMailPieceList, decimal aIdentityCommon) {

            IPredicateExpression filter = new PredicateExpression();
            filter.Add(JobsFields.IdentityCommon == aIdentityCommon);

            RelationCollection relations = new RelationCollection();
            relations.Add(MailPiecesEntity.Relations.JobEntity...);

            MailPiecesCollection mailPieces = new MailPiecesCollection();
            mailPiece.GetMulti(filter, relations);

            // or use the following if you want to fetch the collection into the job.MailPieces
            JobsEntity job = new JobsEntity(); 
            job.mailPiece.GetMulti(filter, relations);

            foreach(MailPiecesEntity mailPiece in mailPieces){
                Console.Write(mailPiece.JobsId.ToString());
            }           
        }
}

Thanx! This works.

But now i know that i have to read all the documentation about n:m relations. Because 1:n relations are covered very well in documentation and examples but the examples about n:m relations editing, updating and deleting are like a 'spelt in een hooiberg' this is a dutch expression for you have to look for them very well and if you're lucky maybe you find one.

Greetings, Jos

"needle in a haystack "

M:N relations are a view on 2 1:n relations, so it's actually not that hard, though it can be confusing IF you see an m:n relation as a relation where just 2 entities are involved, not 3. You then won't update the intermediate entity and things will go wrong.

Hoi Otis,

Thanx for trying to simplify the n:m relationship

But for me, i'm used to Entity.GetMulti().... this is still

And if you look at the Database Relations i don't even know where to begin saving a record and so working my way around. But you gave a little hint of were the needle may be

Greetings, Jos

My problem is solved, but now I have another one, I assume it doesn't belong here, but I search on the forum and didn't found anything...

My code:

        EntityCollection<TranslationEntity> Translations = new EntityCollection<TranslationEntity>();

        IPrefetchPath2 path = new PrefetchPath2(EntityType.TranslationEntity);
        path.Add(TranslationEntity.PrefetchPathTranslationValues);

        RelationPredicateBucket bucket = new RelationPredicateBucket();
        bucket.Relations.Add  (TranslationEntity.Relations.TranslationValueEntityUsingTranslationId);
        bucket.PredicateExpression.Add(TranslationValueFields.LanguageId == 1);

        using (IDataAccessAdapter adapter = DataAccessFactory.GetAdapter())
        {
           adapter.FetchEntityCollection(Translations, bucket);
        }

My problem:

per TranslationEntity I have 5 related TranslationValueEntities. But when I know the language of the user, I only want 1 language/corresponding record to be retrieved... But when I use above code, all 5 related records are retrieved... ==> Translations[0].TranslationValues.Count = 5

I don't see what I do wrong. Can you help me please?

Kind regards TomV

First of all, you are not using the PrefetchPath in the fetch method.

And then when you do, if you want to filter the prefetched related entities, then use the filter on the preftechPath itself not in the main fetch method.

            EntityCollection<TranslationEntity> Translations = new EntityCollection<TranslationEntity>();

            PredicateExpression prefetchFilter = new PredicateExpression(TranslationValueFields.LanguageId == 1);

            IPrefetchPath2 path = new PrefetchPath2(EntityType.TranslationEntity);
            path.Add(TranslationEntity.PrefetchPathTranslationValues, 0, prefetchFilter);

            using (IDataAccessAdapter adapter = DataAccessFactory.GetAdapter())
            {
             adapter.FetchEntityCollection(Translations, null, path);
            }

Thanks!! This solved my problem! Now when I see your solution it's quite obvious to use the filter in the PrefetchPath, but as always, you have to know...

I was mislead by the example in the user manual (page 257 - 258: Advanced Prefetch Paths).

btw, not using the prefetch path was a copy - paste error

Further I just want to say that LLBLGenPro really rocks! I have my license since this week and I already love it!

Kind regards TomV

Thanks for the positive feedback, and I hope you always stay glad with LLBLGen Pro