Hello,

I have 6 tables

Message

MessageReference (references Message.MessageId) MessageReferenceClient (inherits MessageReference - references Client.ClientId) MessageReferenceJob (inherits MessageReference - references Job.JobId) Job Client

I am not able to construct a filter with all the relations added.

I've tried many many permutations but this one:

filter.Relations.Add(MessageEntity.Relations.MessageReferenceEntityUsingMessageId, JoinHint.Left); filter.Relations.Add(MessageReferenceJobEntity.Relations.JobEntityUsingJobId); filter.Relations.Add(MessageReferenceClientEntity.Relations.ClientEntityUsingClientId);

seems to get me closest. If I uncomment either the job entity relation of the client entity relation i get a result set. With both of them in I get nothing.

(theres also a relation to another table to MessageTarget) SQL: exec sp_executesql N'SELECT [RedAnts].[dbo].[Message].[MessageId], [RedAnts].[dbo].[Message].[FromUserId], [RedAnts].[dbo].[Message].[Subject], [RedAnts].[dbo].[Message].[Message], [RedAnts].[dbo].[Message].[UpdatedBy], [RedAnts].[dbo].[Message].[UpdatedOn], [RedAnts].[dbo].[Message].[CreatedBy], [RedAnts].[dbo].[Message].[CreatedOn] FROM (((((( [RedAnts].[dbo].[Message] INNER JOIN [RedAnts].[dbo].[MessageTarget] ON
[RedAnts].[dbo].[Message].[MessageId]=[RedAnts].[dbo].[MessageTarget].[MessageId]) LEFT JOIN [RedAnts].[dbo].[MessageReference] ON [RedAnts].[dbo].[Message].[MessageId]=[RedAnts].[dbo].[MessageReference].[MessageId]) INNER JOIN [RedAnts].[dbo].[MessageReferenceJob] ON [RedAnts].[dbo].[MessageReference].[MessageReferenceId]=[RedAnts].[dbo].[MessageReferenceJob].[MessageReferenceId]) INNER JOIN [RedAnts].[dbo].[Job] ON
[RedAnts].[dbo].[Job].[JobId]=[RedAnts].[dbo].[MessageReferenceJob].[JobId]) INNER JOIN [RedAnts].[dbo].[MessageReferenceClient] ON
[RedAnts].[dbo].[MessageReference].[MessageReferenceId]=[RedAnts].[dbo].[MessageReferenceClient].[MessageReferenceId]) INNER JOIN [RedAnts].[dbo].[Client] ON
[RedAnts].[dbo].[Client].[ClientId]=[RedAnts].[dbo].[MessageReferenceClient].[ClientId]) WHERE ( ( [RedAnts].[dbo].[MessageTarget].[UserId] = @UserId1)) ORDER BY [RedAnts].[dbo].[Message].[CreatedOn] DESC',N'@UserId1 int',@UserId1=26

With all the (((( action going I can't read that sql (not sure why it's needed). My first though is the inner joins between the reference table and it's inheritors would result yield no results. I can't seem to control what join hint it uses...

INNER JOIN [RedAnts].[dbo].[MessageReferenceClient] ON
[RedAnts].[dbo].[MessageReference].[MessageReferenceId]=[RedAnts].[dbo].[MessageReferenceClient].[MessageReferenceId]

The above sql is stuffing it up... however I can't understand why removing on of the relations from the top level tables to it's client/job makes any difference...

I'm totally confused!! It's not making any sense... if it's an inherited entity then it should always be a LEFT join shouldn't it?! I mean if I have more than one table inheriting from and try and bring them both back it's always going to fail with an INNER join

Hi Sam,

The problem is the relations between the subtypes and the supertype. Both are INNER so, coz of inheritance no results will return if you include both. However LLBLGen has a especial method for that: MessageReference.GetEntityTypeFilter(). Please read more about this at LLBLGenPro Help - Using the generated code - (Adapter or SS) - Filtering and sorting - Advance filtering usage - Filtering on entity type.

Anyway, as I can see you aren't filtering on subtypes (MessageClient and MessageJob) entity fields. Why are you including the relations. Do you want to prefetch them or to retrieve the supertype and "implicitly" the subtypes?

Hi David,

I can't see how GetEntityTypeFilter helps me... I still get no resultset. I read the cryptic documentation and learned nothing about what this method actually does. Seem to imply it's for limiting the query to a single subtype which is the opposite of what i'm trying to do.

Anyway I added filter.PredicateExpression.Add(MessageReferenceEntity.GetEntityTypeFilter());

and it made no difference to the result.

And I am going be filtering on Client and Job (not messagereferenceclient or messagereferencejob) I just havent done that yet. I will be adding something like ClientFields.Name % "%sam%" and to do that I need the join between MessageReferenceClient and Client. I'm also needing the same thing for MessageReferenceJob and Job.

Maybe this is something llbl just can't do, but that seems unlikely as it would be a big gap in the support of entity inheritence.

Sam,

GetEntityTypeFilter is necessary because the field you're filtering on is in the supertype, so all SubTypes, no matter what they are, are matching. If you want to limit the amount of matching subtypes, you have to add a filter for that particular type.

Could you please post your final snippet including the test with GetEntityTypeFilter ?

Hi David,

I'm not filtering a field on the supertype. I'm only filtering on an entity that is related to the subtype. No filtering is done on any of the entities in the inheritance hierarchy itself, just related entities.

The top level entity type returned here is Message. Please see my first post where i've listed the entities and how they relate.

filter.Relations.Add(MessageEntity.Relations.MessageTargetEntityUsingMessageId);

        filter.PredicateExpression.Add(MessageReferenceEntity.GetEntityTypeFilter());
        filter.Relations.Add(MessageReferenceEntity.Relations.MessageEntityUsingMessageId);
        filter.Relations.Add(MessageReferenceJobEntity.Relations.JobEntityUsingJobId);
        filter.Relations.Add(MessageReferenceClientEntity.Relations.ClientEntityUsingClientId);
        
        filter.PredicateExpression.Add(MessageTargetFields.UserId == ShivamUser.UserInfo.Id);

        //To demonstrate the point of it:
        filter.PredicateExpression.Add(ClientFields.ClientNo % "%0001%");

Thanks.

And most probably if you run the posted SQL query against the database it won't return any results, right?

Your query looks complex Would you please post the exact SQL query which you want to reproduce? (With the appropriate brackets set)

Sam,

I recently completed 2 cases of filtering that were similiar to yours. Each case required a different solution to get it working. So, if the below solution doesn't work I can fill you in on the other one. I'll have to look up the second one when I get a chance, because it was more involved.

The first case I solved by setting filter.Relations.ObeyWeakRelations to True. Then instead of LLBL generating INNER joins, it started generating LEFT joins on the hierarchy joins. I also had join hints in there as well before I tried the ObeyWeakRelations flag, which didn't help, but they may have contributed to it working after setting that flag.

My hierarchy is a TargetPerEntity, so not sure if that makes a difference in how it will function.

Let me know if any of that works.

Jeff

Hi Jeff,

Onbehalf of Sam, I'm writing this message. I added that property as suggested by you and I got some meaningful data but this is not complete result which we want. I used following code.

filter.Relations.Add(MessageEntity.Relations.MessageTargetEntityUsingMessageId,JoinHint.Inner); filter.Relations.Add(MessageReferenceEntity.Relations.MessageEntityUsingMessageId,JoinHint.Left); filter.Relations.Add(MessageReferenceJobEntity.Relations.JobEntityUsingJobId); filter.Relations.Add(MessageReferenceClientEntity.Relations.ClientEntityUsingClientId); filter.Relations.ObeyWeakRelations = true;

As a result of above code, it's generating following query:

[RedAnts].[dbo].[Message] LEFT JOIN [RedAnts].[dbo].[MessageTarget] ON
[RedAnts].[dbo].[Message].[MessageId]=[RedAnts].[dbo].[MessageTarget].[MessageId]) INNER JOIN [RedAnts].[dbo].[MessageReference] ON
[RedAnts].[dbo].[Message].[MessageId]=[RedAnts].[dbo].[MessageReference].[MessageId]) LEFT JOIN [RedAnts].[dbo].[MessageReferenceJob] ON
[RedAnts].[dbo].[MessageReference].[MessageReferenceId]=[RedAnts].[dbo].[MessageReferenceJob].[MessageReferenceId]) LEFT JOIN [RedAnts].[dbo].[Job] ON [RedAnts].[dbo].[Job].[JobId]=[RedAnts].[dbo].[MessageReferenceJob].[JobId]) LEFT JOIN [RedAnts].[dbo].[MessageReferenceClient] ON
[RedAnts].[dbo].[MessageReference].[MessageReferenceId]=[RedAnts].[dbo].[MessageReferenceClient].[MessageReferenceId]) LEFT JOIN [RedAnts].[dbo].[Client] ON [RedAnts].[dbo].[Client].[ClientId]=[RedAnts].[dbo].[MessageReferenceClient].[ClientId])

I'm not happy with above red marked words. I don't understand why it's generating Inner join where I used left and vice-versa. Please help us to come out from this silly problem.

Regards, Viresh

First of all, I think you need to re-order your relations as follows:

    filter.Relations.Add(MessageEntity.Relations.MessageTargetEntityUsingMessageId);

** filter.Relations.Add(MessageEntity.Relations.MessageReferenceEntityUsingMessageId);** filter.Relations.Add(MessageReferenceJobEntity.Relations.JobEntityUsingJobId); filter.Relations.Add(MessageReferenceClientEntity.Relations.ClientEntityUsingClientId); filter.Relations.ObeyWeakRelations = true;

Huuhh....

It's party time guys!!! Finally got it. Thanks guys for your valuable support. I reordered my relations and set property ObeyWeakRelations to true told by Jeff.

Again many many thanks.

Regards, Viresh