After importing from an EDMX, I have a lot of invalid relationships due to FK fields that have a different field length than the PK field. I would like to do some scripting to help me fix those fields. In order to do that, I need the target fields for both ends of the relationship. I can get both entity names using the GetAllRelationshipsForEntity but I can't see any way to get the field information (entity field or target field).

Using the Northwind Product entity as an example, I'd like to be able to map the FK relationships to get this information.

dbo.Products.CategoryId => dbo.Categories.CategoryId
dbo.Products.SupplierId => dbo.Suppliers.SupplierId

In case you want to sync the fields up, the best way (but also the most time consuming one) is fixing them in the DB because a sync with the relational model will otherwise overwrite your fk/pk lengths again...

You have to do two things: change the target field's length and change the FK field mapped onto the field's length. In the entity model, relationships are derived from RelationshipEdge: NormalRelationshipEdge and IndirectRelationshipEdge. The former is for 1:1/1:n/m:1 relationships and the latter for m:n relationships. The method you're calling returns all relationships so you have to filter and cast, do that like this:

var entity = p.EntityModel.Vertices.FirstOrDefault(e=>e.Name=="Order");
var relationships = p.GetAllRelationshipsForEntity(entity, false);
var r = relationships.Where(x=>x.RelationshipType!=EntityRelationshipType.ManyToMany).Cast<NormalRelationshipEdge>().First();
return r.FieldRelationships;

This gives you access to the FieldRelationships property, which is part of the NormalRelationshipEdge. These are pairs of fields (FK field and PK field) which form the relationship. These are references to the real fields, so you can alter the fk field there based on the pk field object and it should correct the length differences.

In the meta-data you can traverse all catalogs then all schemas and then all tables and for each table obtain its ForeignKeyConstraints property. This property gives you a list of DBForeignKeyConstraint objects, which have their fk and pk fields split in two lists (isn't legacy code lovely at times ). So the fk field at offset x in the ForeignKeyFields list of the DBForeignKeyConstraint is related to the pk field at offset x in the PrimaryKeyFields list of that same fk constraint object. So that should be straight forward to fix them up and get them in sync.

I think if you fix up both the relationships in the entity model and the FK constraints in the meta-data the errors go away already so you don't need to look up mappings and go that route too.

If you switch the sync sources in the Sync window to 'model' before running the scripts, you can make the designer track the changes too I think and export an update DDL SQL script. (In case you want to correct the target DB as well)

Why, oh why, didn't I think of getting this from the schema/tables directly? I just got tunnel vision on using the entity relationships even though it's the db relationships I want. So simple when I start with the right element type.

This is the 99% solution for anyone else (or my future self :-)). The DDL script is the last column in the tuple here.

var tupleList = new List<Tuple<int,string, string, int, string, int, string>>();
var messageManager = MessageManagerSingleton.GetInstance();
var counter = 1;

var metaData = p.MetaData.GetMetaDataForDriverID(OracleOdpNetDriverID);
var catalog = metaData.FindCatalogByName("DEQ-ORADB-TEST");
var schemas = catalog.Schemas;

foreach(DBSchema schema in schemas)
{   
    var tables = schema.Tables.Where(t=>t.Fields.Count>0);

    foreach(var t in tables)
    {
        var tableName = t.Name;
        IList<DBForeignKeyConstraint> fkConstraints = t.ForeignKeyConstraints;
        foreach(DBForeignKeyConstraint fk in fkConstraints)
        {
            DBTableField pkField = fk.PrimaryKeyFields.First();
            DBTableField fkField = fk.ForeignKeyFields.First(); 
            
            //var pkFieldCount = fk.PrimaryKeyFields.Count;
            //var fkFieldCount = fk.ForeignKeyFields.Count;
            //messageManager.DispatchNormalMessage("ElementSearch", "Relationships for {0}: PK Fields {1}; FK Fields {2} ", tableName, pkFieldCount, fkFieldCount);         
            
            if(pkField.Length != fkField.Length)
            {
                var ddlScript = string.Format("ALTER TABLE {0} MODIFY ({1} VARCHAR2({2}));", t.FullName, fkField.FieldName, pkField.Length);
                tupleList.Add(Tuple.Create(counter, tableName, pkField.FullName, pkField.Length, fkField.FullName, fkField.Length, ddlScript)); 
                messageManager.DispatchNormalMessage("ElementSearch", "Relationship with non-matching field lengths for {0}: PK Field {1}; FK Field {2} ", tableName, pkField.FullName, fkField.FullName);              
                counter++;
            }
        }
    }
}

return tupleList;

Glad you got it working! Hopefully this (nightmare) conversion will be finished soon for you

Oh yeah, you can say that again. I'm done with the POC for the migration and I've got the steps mapped. I have two PowerShell scripts, six Element Search queries and one custom plug-in to handle adding any of the attributes that I can't do with the rules.

I'll be doing the actual migration this weekend from 30 EDMX files to five LLBLGen projects. The largest one will be a merge of 23 EDMX files. Out of curiosity, do you have any idea what the largest number of entities any of your customers have in a single project? We have 1847 entities representing 1505 tables and 342 views with a total of 27K fields.

We are hoping that the designer performance will be adequate at that size. If not, we'll split it up but that would reduce one of the advantages for using the LLBLGen designer since we can have everything from a single server instance in one place. I should say that our main application uses all of those entities so it is reasonable to keep them together.

If I remember correctly we have several customers with over 3000 entities in one project. I don't expect problems with performance as we optimized the designer a lot, I think we have removed all bottlenecks that occur when things happen more often than expected (usually this is with events being raised over and over resulting in repainting elements so we have a class in Algorithmia called an EventThrottler which can raise an event that occurs many times in an interval just once so repaints don't happen over and over. 1500 entities in one project leads to a larger sized llblgenproj file but the xml loaded should take care of that pretty quickly too.

Please let us know if you encounter slow elements in the designer so we can profile it with the large project and optimize the elements that are slow right away.