Hi

I am trying to setup a fairly complicated SQL query in LLBL and I think I've come across a small bug. Either that or it's me being retarded!

I'll give you a bit of background info so you'll have more of an idea what I'm trying to do. The db schema for this part is in the attached visio.

Basically the system is a multiple domain CMS. Each domain can have categories assigned from a generic list. Once you've created a doc you can assign it to a category that has been assigned to that documents domain (each document cannot be assigned to multiple domains). To make things complicated categories are hierarchical in nature (only 2 levels tho) and to make it even more complicated they can have multiple parents. With me so far? Now to make it even more complicated when you assign a document to a category you need to specify both parent and child as the document may not be relevant in that parents context.

I.E (Parent > Child)

1) CategoryA > CategoryB - Document is Valid 2) CategoryC > CategoryB - Document is not valid

So I've got a page where the Categories get assigned to the page. What the below query does is pull all the Categories hierarchically correct with the DocumentId if it has been assigned to that category combination and Null if it has not been assigned.

select 
    cr.ParentCategoryId,
    dc.CategoryId,
    dcd.DocumentId      
from dbo.DomainCategory dc
    left join dbo.CategoryRelation cr on dc.CategoryId = cr.ChildCategoryId
    left join dbo.DomainCategoryDocument dcd on dcd.CategoryId = dc.CategoryId and dcd.ParentCategoryId = cr.ParentCategoryId and dcd.DocumentId = 226
where dc.DomainId = 1

After a while searching the Docs and forums I found a way to translate it into LLBL Code and this is what I came up with.

ResultsetFields fields = new ResultsetFields(3);
fields[0] = CategoryRelationFields.ParentCategoryId;
fields[1] = DomainCategoryFields.CategoryId;
fields[2] = DomainCategoryDocumentFields.DocumentId;

IEntityRelation relation1 = new EntityRelation();
relation1.AddEntityFieldPair(DomainCategoryFields.CategoryId, CategoryRelationFields.ChildCategoryId);
relation1.TypeOfRelation = RelationType.ManyToMany;
relation1.HintForJoins = JoinHint.Right;

IEntityRelation relation2 = new EntityRelation();
relation2.AddEntityFieldPair(DomainCategoryDocumentFields.CategoryId, DomainCategoryFields.CategoryId);
relation2.AddEntityFieldPair(DomainCategoryDocumentFields.ParentCategoryId, CategoryRelationFields.ParentCategoryId);                       
relation2.CustomFilter = new PredicateExpression(DomainCategoryDocumentFields.DocumentId == DocumentId);
relation2.TypeOfRelation = RelationType.ManyToMany;
relation2.HintForJoins = JoinHint.Left;


RelationPredicateBucket bucket = new RelationPredicateBucket();         
bucket.Relations.Add(relation1);
bucket.Relations.Add(relation2);
bucket.PredicateExpression.Add(DomainCategoryFields.DomainId = DomainId);

DataTable dynamicList = new DataTable();

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
     adapter.FetchTypedList(fields, dynamicList, bucket);
}

Now this seems to work, except that the generated code has one small error. Reformatted the same as the above query the code comes out as follows.

select 
    cr.ParentCategoryId, 
    dc.CategoryId, 
    dcd.DocumentId 
from 
    dbo.DomainCategory dc 
    left join dbo.CategoryRelation cr on dc.CategoryId = cr.ChildCategoryId) 
    left join dbo.DomainCategoryDocument dcd on dcd.CategoryId = dc.CategoryId and dcd.ParentCategoryId = dc.ParentCategoryId and dcd.DocumentId = @DocumentId1
where 
    dc.DomainId = @DomainId2

It is a little difficult to see the error at first but it is on the second join, one of the joining criteria is trying to join to a non-existent column! In the correct join it is

dcd.ParentCategoryId = cr.ParentCategoryId

and the incorrect generated code join:

dcd.ParentCategoryId = dc.ParentCategoryId

and the code I used to add the criteria clearly shows the correct tables and columns:

relation2.AddEntityFieldPair(DomainCategoryDocumentFields.ParentCategoryId, CategoryRelationFields.ParentCategoryId);

Phew! I hope after all that you understand me!

This is not an urgent issue as I previously said I can use an sp to give me the correct results but it'd be nice to get it to work in LLBL.

Hope you can help

Richard

try using an alias for the CategoryRelationFields.ParentCategoryId, and be sure to use it consistently all over your statement (field definition and in the join creation).

goose wrote:

try using an alias for the CategoryRelationFields.ParentCategoryId, and be sure to use it consistently all over your statement (field definition and in the join creation).

I'm assuming you mean

relation2.SetAliases("PKAlias", "FKAlias");

If so, surely that won't work because you can only assign 2 aliases per relation and I'm using 3 tables in the second join?

Well in this particular 3-table relation try to add the alias using entityField.SetObjectAlias("anyThing");

eg.

relation2.AddEntityFieldPair(DomainCategoryDocumentFields.CategoryId, DomainCategoryFields.CategoryId.SetObjectAlias("anyThing"));