v2.0 -> v5.5 LLBLGen Pro Framework : TypedListDAO issue

Posts   
 
    
sapul
User
Posts: 49
Joined: 11-Jan-2019
# Posted on: 02-May-2019 08:31:43   

Hello

From version 2.0 to version 5.5, we've noticed a failure in our running tests before switching to version 5.5.

Queries that derive from the same .net code bring in different records. Especially in a scenario, the query is not created correctly.

In particular, the question I want to ask, 2.0 in the code we can produce the query, 5.5 AnsiJoin (false) version of the query is getting faulty and runtime error.

I think I have explained it better in the comparison table I attached. We'd appreciate it if you could help us to figure out Why this error is caused and how we can solve it.

edit: i've added another issue on the same subject with no filter. Please see the second attacted image.

Attachments
Filename File size Added on Approval
lllgen_diffrentSqls.PNG 376,756 02-May-2019 08:31.54 Approved
sapul
User
Posts: 49
Joined: 11-Jan-2019
# Posted on: 02-May-2019 09:25:29   

the second example is attached


[TestMethod()]
public void BankaUlkeListeleTest()
        {
            var filter = new PredicateExpression();
            var sorter = new SortExpression();

            using (DataTable dt = BankaListeleTransaction.BankaUlkeListele(filter, sorter))
            {
                Assert.IsTrue(dt.AsEnumerable().Any(p => string.IsNullOrWhiteSpace(p["UlkeAd"].ToString())));
                Assert.IsTrue(dt.AsEnumerable().Any(p => !string.IsNullOrWhiteSpace(p["UlkeAd"].ToString())));
            }
        }
public  static DataTable BankaUlkeListele(IPredicateExpression filter, SortExpression bankaSortEx)
        {
            TypedListBuilder tlb = new TypedListBuilder();
            tlb.addRelations(RelationHelper.CreateCustomRelation(LokasyonFields.Id, BankaFields.UlkeId, "LokasyonEntity", "BankaEntity"), JoinHint.Left);
            tlb.addFields(BankaFields.Id, "Id");
            tlb.addFields(BankaFields.Kod, "Kod");
            tlb.addFields(BankaFields.Ad, "Ad");
            tlb.addFields(BankaFields.AktifMi, "AktifMi");
            tlb.addFields(BankaFields.SikKullanilanMi, "SikKullanilanMi");
            tlb.addFields(LokasyonFields.Ad, "UlkeAd");
            tlb.addFields(BankaFields.AlternatifBankasiOlacakMi, "AlternatifBankasiOlacakMi");
            return tlb.Fill(filter, true, bankaSortEx);
        }
public static IEntityRelation CreateCustomRelation(IEntityField PkField, IEntityField FkField, string PkSideEntity, string FkSideEntity)
        {
            IEntityRelation customRelation = new EntityRelation(SD.LLBLGen.Pro.ORMSupportClasses.RelationType.ManyToOne);
            customRelation.StartEntityIsPkSide = false;
            customRelation.AddEntityFieldPair(PkField, FkField);
            customRelation.InheritanceInfoPkSideEntity = ModelInfoProviderSingleton.GetInstance().GetInheritanceInfo(PkSideEntity, false);
            customRelation.InheritanceInfoFkSideEntity = ModelInfoProviderSingleton.GetInstance().GetInheritanceInfo(FkSideEntity, true);
            return customRelation;
        }
Attachments
Filename File size Added on Approval
llblgenrightJoin.PNG 125,431 02-May-2019 09:26.35 Approved
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-May-2019 10:06:44   

Any reason you switch off ansi joins? If anything please switch it on (it's the default). The Oracle optimizer will convert the ansi joins back to non ansi joins behind the scenes anyway. Oracle databases starting with 9i work fine with ansi joins. Our tests also use only ansi joins, as non-ansi joins were implemented only for Oracle 8i which we don't support anymore.

We'll see what's wrong with the current implementation of the joins however, perhaps we missed something small.

I recon it goes wrong only with a custom relation? I don't know what 'TypedListBuilder' does tho. If you use a DynamicRelation() instead, does that cause the same problems? It's added after 2.0 to have a more flexible way to define a relation outside the generated code model so you don't need to do all these things an 'EntityRelation' requires.

Frans Bouma | Lead developer LLBLGen Pro
sapul
User
Posts: 49
Joined: 11-Jan-2019
# Posted on: 02-May-2019 12:31:35   
  • Any reason you switch off ansi joins?

Since we upgraded an existing project from version 2.0 to version 5.5, we tested by keeping existing settings including ANSI. If your tests are not running for ANSI = OFF, as you say, we need to continue (we'd better simple_smile ) on our way as ANSI = ON. Am i right=?

what goes wrong with a custom filter?

This issue is related to 5.5 whether ansi settings is on or off. We solve the problem by adding filter directly to the main where clause.

IEntityRelation kontRelation = DokTescilKonsimentoEntity.Relations.DokTescilKonteynerEntityUsingDokTescilKonsimentoId; //kontRelation.CustomFilter = new PredicateExpression(DokTescilKonteynerFields.DokKonsKonteynerYukuId == kontYukIdList); tlb.addRelations(kontRelation, JoinHint.Left);

        IEntityRelation dokmeRelation = DokTescilKonsimentoEntity.Relations.DokTescilDokmeEntityUsingDokTescilKonsimentoId;
     //dokmeRelation.CustomFilter = new PredicateExpression(DokTescilDokmeFields.DokKonsimentoDokmeYukuId == dokmeYukIdList);
        tlb.addRelations(dokmeRelation, JoinHint.Left);

        tlb.addFields(DokTescilKonteynerFields.DokKonsKonteynerYukuId, "DokKonsKonteynerYukuId");
        tlb.addFields(DokTescilDokmeFields.DokKonsimentoDokmeYukuId, "DokKonsDokmeYukuId");
        tlb.addFields(DokTescilSeferFields.Id, "DokTescilSeferId");

        **var newfilterInsteadOfcustomfilter = new PredicateExpression();
        newfilterInsteadOfcustomfilter.Add(DokTescilKonteynerFields.DokKonsKonteynerYukuId == kontYukIdList);
        newfilterInsteadOfcustomfilter.Add(DokTescilDokmeFields.DokKonsimentoDokmeYukuId == dokmeYukIdList);**

        //return tlb.Fill();
        **return tlb.Fill(newfilterInsteadOfcustomfilter);**

-I recon it goes wrong only with a custom filter?

No. My second post in this thread includes an example. LLBLGEN 5.5 ANSI=OFF can not generate code properly for JoinHint.LEFT JOIN relation with settings customRelation.StartEntityIsPkSide = false;

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-May-2019 13:31:05   

sapul wrote:

  • Any reason you switch off ansi joins?

Since we upgraded an existing project from version 2.0 to version 5.5, we tested by keeping existing settings including ANSI. If your tests are not running for ANSI = OFF, as you say, we need to continue (we'd better simple_smile ) on our way as ANSI = ON. Am i right=?

Yes. non-ansi code is still there and should work, but alas, it might fail in some edge case (also because there are situations which are complex to formulate in non-ansi and straightforward in ansi).

what goes wrong with a custom filter?

This issue is related to 5.5 whether ansi settings is on or off. We solve the problem by adding filter directly to the main where clause.

IEntityRelation kontRelation = DokTescilKonsimentoEntity.Relations.DokTescilKonteynerEntityUsingDokTescilKonsimentoId; //kontRelation.CustomFilter = new PredicateExpression(DokTescilKonteynerFields.DokKonsKonteynerYukuId == kontYukIdList); tlb.addRelations(kontRelation, JoinHint.Left);

        IEntityRelation dokmeRelation = DokTescilKonsimentoEntity.Relations.DokTescilDokmeEntityUsingDokTescilKonsimentoId;
     //dokmeRelation.CustomFilter = new PredicateExpression(DokTescilDokmeFields.DokKonsimentoDokmeYukuId == dokmeYukIdList);
        tlb.addRelations(dokmeRelation, JoinHint.Left);

        tlb.addFields(DokTescilKonteynerFields.DokKonsKonteynerYukuId, "DokKonsKonteynerYukuId");
        tlb.addFields(DokTescilDokmeFields.DokKonsimentoDokmeYukuId, "DokKonsDokmeYukuId");
        tlb.addFields(DokTescilSeferFields.Id, "DokTescilSeferId");

        **var newfilterInsteadOfcustomfilter = new PredicateExpression();
        newfilterInsteadOfcustomfilter.Add(DokTescilKonteynerFields.DokKonsKonteynerYukuId == kontYukIdList);
        newfilterInsteadOfcustomfilter.Add(DokTescilDokmeFields.DokKonsimentoDokmeYukuId == dokmeYukIdList);**

        //return tlb.Fill();
        **return tlb.Fill(newfilterInsteadOfcustomfilter);**

-I recon it goes wrong only with a custom filter?

No. My second post in this thread includes an example. LLBLGEN 5.5 ANSI=OFF can not generate code properly for JoinHint.LEFT JOIN relation with settings customRelation.StartEntityIsPkSide = false;

OK,

So to recap: the issue occurs when: 1) ansi joins are off 2) a custom relation is used (m:1) with left join. (which should add the (+) suffixes on the fields but apparently doesn't in the second example)

? We'll create a test for this and see if we can reproduce this.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-May-2019 13:56:57   

I can reproduce the problem of not having (+) in the where clause.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-May-2019 14:11:16   

Fixed in next hotfix build of 5.4.6 and 5.5.3. It was a mistake caused by optimization where we made a lot of changes to bring down allocations in the library and we forgot at one spot to specify the pkfield suffix (which would be (+) here in this particular case) in the special string concat code we added. The fk field suffix for non-ansi joins (in your particular case this isn't used) was in place.

If you have to add a new custom relation, please use DynamicRelation instead btw, less code wink

The v5.5.3 hotfix build will be available in a couple of minutes.

Frans Bouma | Lead developer LLBLGen Pro
sapul
User
Posts: 49
Joined: 11-Jan-2019
# Posted on: 02-May-2019 14:28:36   

thanks for both the fix and the advise! simple_smile

Regards, Serkan

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-May-2019 15:14:14   

Hotfix is now available. Runtime updates should be available on nuget in a couple of minutes (after it is done indexing).

Frans Bouma | Lead developer LLBLGen Pro