Scenario: I have a lookup table: Customer with PrimaryKey CustomerName

I want the ability to "clean up" an incorrect spelling of a CustomerName, for example: Joe Smmmith UPDATE TO Joe Smith NOTE: Joe Smith already exists in the Customer table!!!

So, since this is the primary key and the new value already exists, I need to first update all rows in all descendant table to the new value, and then delete the original row.

So in this case, I was hoping to be able do something like

Dim customer as New CustomerEntity("Joe Smmmith")
For each relation as IRelation in customer.Relations

  'And then in here (but only for child relations), for each relation, we would have to
  'perform an UpdateMulti with the appropriate filter.
  'In this case, the appropriate filter is (CustomerFields.CustomerName = "Joe Smmmith"), BUT, I don't want to hardcode this, I want to be able to properly derive this purely based on the "metadata" that is hopefully available through the relations collection.

Next

So.... a) Do you understand what I mean? Basically, perform and update on all tables subordinate to Customer, updating the appropriate column in the subordinate table from "oldValue" to newValue b) Is it possible, and if so, could you maybe show a little pseudocode to point me in the right direction?

(And yes, I can also think of scenarios that are more complicated, ie: if the relation has > 1 column involved, but lets not worry about that for now, that hopefully will be a simple enhancement).

The reason I want to do this is, I want one nice generic piece of code that can handle this fix on all lookup tables, so I will implement it on one form, and pass the relevant information of what I am updating to this for, from the respective lookup table maintenance form. And as the system grows, this functionality will just continue to work for everything.

I don't think that will work. For one reason: you will obtain an FK violation exception when updating the dependant tables. So the best option IMHO to place a rule on DB for cascade update. Does that work for you?

No, that's exactly my point....if it was as easy as turning on cascading updates, I could let sql server handle it...I'm talking about a case where we have 2 or more rows in the lookup table that should be the same thing....because we have bad data (for whatever reason)....so, I want to say, change all instances of UserXX1 to UserXX2, throughout the entire system. And then, delete UserXX1.

It is very similar to cascading updates, but it is distinctly different.

Dim customer as New CustomerEntity("Joe Smmmith") For each relation as IRelation in customer.Relations

'And then in here (but only for child relations), for each relation, we would have to 'perform an UpdateMulti with the appropriate filter. 'In this case, the appropriate filter is (**CustomerFields.CustomerName **= "Joe Smmmith"), BUT, I don't want to hardcode this, I want to be able to properly derive this purely based on the "metadata" that is hopefully available through the relations collection.

Next

If I understand you correctly, shouldn't the filter in this case be,

RelatedEntityFields.CustomerName == "Joe Smmmith"

?

But I can't hardcode it. Think of writing code that acts upon sql server system tables in order to detect all tables involved in relations with a particular lookup table, and then builds and executes dynamic sql upon them. I want to do this in LLBLGen, by using the relations collection.

Hi Trev -

This should be quite possible, and really not all that difficult.

Simply loop through all the child relations for your Customer entity, use UpdateMulti & construct the Filter Walaa supplied - by using the Primary & Foreign Keys on the Relation.

Should be easy enough to construct.

Ryan

Ya, conceptually it is quite simple....how to implement it without knowing all the inner workings of LLBLGen is another.

So for starters, I have:

        For Each relation As IEntityRelation In MetricClassEntity.Relations.GetAllRelations
            Dim x As String = ""
            Debug.WriteLine(relation.InheritanceInfoFkSideEntity Is Nothing)
            Debug.WriteLine(relation.InheritanceInfoPkSideEntity Is Nothing)
            '// Both are null.....now what??
            '//relation.get?????
        Next

the MetricClassEntity.Relations.GetAllRelations executes:

    Public Overridable ReadOnly Property MetricEntityUsingMetricClassCode() As IEntityRelation
        Get
            Dim relation As IEntityRelation = New EntityRelation(SD.LLBLGen.Pro.ORMSupportClasses.RelationType.OneToMany, "Metric", True)
            relation.AddEntityFieldPair(MetricClassFields.MetricClassCode, MetricFields.MetricClassCode)
            relation.InheritanceInfoPkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("MetricClassEntity", True)
            relation.InheritanceInfoFkSideEntity = InheritanceInfoProviderSingleton.GetInstance().GetInheritanceInfo("MetricEntity", False)
            Return relation
        End Get
    End Property

However, for some reason both:

relation.InheritanceInfoPkSideEntity
relation.InheritanceInfoFkSideEntity

are returning nothing (null) after that executes...I should be able to read those properties within my loop, but they are null, even though I can see them getting set when I step through the code. So what seems the obvious route to take seems to not work as one would expect.

So now I will start looking into some of the various GetXXX() functions on the relation object to see if I can discern anything interesting from there.

Frans...help!

You don't need to look at the InheritanceInfo.

What you want is the primary key / foreign key on the relation.

Pseudo Code:

For Each relation As IEntityRelation In MetricClassEntity.Relations.GetAllRelations()

     // Make sure we are the Parent in the relation
     if (relation.EntityType == OneToMany && relation.StartSideIsPK)
    {
          EntityFields pkFields = relation.GetAllPrimaryKeys();
          EntityFields fkFields = relation.GetAllForeignKeys();

          PredicateExpression filter = new PredicateExpression();

          // Build where clause here
    }

Next

1. I still need to know what the type of entity the child is in the relation.

2. I can't find the actual methods you're referring to here:

EntityFields pkFields = relation.GetAllPrimaryKeys();
EntityFields fkFields = relation.GetAllForeignKeys();

There are many Get() functions, all whose return types are either: IEntityFieldCore IFieldPersistenceInfo

Neither of which look like what I need. Actually, looking through what is available on a Relation, I don't think what I'm trying to do is even possible.

If relation.InheritanceInfoFkSideEntity was populated, at least I would have the string name of the entity on the other side of the relation, so I could construct it via reflection and then do an UpdateMulti. But since it isn't, I don't think it is even possible to determine the type of entity on the other side of the relation.

This is absolutely possible. Simply get the Foreign Key FieldCore objects.

See Here:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=17111

Ryan

Ah, that looks useful...thanks muchly, will see what I can come up with.......