How would I go about doing a query such as this using LLBLGen?

SELECT DISTINCT [Group].GroupID, [Group].Description, [Group].Abbreviation FROM Product, Product_Module, Group_Module, [Group] WHERE Product.ProductID = Product_Module.ProductID AND Product_Module.ModuleID = Group_Module.ModuleID AND Group_Module.GroupID = [Group].GroupID AND Product.ProductID = @ProductID ORDER BY [Group].Description

Thanks in advance...

Is there a portion of the query you do not know how to implement, the how do i section is excellent for most common questions. i.e the distinct, adding fields, nested conditionals, the order by etc?

Also please specify if you are using Adapter or Self Servicing as there are 2 ways of doing most everything =)

John

Thanks for the reply... here is what I have so far, and it seems to do the trick:

Dim filter As New RelationPredicateBucket filter.Relations.Add(ProductEntity.Relations.ProductModuleEntityUsingProductId) filter.Relations.Add(ProductModuleEntity.Relations.ModuleEntityUsingModuleId) filter.Relations.Add(ModuleEntity.Relations.GroupModuleEntityUsingModuleId) filter.Relations.Add(GroupModuleEntity.Relations.GroupEntityUsingGroupId) filter.PredicateExpression.Add(PredicateFactory.CompareValue(ProductFieldIndex.ProductId, ComparisonOperator.Equal, productId)) Dim adapter As New DataAccessAdapter Dim groups As New EntityCollection(New GroupEntityFactory) adapter.FetchEntityCollection(groups, filter)

The joins seem to work; I get the expected rows back. Now, how do I do the DISTINCT and ORDER BY part of the query?

Thanks!

You may want to take a look at some of the How To... sections of the help especially the Filtering and Sorting section for additional information on any queries you are trying to create.

Since you are using an object fetch the duplicates are automatically filtered out. You can then sort your collection on the client side using...

Dim filter As New RelationPredicateBucket
adapter.FetchTypedView(invoices.GetFieldsInfo(), invoices, bucket, 0, sorter, True)
filter.Relations.Add(ProductEntity.Relations.ProductModuleEntityUsingProductId)
filter.Relations.Add(ProductModuleEntity.Relations.ModuleEntityUsingModuleId)
filter.Relations.Add(ModuleEntity.Relations.GroupModuleEntityUsingModuleId)
filter.Relations.Add(GroupModuleEntity.Relations.GroupEntityUsingGroupId)
filter.PredicateExpression.Add(PredicateFactory.CompareValue(ProductFieldIndex.ProductId, ComparisonOperator.Equal, productId))
Dim adapter As New DataAccessAdapter
Dim groups As New EntityCollection(New GroupEntityFactory)
adapter.FetchEntityCollection(groups, filter)

groups.Sort(CType(GroupFieldIndex.Description, Integer), ListSortDirection.Descending)

... or sort on the server, using a SortExpression (though sorting on the client was a good suggestion! often overlooked by developers )