Hi,

I am struggling with joining and filtering data in LLBL Gen Where can i find some examples?

Assume I have four tables, order, orderdetails, customer and category. Order (Id, CustomerId, Number, Date) OrderDetail (Id, OrderId, Amount, CategoryId) Customer (Id, Name) Category(Id, Name)

I want to have the orders after a specific date with a specific category for a specific customer. My query will be something like below.

SELECT o.Id, o.Number, o.Date, c.Name, cat.Name, od.Amount FROM Order o INNER JOIN OrderDetails od ON od.OrderId = o.Id INNER JOIN Category cat ON cat.Id = od.CategoryId INNER JOIN Customer c ON c.Id = o.CustomerId WHERE o.Date > GETDATE() AND od.CategoryId = 2 AND o.CustomerId = 34

Or if my query is simpler will that change the way we need to ask LLBLGen? SELECT o.Id, o.Number, o.Date FROM Order o INNER JOIN OrderDetails od ON od.OrderId = o.Id WHERE o.Date > GETDATE() AND od.CategoryId = 2 AND o.CustomerId = 34

Can you please shine some light on this?

Regards, John

Perhaps the docs can help? https://www.llblgen.com/Documentation/5.10/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Filtering%20and%20Sorting/gencode_filteringadvanced.htm#multi-entity-filters

Otis wrote:

Perhaps the docs can help? https://www.llblgen.com/Documentation/5.10/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Filtering%20and%20Sorting/gencode_filteringadvanced.htm#multi-entity-filters

Thanks!

Hi,

Sorry to disturb you again, but I still get an error which doesn't make much sense to me.

I took an example and changed the entities to mine.

            // Define QuerySpec query
            var qf = new QueryFactory();
            var q = qf.AdviesVervolgactieSet
                    .From(QueryTarget
                       .InnerJoin(AdviesVervolgactieSetEntity.Relations.AdviesVervolgactieSetVoorwaardenEntityUsingAdviesvervolgactieSetId))
                .Where(AdviesVervolgactieSetFields.IngangsklachtId.Equal(ingangsklachtId));
            EntityCollection<AdviesVervolgactieSetEntity> adviesVervolgactieSets;

            // fetch them using a DataAccessAdapter instance
            using (var adapter = new DataAccessAdapter())
            {
                adviesVervolgactieSets = adapter.FetchQuery(q, new EntityCollection<AdviesVervolgactieSetEntity>());
            }

            Console.WriteLine("Number of entities fetched: {0}", adviesVervolgactieSets.Count);

            foreach (var c in adviesVervolgactieSets)
            {
                Console.WriteLine("{0} {1}", c.AdviesvervolgactieSetId, c.Ingangsklacht.Naam);
            }

It gives me a designtime error on the adapter.FetchQuery(... : The type arguments cannot be inferred of the usage. So I change this line to:

adviesVervolgactieSets = adapter.FetchQuery<AdviesVervolgactieSetEntity>(q, new EntityCollection<AdviesVervolgactieSetEntity>())

but then the messge is 'cannot resolve method FetchQuery()..' If I look into FetchQuery, I see two overloads FetchQuery(string, object) and FetchQuery(PlainSQLFetchAspects, string, object). But that doesn't fit. My q variable is of type EntityQuery<T>. I also did a q.ToString(), but then i got a TargetParameterCountException.

What can I do to make this work? I only have 2 entities and I want all the fields from both entities and a filter on some id.

Thanks for you help. John

You need to add

using SD.LLBLGen.Pro.QuerySpec;
using SD.LLBLGen.Pro.QuerySpec.Adapter;

to the top of your code file. FetchQuery is an extension method but as you don't have the using at the top, it'll match with the FetchQuery method on the adapter which is for plain SQL queries

Thanks!

So far this works, but my underlying objectlist is empty. The AdviesVervolgactieSetVoorwaardens list.

            foreach (var c in adviesVervolgactieSets)
            {
                Console.WriteLine("{0} {1}", c.AdviesVervolgactieSetId, c.AdviesVervolgactieSetVoorwaardens.FirstOrDefault().Urgentiecategorie.Naam);
            }

If I do it the other way around (select from adviesVervolgactieSetVoorwaarden) then my AdviesVervolgactieSet is null.

Am I still missing something?

Thanks for your time.

If you fetch entities, you specify which entity type you want to fetch and specify a query to obtain the instances you want. This is always either 1 or a set of entities of that type. If you want to fetch related entities as well, you have to specify a prefetch path, which 'eager loads' the related entities into a graph. You use adapter which doesn't use lazy loading. To define the prefetch path to specify the additional entities: https://www.llblgen.com/Documentation/5.10/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_prefetchpaths.htm

Looking at the query you posted, I think you can remove the join as you don't need it (as the prefetch path will be fetched using separate queries).

Hi,

Here I am again. Had a good Easter?

I am almost there. Just a few minor steps away.

Now I have this:

            var q2 = qf.AdviesVervolgactieSetVoorwaarden
                .Where(AdviesVervolgactieSetVoorwaardenFields.UrgentiecategorieId == 3)
                .WithPath(AdviesVervolgactieSetVoorwaardenEntity.PrefetchPathAdviesVervolgactieSet
                    .WithFilter(AdviesVervolgactieSetFields.IngangsklachtId.Equal(32))
                    .WithSubPath(AdviesVervolgactieSetEntity.PrefetchPathIngangsklacht))
                .WithPath(AdviesVervolgactieSetVoorwaardenEntity.PrefetchPathUrgentiecategorie);

First, the .WithFilter(AdviesVervolgactieSetFields.IngangsklachtId.Equal(32)) doesn't seem to have any affect. Only that the underlying AdviesVervolgactieSet object is null. Is this by design?

Second, I also need to add a filter on the AdviesVervolgactieSet. This is the filter : ((triageTypeId NOT IS NULL && triageTypeId == 2) || triageTypeId IS NULL)

How can I fix and add those filters?

cadsjo wrote:

Now I have this:

            var q2 = qf.AdviesVervolgactieSetVoorwaarden
                .Where(AdviesVervolgactieSetVoorwaardenFields.UrgentiecategorieId == 3)
                .WithPath(AdviesVervolgactieSetVoorwaardenEntity.PrefetchPathAdviesVervolgactieSet
                    .WithFilter(AdviesVervolgactieSetFields.IngangsklachtId.Equal(32))
                    .WithSubPath(AdviesVervolgactieSetEntity.PrefetchPathIngangsklacht))
                .WithPath(AdviesVervolgactieSetVoorwaardenEntity.PrefetchPathUrgentiecategorie);

First, the .WithFilter(AdviesVervolgactieSetFields.IngangsklachtId.Equal(32)) doesn't seem to have any affect. Only that the underlying AdviesVervolgactieSet object is null. Is this by design?

the WithFilter call is for specifying a filter for the node you're adding with WithPath. So in your case if you want to filter the set AdviesVervolgactieSet entities then you specify the filter there. As your filter is targeting AdviesVervolgactieSetVoorwaarden, you should specify it with the where you already have.

Second, I also need to add a filter on the AdviesVervolgactieSet. This is the filter : ((triageTypeId NOT IS NULL && triageTypeId == 2) || triageTypeId IS NULL) How can I fix and add those filters?

You should specify these in the WithFilter call you have now So move the predicate you have in WithFilter to the Where (with an And) and specify these predicates in the WithFilter call you currently have, as the WithFilter is for the node you add, which is AdviesVervolgactieSet