Hello,

I would like to apologize in advance for not being able to show actual production code due to current NDA restrictions.

Hence allow me to explain the the issue with a anonymized entity structure.

Technical Premise:

Issue is a Runtime rehaviour issue (no Exception is thrown) as the only resulting data is wrong / unexpected LLBLGen Pro Version: 5.7 (5.7.2) RTM SD.LLBLGen.Pro.ORMSupportClasses Version: 5.7.2.0 Database Engine: MS SQL Server 2019 (as well as MS SQL Server 2016) .NET Versions involved: .NET 4.8 as well as .net Core 3

Here is the simplified and anonymized entity structure:

class MainEntity
SubEntityL {get;set} (1:1 Relation in Database)
EntityCollection<SubEntityS> {get;set} (1:n Relation in Database)
EntityCollection<SubentityM> {get;set} (1:n Relation in Database)
SubEntityP {get;set} (1:1 Relation in Database)

The Method we are using to fetch the data is in this specific example:

void FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, IPrefetchPath2 prefetchPath);

There are 4 prefetch paths being used, corresponding directly to each of the 4 Properties shown above.

The order of the queries we observed is as follows:

1. MainEntity data is fetched
2. SubEntityL data is fetched
3. SubEntityS data is fetched
4. SubentityM data is fetched
5. SubEntityP data is fetched

The circumstances for a reproduction of the issue (so far known to us):

• A Entity has multiple EntityCollections as property.
• There is a active retry strategy that will determine transient exceptions allowing for a retry
• The problem only occurs during the retry
• The problem only occurs when the first attempt already fetched some(or all) EntityCollections

Detailed Description of our issue:

We are using a custom ActiveRecoveryStrategy that determines if a exception is transient. (The kind of error that triggers the retry seems to have no bearing on the issue as we have encountered the problem with Timeouts, database connection lost and deadlock exceptions.) The fetch call itself is proven to be working as expected when no transient database errors occur. The issue appears when there is a transient error and the retry strategy has is being triggered. Given the circumstance that the SubEntityP query fails (due to e.g. Timeout) the recovery strategy triggers and retries the entire fetch operation.

When the fetch operation then succeeds after the retry, all properties with 1:n relations contain more data than expected/is there. (We have verified that no data duplication is happening on the database) The entities in those properties are in fact duplicated on the first retry.

Say in EntityCollection<SubentityM> should be 2 entities, there will be 4 after the retried fetch.

(If there are multiple retries, the amount of data is growing accordingly, so after the second retry there would 6 entites in the collection) The issue occurs only if on the first (failed fetch attempt) the subqueries for some/all those EntityCollection properties were executed. If say, the first fetch attempt fails on fetching the SubEntityL the problematic behaviour does not occur.

We have the same issue in similar entity structures as well, so it is not a problem with this specific entity.

So to sum up the behaviour:

It seems that the MainEntity Collection was not cleared and the retry fetch seems to update the MainEntities and simply adding new records to the properties with the EntityCollections. The expected behaviour would be that the EntityCollecctions are cleared before the retry fetch is executed.

We have hoped that we could resolve the problem by following the advice given here: https://www.llblgen.com/tinyforum/Thread/2285#12923 as it sounded like a somewhat similar issue. However when doing that, the SubEntity collections remain completely empty which we do not understand why.

We can reproduce the issue on our system by forcing a long running database lock on SubEntityP and in doing so forcing the fetch into a retry (due to a query timeout) with the circumstances outlined above. Due to the very specific nature of the issue, we could not find any other posts that would help us in resolving this issue.

So we are aksing for you help with this issue. I have tried to provide as much and all information we have available. I hope I have not forgotten anything, if so please let me know.

Thank you

Best regards

Mike

When there's a retry to fetch SubentityP, are there also duplicate MainEntity instances in the entity collection returned? (so the collectionToFill collection passed to the FetchEntityCollection method) ? Or only duplicates in the entity collections contained in the MainEntity instances? As that's unclear to me. Also, you refer to a custom strategy but don't mention what exactly it is you changed. If you e.g. added code that mitigates the re-entry check, then a fetch on a path node is retried as you see in your situation it seems, instead of that the root fetch should be retried. Our transient error recovery tests with prefetch paths refetch the whole tree again, so including the MainEntity in your case, not resulting in duplicates at that level. (tho it has a lock on the root entity so it's a bit different than your specific situation).

In any case, the timeout/deadlock on SubentityP should result in a refetch of the entire tree. (See: https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_transientrecovery.htm#strategy-re-entrance-protection) Could you check that with e.g. a profiler?

Thank your for your quick reply!

The MainEntity instances are NOT duplicated. They do have the correct count on each of the fetch attempts.

Our Retry Strategy only overrides the bool IsTransientException(Exception toCheck) method.

We have NOT overriden or hidden the Execute or ExecuteAsync methods.

I also have verified via the SQL Profiler that the sequence of queries and the queries themselves are the same on the first initial and the retry attempt.

We'll make an attempt to reproduce this, but you have to be aware that we're guessing what your setup is as you haven't provided any code. So if we fail to set up a reprocase you have to provide one.

We could reproduce it. The initial thing we thought was the reason isn't the reason, so we have to dig further why this happens.

[Test]
public void TransientErrorRecoveryWithHierarchyTest2()
{
    CreateDeadlock();
    var contacts = new EntityCollection<ContactEntity>();
    var path = new PrefetchPath2(EntityType.ContactEntity);
    path.Add(ContactEntity.PrefetchPathContactCreditCardCollection);
    path.Add(ContactEntity.PrefetchPathSalesOrderHeaderCollection);
    using(var adapter = new DataAccessAdapter())
    {
        adapter.CommandTimeOut = 1;
        adapter.ActiveRecoveryStrategy = new SqlAzureRecoveryStrategy();
        adapter.FetchEntityCollection(contacts, new RelationPredicateBucket(ContactFields.ContactId.LesserThan(10)), 0, 
                                      new SortExpression(ContactFields.ContactId.Ascending()), path);
    }
    
    Assert.AreEqual(9, contacts.Count);
    
    // control group
    var contacts2 = new EntityCollection<ContactEntity>();
    var path2 = new PrefetchPath2(EntityType.ContactEntity);
    path2.Add(ContactEntity.PrefetchPathContactCreditCardCollection);
    path2.Add(ContactEntity.PrefetchPathSalesOrderHeaderCollection);
    using(var adapter = new DataAccessAdapter())
    {
        adapter.CommandTimeOut = 1;
        adapter.FetchEntityCollection(contacts2, new RelationPredicateBucket(ContactFields.ContactId.LesserThan(10)),  0, 
                                      new SortExpression(ContactFields.ContactId.Ascending()), path2);
    }
    
    Assert.AreEqual(9, contacts2.Count);
    for(int i = 0; i < contacts.Count; i++)
    {
        Assert.AreEqual(contacts2[i].ContactCreditCardCollection.Count, contacts[i].ContactCreditCardCollection.Count, "Differs at offset {0}. ContactId: {1}", i, contacts2[i].ContactId);
    }
}

Fixed in hotfix builds 5.8.5 and 5.9.1, now available on nuget and our website.

The issue was a result of a 'feature' we have, which is that you can fetch an entity collection which isn't empty with a prefetch path to fetch e.g. additional entities into that collection. However it goes wrong here, with the re-entry, so we use a Context which we create below the hood if it's not there in this particular situation, to make sure the graph is merged into the right entities. It should work now.

Thank you very much!

We will update as soon was we can!