Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?
 

Pages: 1
LLBLGen Pro Runtime Framework
Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?
Page:1/1 

  Print all messages in this thread  
Poster Message
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 27-Feb-2008 02:43:49.  
The LLBLGen version I am running is 2.5 Final December 5th. DLL version 2.5.07.1214.

I am getting an exception when using a precreated relationship from the generated code. The relationship looks fine so i don't know why the exception is being thrown. The code is below.

The GetRelationInfoContactCollectionViaSupportGroupMember is generated by the designer and I have not made any changes to the generated classes.
Code:

using (IDataAccessAdapter adapter = BLServices.Adapters.InfrastructureCentral)
            {
                adapter.FetchEntityCollection(this._supportGroupMembers, this._supportGroup.GetRelationInfoContactCollectionViaSupportGroupMember());
            }

        public virtual IRelationPredicateBucket GetRelationInfoContactCollectionViaSupportGroupMember()
        {
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(SupportGroupEntity.Relations.SupportGroupMemberEntityUsingSupportGroupGuid, "__SupportGroupEntity__", "SupportGroupMember_", JoinHint.None);
            bucket.Relations.Add(SupportGroupMemberEntity.Relations.ContactEntityUsingMemberContactGuid, "SupportGroupMember_", string.Empty, JoinHint.None);
            bucket.PredicateExpression.Add(new FieldCompareValuePredicate(SupportGroupFields.ContactGuid, null, ComparisonOperator.Equal, this.ContactGuid));
            return bucket;
        }



        public virtual IEntityRelation SupportGroupMemberEntityUsingSupportGroupGuid
        {
            get
            {
                IEntityRelation relation = new EntityRelation(SD.LLBLGen.Pro.ORMSupportClasses.RelationType.OneToMany, "SupportGroupMember" , true);
                relation.AddEntityFieldPair(SupportGroupFields.ContactGuid, SupportGroupMemberFields.SupportGroupGuid);
                relation.InheritanceInfoPkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("SupportGroupEntity", true);
                relation.InheritanceInfoFkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("SupportGroupMemberEntity", false);
                return relation;
            }
        }


        public virtual IEntityRelation ContactEntityUsingMemberContactGuid
        {
            get
            {
                IEntityRelation relation = new EntityRelation(SD.LLBLGen.Pro.ORMSupportClasses.RelationType.ManyToOne, "Contact", false);
                relation.AddEntityFieldPair(ContactFields.ContactGuid, SupportGroupMemberFields.MemberContactGuid);
                relation.InheritanceInfoPkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("ContactEntity", false);
                relation.InheritanceInfoFkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("SupportGroupMemberEntity", true);
                return relation;
            }
        }



Any clues. Thanks for your help
Brandt  Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 27-Feb-2008 03:46:44.  
It turns out that quite a bit of stuff isn't working as it should. I have a contact hierarchy defined in llblgen as one target per entity type. The relevant tables in the hierarchy are the Contact and the SupportGroup where a SupportGroup is defined through inheritance as a Contact. The SupportGroup table has a relationship with another table called SupportGroupMember which has another relationship to the Contact table. The dataflow is like this; A SupportGroup is a Contact, which has SupportGroupMembers who also happen to be contacts. Unfortunantely the m:n relations between the Contact, SupportGroup and SupportGroupMembers tables are failing. Ex. When I query a Contact for which SupportGroups he belongs to using _contact.GetRelationInfoSupportGroupCollectionViaSupportGroupMember() I end up getting the original contact back as a SupportGroupEntity.

The generated query
Code:

exec sp_executesql N'SELECT DISTINCT [LPA__1].[ContactGuid] AS [F0], [LPA__1].[Name] AS [F1], [LPA__1].[Email] AS [F2], [LPA__1].[WebPage] AS [F3], [LPA__1].[Deleted] AS [F4], [LPA__1].[UpdatedOn] AS [F5], [ICDEV].[PointOfContact].[SupportGroup].[ContactGuid] AS [F6]
FROM (( [ICDEV].[PointOfContact].[Contact] [LPA__1]
INNER JOIN [ICDEV].[Support].[SupportGroupMember] [LPA_S2] ON [LPA__1].[ContactGuid]=[LPA_S2].[ContactGuid])
INNER JOIN [ICDEV].[PointOfContact].[SupportGroup] ON [ICDEV].[PointOfContact].[SupportGroup].[ContactGuid]=[LPA_S2].[SupportGroupGuid])
WHERE ( ( [LPA_S2].[ContactGuid] = @ContactGuid1) AND ( [ICDEV].[PointOfContact].[SupportGroup].[ContactGuid] IS NOT NULL))',N'@ContactGuid1 uniqueidentifier',@ContactGuid1='723DA26F-6BD6-DC11-AA20-001641982829'




The correct query

Quote:

select CSG.Name from [ICDEV].[PointOfContact].[Contact] C
INNER JOIN [ICDEV].[Support].[SupportGroupMember] SGM ON SGM.[ContactGuid]= C.[ContactGuid]
INNER JOIN [ICDEV].[PointOfContact].[SupportGroup] SG ON SG.[ContactGuid] = SGM.[SupportGroupGuid]
INNER JOIN [ICDEV].[PointOfContact].[Contact] CSG ON CSG.[ContactGuid] = SG.[ContactGuid]
WHERE C.[ContactGuid] = '723DA26F-6BD6-DC11-AA20-001641982829'


Some how the inheritance is getting lost in the translation. I have attached a zip with images of the table structure and also the llblgen designer hierarchy.

Please advise

Thanks Brandt


Brandt  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14641 posts
# Posted on: 27-Feb-2008 09:55:25.  
Quote:
using (IDataAccessAdapter adapter = BLServices.Adapters.InfrastructureCentral)
            {
                adapter.FetchEntityCollection(this._supportGroupMembers, this._supportGroup.GetRelationInfoContactCollectionViaSupportGroupMember());
            }

        public virtual IRelationPredicateBucket GetRelationInfoContactCollectionViaSupportGroupMember()
        {
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(SupportGroupEntity.Relations.SupportGroupMemberEntityUsingSupportGroupGuid, "__SupportGroupEntity__", "SupportGroupMember_", JoinHint.None);
            bucket.Relations.Add(SupportGroupMemberEntity.Relations.ContactEntityUsingMemberContactGuid, "SupportGroupMember_", string.Empty, JoinHint.None);
            bucket.PredicateExpression.Add(new FieldCompareValuePredicate(SupportGroupFields.ContactGuid, null, ComparisonOperator.Equal, this.ContactGuid));
            return bucket;
        }


You are fetching supportGroupMembers entities, you should use relations starting from the type you are fetching and walked to whatever types you need to join to.

So your relations should start with:
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(SupportGroupMemberEntity.Relations....);
  Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 27-Feb-2008 14:27:05.  
[quotenick="Walaa"]
Quote:
using (IDataAccessAdapter adapter = BLServices.Adapters.InfrastructureCentral)
            {
                adapter.FetchEntityCollection(this._supportGroupMembers, this._supportGroup.GetRelationInfoContactCollectionViaSupportGroupMember());
.......................


Sorry about that Walla. The variable this._supportGroupMembers is actually an an entity collection of contacts ( EntityCollection<ContactEntity> ) and not an EntityCollection of SupportGroupMembers. The SupportGroupMembers table just holds the relationship for membership of a contact to a SupportGroup . So since a SupportGroup is a contact and a support group has contacts as members a SupportGroup could have other SupportGroups as members. What I am trying to fetch is all of the contacts that are related to the SupportGroup by being a support group member.

The GetRelationInfoContactCollectionViaSupportGroupMember method was generated by llblgen as a m:n relation. When LLBLGen generates the sql it seems to not to check that a SupportGroup is of the type Contact so when it fetches the related Contacts to the SupportGroup by using the SupportGroupMembers relationship the tables are not aliased properly causing bad results. m:n relationships work everywhere else except when the target of the m and n are members of the same inheritance hierarchy.

I can get around it by not using those m:n relationships ( Contact to SupportGroup via SupportGroupMember and SupportGroup to Contact via SupportGroupMember ) but it would be nice for them to work and they should work.


Thanks Brandt


Brandt  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14641 posts
# Posted on: 27-Feb-2008 16:22:01.  
Now I'm confused, you haven't mentioned Inheritance relations before.

Anyway this looks like an issue that was saved on 22nd of Jan 2008, would you please download and use the latest available release.

If this doesn't solve the issue, please sketch out the relevant database schema and explain again with code snippets based on the sketched out schema.

And always keep in mind to use relations in a sequential way, starting from the type you are fetching.
  Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 27-Feb-2008 16:24:22.  
Walaa wrote:
Now I'm confused, you haven't mentioned Inheritance relations before.

Anyway this looks like an issue that was saved on 22nd of Jan 2008, would you please download and use the latest available release.

If this doesn't solve the issue, please sketch out the relevant database schema and explain again with code snippets based on the sketched out schema.

And always keep in mind to use relations in a sequential way, starting from the type you are fetching.



Thanks Walla, I will download the newest binaries. In my second post I explained it was an inheritance heirarchy after I found that other things were broken. I will get back with you. I attached a zip in the second post that shows the tables and the inheritance set up in llblgen. *** I thought I added the attachment but hit the continue button instead of the upload. I added it now to the 2nd post. ****


Brandt  Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 27-Feb-2008 16:52:52.  
I upgraded the runtime and made sure the correct ORMsupport dll was being copied to the bin folder. The runtime is now : 2.5.8.214. I am still having the same problem. If you could please check the zip file that I attached to the second post. It has the structure of the database and the inheritance hierarchy as images. The problem happens when a SupportGroup ( a subtype of Contact ) tries populate a related m:n collection of Contact using the generated GetRelationInfoContactCollectionViewSupportGroupMember relationship.

In the first post I included the code that I called and also the generated methods that create the RelationPredicateBucket.

Thanks Brandt

Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38097 posts
# Posted on: 27-Feb-2008 18:41:55.  
I have a problem with this sentence: "The dataflow is like this; A SupportGroup is a Contact, which has SupportGroupMembers who also happen to be contacts. "
when I check the images you posted, I don't see SupportGroupMember as a subtype of Contact in the hierarchy. So it doesn't join Contact again for supportgroupmember as it doesn't know that supportgroupmember is of type Contact, only SupportGroup is. (inheritance1.jpg).

The bug you run into was plaguing v2.0, and was fixed in v2.5. However if there's no meta-data that supportgroupmember is a subtype of contact, it can't possibly join the parentrelations Regular Smiley Could you check that for me please?


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 27-Feb-2008 18:56:02.  
Otis wrote:
I have a problem with this sentence: "The dataflow is like this; A SupportGroup is a Contact, which has SupportGroupMembers who also happen to be contacts. "
when I check the images you posted, I don't see SupportGroupMember as a subtype of Contact in the hierarchy. So it doesn't join Contact again for supportgroupmember as it doesn't know that supportgroupmember is of type Contact, only SupportGroup is. (inheritance1.jpg).

The bug you run into was plaguing v2.0, and was fixed in v2.5. However if there's no meta-data that supportgroupmember is a subtype of contact, it can't possibly join the parentrelations Regular Smiley Could you check that for me please?


Thanks Otis. Your right that SupportGroupMembers are not a subtype of Contact. There is a one to many relation between SupportGroup and SupportGroupMembers. That is why I said that "A SupportGroup is a contact" and that the SupportGroup has SupportGroupMembers (like a customer has orders). The SupportGroupMember then has a relationship back to the ContactEntity. The confusion is in the part "who also happen to be contacts" I didn't mean for that to suggest that SupportGroupMembers are also Contacts. I meant that the table SupportGroupMember is the membership of a contact to a supportgroup.
Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38097 posts
# Posted on: 27-Feb-2008 19:32:35.  
So supportgroupmember is the intermediate entity between SupportGroup and Contact which should be a m:n relation, based on the intermediate entity supportgroupmember?

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 27-Feb-2008 19:33:50.  
Otis wrote:
So supportgroupmember is the intermediate entity between SupportGroup and Contact which should be a m:n relation, based on the intermediate entity supportgroupmember?


Exactly.
Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38097 posts
# Posted on: 27-Feb-2008 20:25:19.  
Brandt wrote:
Otis wrote:
So supportgroupmember is the intermediate entity between SupportGroup and Contact which should be a m:n relation, based on the intermediate entity supportgroupmember?


Exactly.

Ok, so the relations are between SupportGroupMember -> Contact and SupportGroupMember -> SupportGroup.

We have a succeeding unittest with that scenario. I'll see if it is different with a situation which mimics your situation. Though I've to say I still have the feeling I don't see the exact picture. With complex situations like this, the .lgp project file is essential for us to get a good picture what's going on.

Please either attach it here or open a helpdesk thread and attach it there (so only we can see it)


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 27-Feb-2008 20:35:49.  
I attached the lgp of the project to this thread. Thanks again.
Brandt  Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 27-Feb-2008 20:40:31.  
[quotenick="Otis"][quotenick="Brandt"]
Otis wrote:


Please either attach it here or open a helpdesk thread and attach it there (so only we can see it)


I tried to attach the .lgp file here but it was larger than the 256k limit so I created a thread in the helpdesk and uploaded it there. Thanks again.


Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38097 posts
# Posted on: 27-Feb-2008 21:35:30.  
Continued in helpdesk thread.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.