Hi Guys:

Thought I would start a new thread with a descriptive title so that it would be easier for others to find.

I have a few tables which have a column representing the user who has added a message, a company, etc.

Now I have some typed lists which I use to pull information from various tables in order to give me a datatable that I can use with a repeater.

In the current example I bring back all messages sent to a particular person. The problem is if the user who sent the message is no longer on the system it doesn't return the message sent (Which is not what I want. I want all messages returned regardless of whether or not the person who sent it still exists on the system).

I've found threads that mention obeyweakrelations but it doesn't seem to work for me and I was wondering why?

Here are the related threads:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1880 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1280

And here is the method:

public static DataTable GetMessagesSentToUser(Guid userID)
        {

            MessagesSentTypedList messagesSent = new MessagesSentTypedList();
            DataAccessAdapter adapter = new DataAccessAdapter(CampaignManager.ContainerConnectionString);
            IRelationPredicateBucket bucket = (IRelationPredicateBucket)messagesSent.GetRelationInfo();
            bucket.PredicateExpression.Add(PredicateFactory.CompareValue(MessageFieldIndex.DomainUserID, ComparisonOperator.Equal, userID));
            ISortExpression sorter = new SortExpression(SortClauseFactory.Create(MessageFieldIndex.Added, SortOperator.Descending));
            messagesSent.ObeyWeakRelations = true;
            
            adapter.FetchTypedList(messagesSent.GetFieldsInfo(), messagesSent, bucket, 0, sorter, false);

            return messagesSent;
        }

ObeyWeakRelations makes 1:n relations 'weak' (i.e. will do a LEFT JOIN), and will make m:1 and 1:1 relations weak, if the FK side is at the start of the relation and the FK side is nullable. It will then do a RIGHT JOIN.

The old routine (prior to sept. 24) was not that great, the routine after that should work. Do you use a runtime library version released before sept. 24?

Otis wrote:

ObeyWeakRelations makes 1:n relations 'weak' (i.e. will do a LEFT JOIN), and will make m:1 and 1:1 relations weak, if the FK side is at the start of the relation and the FK side is nullable. It will then do a RIGHT JOIN.

The old routine (prior to sept. 24) was not that great, the routine after that should work. Do you use a runtime library version released before sept. 24?

Hi Otis,

I've made the FK on the Message table CampaignManager nullable and I refreshed the catalog and regenerated the code. Unfortunately it still doesn't retrieve the message if I change the ID to e.g. 0 ( A non existing user).

I'm using Typed List.

Each message can have only one campaign Manager but Campaign Managers can send many messages.

Just trying to continue showing all messages sent to a customer even if the person who sent it no longer exists on the system.

I'm using the december build of llblgen pro.

Any idea why it wouldn't work?

Basically I want to do a left join instead of an inner join (I ran a trace and copied it into query analyzer where changing it from inner to left brought the result back).

Thanks

John

Ah I see what's wrong.

messagesSent.ObeyWeakRelations = true; should be before: IRelationPredicateBucket bucket = (IRelationPredicateBucket)messagesSent.GetRelationInfo();

The ObeyWeakRelations flag will be used in the RelationCollection of the bucket. As you first retrieve the bucket and then you set the flag, the bucket still contains a relationcollection which has ObeyWeakRelations (the flag is actually set there, as it affects the outcome of the RelationCollection being converted to SQL) set to false.

Otis wrote:

Ah I see what's wrong.

messagesSent.ObeyWeakRelations = true; should be before: IRelationPredicateBucket bucket = (IRelationPredicateBucket)messagesSent.GetRelationInfo();

The ObeyWeakRelations flag will be used in the RelationCollection of the bucket. As you first retrieve the bucket and then you set the flag, the bucket still contains a relationcollection which has ObeyWeakRelations (the flag is actually set there, as it affects the outcome of the RelationCollection being converted to SQL) set to false.

Thanks Otis that worked.

I originally had it up there and then moved it down to see if that would make a difference (I then changed the column so it could be null but didn't move it back up when the first attempt didn't work).

One last thing with the code I mentioned above.....does the Generated code close open connections itself or should I be declaring:

adapter.CloseConnection();
return messagesSent;

before returning the datatable?

Thanks again

John

Are closed automatically. You only have to call CloseConnection() if you explicitly open it with OpenConnection() or if you've set KeepConnectionOpen to true (which is false by default).