How to recursively set all the PrefechPaths of a hierarchy

Posts   
 
    
egutierrez
User
Posts: 7
Joined: 12-Nov-2018
# Posted on: 01-Mar-2019 17:35:26   

SD.LLBLGen.Pro.ORMSupportClasses: 4.2.20160929 SD.LLBLGen.Pro.DQE.SqlServer: 4.2.20160929 .NET Framework 4.6.1 Sql Server 12.0.5207.0 (2012)

Hi,

I'm working on a web application that has some similar features as the SQL DATA COMPARE tool of the visual studio where we take two databases with exactly the same schema and we compare the data between them.

A difference is that we don't compare all the rows of all the tables but instead we start with a row as the well-known root and from there we take all the data for all the rows in other tables that have a relation. So this is a recursive process as we have to go to all the nodes. We add all the possible paths to get the complete hierarchy of data.

As an example, if I have Table A 1:N TableB and TableB 1:N TableC, I choose a row from TableA and should take it together with all the rows from Table B that have a relation of the row in TableA. The same for TableC with TableB.

The database I'm working with is a legacy DB with about 50 tables and many relations between them such as: - More than one relation from A to B - Relations from A to itself - Relations from A to B and B to A - Relations that form some circular reference A to B, B to C, C to D, D to A - Relations with Int PKs, Composite and non composite PKs, GUID PKs.

var entityCollection = new EntityCollection(new CustomerEntityFactory());
          var filter = new PredicateExpression
          {
              CustomerFields.CustomerID == customerId
          };

            
           using (var adapter = new DataAccessAdapter(_connectionString, false, CatalogNameUsage.Clear, string.Empty))
           {
               var parameters = new QueryParameters
               {
                    PrefetchPathToUse = customerPrefetchPath,
                    CollectionToFetch = entityCollection,
                    FilterToUse = filter,
                    AllowDuplicates = false
               };

               await adapter.FetchEntityCollectionAsync(parameters, cancellationToken: CancellationToken.None);
           }

When I get the data I then use this code to get a DataSet (I need a DataSet):

var projectedDataSet = new DataSet();
entityCollection.CreateHierarchicalProjection(projectionResults);

So my questions are: - Do you think this is the best way to get all the data starting from that root? We only know the entity where we start. Is there another way? - We have tried this code but it seems the prefetch paths are not complete when we go to the subpaths of the second level of the hierarchy. Is there any limitation on having so many prefetch paths? - We also with a previous code got "System.ArgumentException: These columns don't currently have unique values.", not yet in this version of the code but we don't want to have this issue again.

Thanks,

Full Code:


public async Task<DataSet> GetCustomerById(int customerId)
{
    //Create the PrefetchPath hierarchy
    var customerPrefetchPath = new PrefetchPath2((int)EntityType.CustomerEntity);
    var visitedEntities = new HashSet<EntityType>();
    var customerEntity = new CustomerEntityFactory().Create();
    SetEntityPrefetchPath(customerEntity, customerPrefetchPath, visitedEntities);

    //Get the data
    var entityCollection = new EntityCollection(new CustomerEntityFactory());
    
    var filter = new PredicateExpression
    {
        CustomerFields.CustomerID == customerId
    };

    using (var adapter = new DataAccessAdapter(_connectionString, false, CatalogNameUsage.Clear, string.Empty))
    {
        var parameters = new QueryParameters
        {
            PrefetchPathToUse = customerPrefetchPath,
            CollectionToFetch = entityCollection,
            FilterToUse = filter,
            AllowDuplicates = false
        };

        await adapter.FetchEntityCollectionAsync(parameters, cancellationToken: CancellationToken.None);
    }

    //Convert to a DataSet
    var projectedDataSet = new DataSet();
    entityCollection.CreateHierarchicalProjection(projectedDataSet);

    return projectedDataSet;
}

private void SetEntityPrefetchPath(IEntity2 currentEntity, IPrefetchPath2 currentEntityPath, HashSet<EntityType> visitedEntities)
{
    var llblgenEntityType = (EntityType)currentEntity.LLBLGenProEntityTypeValue;
                
    //Base case
    if (visitedEntities.Contains(llblgenEntityType) || currentEntityPath == null)
    {
        return;
    }

    visitedEntities.Add(llblgenEntityType);

    var entityDotNetType = currentEntity.GetType();

    //Gets all the paths. Is there any better way of do it?
    var childPrefetchPathsProperties = entityDotNetType.GetProperties(BindingFlags.Public | BindingFlags.Static).Where(x => x.Name.StartsWith("Prefetch")).ToList();

    foreach (var childPathProperty in childPrefetchPathsProperties)
    {
        var childPrefetchPathElement = (IPrefetchPathElement2)childPathProperty.GetValue(null, null);

        var nextEntity = childPrefetchPathElement.RetrievalCollection.EntityFactoryToUse?.Create();             

        if (nextEntity != null)
        {
            var nextEntityPath = currentEntityPath.Add(childPrefetchPathElement).SubPath;
            SetEntityPrefetchPath(nextEntity, nextEntityPath, visitedEntities);
        }
    }           
}
Attachments
Filename File size Added on Approval
DbDiagram.jpg 30,872 01-Mar-2019 17:35.52 Approved
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 02-Mar-2019 06:47:40   

Hi there,

IMHO, I tend to prefer to explicitly declare the prefetch path elements. That way you have more control over the graph, including: not duplicate branches, avoid circular paths, avoid going too depth in a self-relation, etc. It also is more redeable, clean and efficient.

David Elizondo | LLBLGen Support Team
egutierrez
User
Posts: 7
Joined: 12-Nov-2018
# Posted on: 04-Mar-2019 04:33:38   

Hi,

Thanks for the reply.

This is a special case because we need to dynamically discover all the information and relations from the DB, because this tool is to compare and move data between 2 databases, and the schema can change any time.

The only thing we know is that the root entity is the CustomerEntity, but at developing time we don't know the other entities, only at runtime when we discover all the entities, the relations and its properties. In another case, I would use strongly typed entities but this case is special.

So, I need to retrieve all the rows starting from a start row that is a customer and traverse the complete graph to get all the information I have to compare. This should be retrieved completely as the user will select what data to move.

In the end, I need a method that gives me all the rows after traversing the graph, in the form of a DataSet, I don't even need the entities at all or the strongly typed navigation properties, but there is a requirement of the project to use LLBLGEN to do this task.

I tried this method but it doesn't work, it doesn't retrieve the complete graph, also I tried to another method where I save the prefetch path for every entity and I attach it if the entity type is in more than one relation but It never finished getting the data.

So at this point, I don't know how to do this requirement, not sure if the prefetch paths are the way to go.

I understand that this requirement can sound a little bit weird but this is the nature of the tool, it's very similar to any data comparison tool for databases, but with the difference that we don't retrieve the 100% of the data but we have to traverse a graph.

Another thing is that the app server and the DB server are in the same local network, not over the internet so the network is very fast. Although there are many tables, we don't expect to have a ton of data in one dataset.

Could you please guide me on how to achieve this requirement? much appreciated.

Thanks,

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 04-Mar-2019 20:14:57   

we need to dynamically discover all the information and relations from the DB, because this tool is to compare and move data between 2 databases, and the schema can change any time.

I'm afraid you are using this tool in a way it's not intended to. There are other tools out there for data comparison and migration.

LLBLGen Pro is an ORM, which requires to read the schema and generate code accordingly whenever the schema changes.

So this all happens at Design/Develop-time, not at run-time. So you end up knowing all the information and relations at run-time.