Not sure if this is the right place...

Basically I am using one of the GetMultiManyToManyXXX, and it needs an Entity Object (or more so something implementing the IEntity interface). That's all well and good, but when I step through it, it looks like it builds the predicate expression fine but it only returns one record.

Tables/Entities (Generic Names for this example)

Group User GroupUser (Many to Many link between the two)

userObject.UserID = 1 groupObject.GetMultiManyToManyUsingUserCollectionViaGroupUser(userObject)

In the GroupUser Table, there are two records: UserID = 1 GroupID = 1 UserID = 1 GroupID = 2

Yet I am only getting the first one back. Am I using this method incorrectly?

Sean S

Nevermind, figured it out. Apparently I can't read.

Sean

Actually, same problem with what I thought was the correct way to go.

Here is the call. Me = UserEntity that has already been filled with id = 1

_groupList = me.GetMultiGroupCollectionViaGroupUser(True)

The table information is in my first post.

Here is the generated code (Self Servicing)

Public Overridable Function GetMultiGroupCollectionViaGroupUser(forceFetch As Boolean, entityFactoryToUse As IEntityFactory) As CollectionClasses.GroupCollection If ( Not _alreadyFetchedGroupCollectionViaGroupUser Or forceFetch Or alwaysFetchGroupCollectionViaGroupUser) AndAlso Not MyBase.IsSerializing AndAlso Not MyBase.IsDeserializing AndAlso Not MyBase.InDesignMode Then If MyBase.ParticipatesInTransaction Then If Not groupCollectionViaGroupUser.ParticipatesInTransaction Then MyBase.Transaction.Add(_groupCollectionViaGroupUser) End If End If Dim relations As IRelationCollection = New RelationCollection() Dim Filter As IPredicateExpression = New PredicateExpression() relations.Add(UserEntity.Relations.GroupUserEntityUsingUserId, "__UserEntity", "GroupUser", JoinHint.None) relations.Add(GroupUserEntity.Relations.GroupEntityUsingUserId, "GroupUser", String.Empty, JoinHint.None) Filter.Add(New FieldCompareValuePredicate(EntityFieldFactory.Create(UserFieldIndex.UserId), ComparisonOperator.Equal, Me.UserId)) _groupCollectionViaGroupUser.SuppressClearInGetMulti = Not forceFetch If Not entityFactoryToUse Is Nothing Then _groupCollectionViaGroupUser.EntityFactoryToUse = entityFactoryToUse End If _groupCollectionViaGroupUser.GetMulti(Filter, relations) _groupCollectionViaGroupUser.SuppressClearInGetMulti = False _alreadyFetchedGroupCollectionViaGroupUser = True End If Return _groupCollectionViaGroupUser End Function

To me, this looks like it should be both running a join and returning records from the group table based on UserID = 1 (Through the GroupUser table). For some reason, I am only getting one record back but should get two. Any thoughts?

Sean

Can you post the sql that is generated? You find more on how to do this in the Troubleshooting and debugging portion of the manual?

i would think that relations.Add(GroupUserEntity.Relations.GroupEntityUsingUserId, "GroupUser", String.Empty, JoinHint.None) would be relations.Add(GroupUserEntity.Relations.GroupEntityUsingGroupId, "GroupUser", String.Empty, JoinHint.None) instead.

Could you please enable tracing (See 'troubleshooting and debugging' ) and check what query is generated exactly for this fetch? The filter is OK, as it filters on userid ==1. The join then makes sure that only rows to that user are joined.

Will do so tonight when done with work. I appreciate the help so far.

Sean S

Found the problem

SELECT DISTINCT [Group].[GroupID] AS [GroupId], [Group].[GroupName] FROM ( ( [User] [LPA__1] INNER JOIN [GroupUser] [LPA_I2] ON [LPA__1].[UserID]=[LPA_I2].[UserID] ) INNER JOIN [Group] ON [Group].[GroupID]=[LPA_I2].[UserID] ) WHERE ( (
[LPA__1].[UserID] = @UserId1 ) )

Parameter: @UserId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

This right here is being generated from the code. INNER JOIN [Group] ON [Group].[GroupID]=[LPA_I2].[UserID]

Hense the problem. Any idea why it would generate this? Unfortunately I don't know enough yet to fully understand how this all works.

Sean S

Which runtime library version are you using?

Also, in the designer how's the relation defined between Group and GroupUser? It seems you've defined the relation between Group.GroupID and GroupUser.UserID, could you check that please?

Sorry I didn't write this sooner, bad internet connection.

Turns out to be a foreign key was not pointing the correct column. Sorry for the trouble.

Sean

no problem