Been pouncing around the forum, but can't seem to get this one to work. Seems like it should be simple enough.

I have the following query:

select * from topic t
left join topic ts on t.topic_id = ts.sub_topic_id
where 1=1
and t.content_type_id in (1, 2)
and t.sub_topic_id is null
order by t.sort_order asc

The table has a circular reference to its PK from sub_topic_id. I would like to get all records where there is a subtopic, plus those where there isn't and the types are 1 and 2.

So far I've written:

IPredicate contentTypeFileter =  new FieldCompareRangePredicate(ContentTypeFields.ContentTypeId, null, "TOPIC_ALIAS", this._contentTypeIds);
IPredicate subTopicFilter =  new FieldCompareNullPredicate(TopicFields.SubTopicId, null, "TOPIC_ALIAS", false);

IEntityRelation circularReferenceTopicIdSubTopicId = TopicEntity.Relations.TopicEntityUsingTopicIdSubTopicId;

base.RelationPredicateBucket.PredicateExpression.Add(contentTypeFileter);
base.RelationPredicateBucket.PredicateExpression.Add(subTopicFilter);
base.RelationPredicateBucket.Relations.Add(circularReferenceTopicIdSubTopicId, "TOPIC_ALIAS", JoinHint.Right);

But the output looks like:

SELECT [dbo].[topic].[topic_id] AS [TopicId], [dbo].[topic].[content_type_id] AS [ContentTypeId], [dbo].[topic].[name] AS [Name], [dbo].[topic].[description] AS [Description], [dbo].[topic].[tags] AS [Tags], [dbo].[topic].[active] AS [Active], [dbo].[topic].[sort_order] AS [SortOrder], [dbo].[topic].[sub_topic_id] AS [SubTopicId], [dbo].[topic].[create_date] AS [CreateDate], [dbo].[topic].[create_user_id] AS [CreateUserId], [dbo].[topic].[update_date] AS [UpdateDate], [dbo].[topic].[update_user_id] AS [UpdateUserId] 

FROM ( [dbo].[topic] [LPA_T1] 
LEFT JOIN [dbo].[topic]  ON  [LPA_T1].[topic_id]=[dbo].[topic].[sub_topic_id]) 
WHERE ( ( [TOPIC_ALIAS].[content_type_id] IN (@ContentTypeId1, @ContentTypeId2) 
AND [LPA_T1].[sub_topic_id] IS NULL))

Can't figure out how to get the aliases in the right place I guess... any help much appreicated!

I figured it out... I realized there were too many aliases getting added. I recalled Walaa's advice is several posts saying "take out an alias, you don't need it".

So this code:

IPredicate contentTypeFileter =  new FieldCompareRangePredicate(TopicFields.ContentTypeId, null, this._contentTypeIds);
IPredicate subTopicFilter =  new FieldCompareNullPredicate(TopicFields.SubTopicId, null, false);

IEntityRelation circularReferenceTopicIdSubTopicId = TopicEntity.Relations.TopicEntityUsingTopicIdSubTopicId;

base.RelationPredicateBucket.PredicateExpression.Add(contentTypeFileter);
base.RelationPredicateBucket.PredicateExpression.Add(subTopicFilter);
base.RelationPredicateBucket.Relations.Add(circularReferenceTopicIdSubTopicId, "TOPIC_ALIAS", JoinHint.Left);

Results with this query:

SELECT [dbo].[topic].[topic_id] AS [TopicId], [dbo].[topic].[content_type_id] AS [ContentTypeId], [dbo].[topic].[name] AS [Name], [dbo].[topic].[description] AS [Description], [dbo].[topic].[tags] AS [Tags], [dbo].[topic].[active] AS [Active], [dbo].[topic].[sort_order] AS [SortOrder], [dbo].[topic].[sub_topic_id] AS [SubTopicId], [dbo].[topic].[create_date] AS [CreateDate], [dbo].[topic].[create_user_id] AS [CreateUserId], [dbo].[topic].[update_date] AS [UpdateDate], [dbo].[topic].[update_user_id] AS [UpdateUserId] 
FROM ( [dbo].[topic] [LPA_T1]  
RIGHT JOIN [dbo].[topic]  ON  [LPA_T1].[topic_id]=[dbo].[topic].[sub_topic_id]) 
WHERE ( ( [dbo].[topic].[content_type_id] IN (1, 2) 
AND [dbo].[topic].[sub_topic_id] IS NULL))

Which is a good thing!

Ooops... actually the above wasn't the desired output... this is mo betta...

IPredicate contentTypeFileter =  new FieldCompareRangePredicate(TopicFields.ContentTypeId, null, this._contentTypeIds);
IPredicate subTopicFilter = new FieldCompareNullPredicate(TopicFields.TopicId, null, "TOPIC_ALIAS", false);

IEntityRelation circularReferenceTopicIdSubTopicId = TopicEntity.Relations.TopicEntityUsingSubTopicId;

base.RelationPredicateBucket.PredicateExpression.Add(contentTypeFileter);
base.RelationPredicateBucket.PredicateExpression.Add(subTopicFilter);
base.RelationPredicateBucket.Relations.Add(circularReferenceTopicIdSubTopicId, "TOPIC_ALIAS", JoinHint.Left);

SELECT [dbo].[topic].[topic_id] AS [TopicId], [dbo].[topic].[content_type_id] AS [ContentTypeId], [dbo].[topic].[name] AS [Name]
FROM ( [dbo].[topic]  
LEFT JOIN [dbo].[topic] [LPA_T1]  ON  [dbo].[topic].[topic_id]=[LPA_T1].[sub_topic_id]) 
WHERE ( ( [dbo].[topic].[content_type_id] IN (1, 2) 
AND [LPA_T1].[topic_id] IS NULL))

I added the wrong relation previously.