Hi!

LLBLGen Newbie here looking to make code that will produce a query with the following result

SELECT DISTINCT r.*
FROM [dbo].[structure] s
INNER JOIN [dbo].[element] e
ON s.PermissionID = e.ID
INNER JOIN [dbo].[rights] r
ON r.ID = s.ParentID
WHERE e.ObjectID = 'some-guid'

Also looking to use prefetch path in the same query, and to fetch some related entities of r (let's call it RightsEntity) at the same time

The inheritance hierarchy is as follows: structure is by itself and element is a parent of rights

I have changed actual table names and column names

Is a query like this possible, and possible to do a prefetch path in the same query?

If this isn't enough information please let me know and I can post more including my current attempt (I would have to obfuscate it more that's why I don't just paste it in right now)

Also I must use self-servicing so classes like IPredicateExpression, IRelationPredicateBucket and so on

Thanks!

You say you have to use SelfServicing, however if this is a new project, you're highly recommended to use Adapter. RelationPredicatebucket is an Adapter construct.

I recon, the relationships are: Structure m:1 Element and Structure m:1 Rights ? As Element is a parent of Rights, it'll be joined twice due to the inheritance

var qf = new QueryFactory();
var q = qf.Structure
            .From(QueryTarget.InnerJoin(qf.Element).On(StructureFields.PermissionID.Equal(ElementFields.ID)))
                .InnerJoin(qf.Rights).On(StructureFields.ParentID.Equal(RightsFields.ID)))
            .Where(ElementFields.ObjectID.Equal("some-guid"));
using(var adapter = new DataAccessAdapter())
{
    var results = adapter.FetchQuery(q);
    // do something with results
}

This is a queryspec variant of the query you posted (see https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/QuerySpec.htm). The join with Rights doesn't make sense, see below.

You can also write the query as a linq query, however it's recommended to use either queryspec or linq for fetch queries.

You mention prefetch paths but the example you give, rights, is already in the query, so it's unclear why you want to fetch related entities of that type.

As you're trying to fetch entities, and I think you fetch structure entities here?, you don't need to join entities which aren't going to be fetched nor involved in a predicate. Here, you filter on a field in Element, but you also join Rights, but there's no need to do that.

If you want to fetch structure entities, and their related right entities, based on a filter on element, then the query becomes:

var qf = new QueryFactory();
var q = qf.Structure
            .From(QueryTarget.InnerJoin(qf.Element).On(StructureFields.PermissionID.Equal(ElementFields.ID)))
            .Where(ElementFields.ObjectID.Equal("some-guid"))
            .WithPath(StructureEntity.PrefetchPathRights);
using(var adapter = new DataAccessAdapter())
{
    var results = adapter.FetchQuery(q);
    // do something with results
}

Here I've specified 1 join, namely the one which is used in the filter, and the rights are fetched as a prefetch path node. This query can also be done in linq if you want to. (or the low level API, but it's recommended to use linq or queryspec). See: https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_prefetchpaths.htm

Yes, low-level API example would be great!

The join with "rights" table is because it's a self referencing table... if I remove it, I get a different result of rows than intended. Are you saying that join is completely unnecessary even in SQL?

Thanks again for the help!

This is the type of code I am dealing with https://www.llblgen.com/documentation/2.6/Tutorials%20and%20examples/examples_howdoi.htm it's an existing older system

Just to be clear.

You are fetching Rights, correct?

Are you joining to Strtucture to limit/filter the returned Rights?

Is a query like this possible, and possible to do a prefetch path in the same query?

I don't see anything that makes it not possible, and yes a prefetchPaths can be provided in the same fetch method, to fetch related entities. A separate query will be generated to fetch each prefetchPath provided.

It's crucial you provide what you already have, as we're otherwise talking in hypothetical situations. Also provide what version you're working with, as old versions like 2.6 (which is from 2008 ) have very little features compared to today's versions. It's also unclear what the relationships really are.

Otis wrote:

It's crucial you provide what you already have, as we're otherwise talking in hypothetical situations. Also provide what version you're working with, as old versions like 2.6 (which is from 2008 ) have very little features compared to today's versions. It's also unclear what the relationships really are.

Hi sorry we do have a professional version of LLBLGen the latest 5.6, just that our codebase is written in that older way

Anyway I did find the answer to most of my questions from a coworker (using subquery and FieldCompareSetPredicate) but I'll leave this here in case anyone wants to comment or search for something like this

You did give me an idea that helped!

Thank you!

Ok, well, if anything, you can use linq/queryspec queries in the same codebase as when you use the low level api. FieldCompareSetPredicate is something you shouldn't be using nowadays anymore, as it's very verbose and the new constructs are way simpler to write and maintain. E.g.https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Filtering%20and%20Sorting/gencode_filteringpredicateclasses.htm#fieldcomparesetpredicate allows you to produce FieldCompareSetPredicate instances with easier to read code. But maybe for next time