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