Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Adding relations to filter in inheritance scenario
 

Pages: 1
LLBLGen Pro Runtime Framework
Adding relations to filter in inheritance scenario
Page:1/1 

  Print all messages in this thread  
Poster Message
worldspawn
User



Location:
Melbourne, Australia
Joined on:
26-Aug-2006 06:56:13
Posted:
321 posts
# Posted on: 24-Jan-2008 06:43:57.  
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 Confused
--
Sam Critchley
Dotnet 3.5 - SQL Server 2005 - LLBLGen Pro 3.0
 
Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8099 posts
# Posted on: 24-Jan-2008 07:14:43.  
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?


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
worldspawn
User



Location:
Melbourne, Australia
Joined on:
26-Aug-2006 06:56:13
Posted:
321 posts
# Posted on: 25-Jan-2008 00:18:35.  
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 Critchley
Dotnet 3.5 - SQL Server 2005 - LLBLGen Pro 3.0
 
Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8099 posts
# Posted on: 25-Jan-2008 01:51:32.  
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 ?


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
worldspawn
User



Location:
Melbourne, Australia
Joined on:
26-Aug-2006 06:56:13
Posted:
321 posts
# Posted on: 25-Jan-2008 04:27:09.  
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.

        
Code:
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.
--
Sam Critchley
Dotnet 3.5 - SQL Server 2005 - LLBLGen Pro 3.0
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14572 posts
# Posted on: 25-Jan-2008 11:18:33.  
And most probably if you run the posted SQL query against the database it won't return any results, right?

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


  Top
taylor74
User



Location:
USA
Joined on:
06-Oct-2004 18:48:28
Posted:
59 posts
# Posted on: 26-Jan-2008 04:34:10.  
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
  Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# Posted on: 28-Jan-2008 09:14:30.  
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. Confused

Regards,
Viresh


Regards,

Viresh Shah
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14572 posts
# Posted on: 28-Jan-2008 10:21:19.  
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;
  Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# Posted on: 28-Jan-2008 10:31:46.  
Huuhh....

It's party time guys!!!Laugh 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


Regards,

Viresh Shah
 
Top
Pages: 1  


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

Version: 2.1.12172008 Final.