SQLMetal Multiple Foreign Keys Pointing to One Table Issue

Question

Edit - Cleaning up question to better reflect the actual issue:

I'm using SQLMetal to generate database classes from our SQL Server db. Recently, I needed to add a table that had multiple foreign keys pointing to the same table. Using LINQPad to play around with the new tables, I was able to access properties for both foreign keys like so:

record.FK_AId
record.FK_BId
record.FKA
record.FKB

...which is just how I would expect it. The problem is, the classes generated by SQLMetal produce these properties:

record.FK_AId
record.FK_BId
record.FKA
record.FKBTableNameGoesHere

Now I could just cange the generated classes so FKBTableNameGoesHere would be FK_B, but the generated files are changed very often by different team members, so that would be a huge pain. Is there an easy fix for this?

Thanks in advance.

Edit 2 So, I thought the solution would be to just create a partial class that had a property named what I wanted it to be, and let the getter/setter point to the poorly named property. That worked for selecting, but not using it in where clauses and such. ANYONE have a solution??

score 4 · Accepted Answer · edited May 23 '17 at 11:48

So, my solution was to just add another partial class and add a property with the get/set pointed to the oddly named FKBTableNameGoesHere property. That way we don't have to keep modifying the generated classes. Not exactly solving the problem, but should make it clearer to developers what the property means. Anyone see any potential issues with that solution?

Edit - So, apparently this only works for selecting data and not filtering based on it. Not as easy of a fix as I had hoped. Anyone have any other suggestions?

Edit 2 - Jeeze, thought this would be somewhat of a common problem but I guess not. Anyway, turns out I was on the right track with this. I found this post:

Multiple foreign keys to the same table

Which gave me the idea that I couldn't just link directly to the getter/setter for another property since there's probably a lot more than that going on behind the scenes. This guys solution wasn't exactly the answer, but it sent me in the rigth direction. Adding the association attributes is what finally did it:

public partial class ProblemClass
{
    [Association(Name = "FK__SomeLinkHere", Storage = "_OriginalPoorlyNamedStorageVariable", ThisKey = "FK_1_Id", OtherKey = "Id", IsForeignKey = true)]
    public FKType MyNewBetterName
    {
        get
        {
            return this._OriginalPoorlyNamedStorageVariable.Entity;
        }

        set
        {
            this.OriginalPoorlyNamedStorageVariable = value;
        }
    }
}

Going to leave the bounty open for anyone who can still come up with a cleaner solution.

This can be actually taken a step further and using _OriginalPoorlyNamedStorageVariable can be omitted entirely. However I'm not sure if not including your own storage variable in constructor won't cause some other complications, but I'm probably going to find out really soon... — jahu, Aug 01 '14 at 13:53

score 0 · Answer 2 · answered May 22 '12 at 05:51

Ok, I'll propose a new answer (little late, sorry) that will work even if the name of the association changes.

This method will look for the association property of the main entity and then It will look for the value in the master table. Imagine that:

Table: Orders referenced with table Customers by Orders.CustomerID equals Customers.Id. So we pass the Meta information of the main table, the field CustomerID (which is the referenced field) and the field Name (which is the value we want).

/// <summary>
/// Gets the value of "referencedValueFieldName" of an associated table of the "fieldName" in the "mainTable".
/// This is equivalent of doing the next LINQ query:
///   var qryForeignValue = 
///     from mt in modelContext.mainTable
///     join at in modelContext.associatedTable
///       on mt.fieldName equals at.associatedField
///     select new { Value = at.referencedValueField }
/// </summary>
/// <param name="mainTable">Metadata of the table of the fieldName's field</param>
/// <param name="fieldName">Name of the field of the foreign key</param>
/// <param name="referencedValueFieldName">Which field of the foreign table do you the value want</param>
/// <returns>The value of the referenced table</returns>
/// <remarks>This method only works with foreign keys of one field.</remarks>
private Object GetForeignValue(MetaTable mainTable, string fieldName, string referencedValueFieldName) {
  Object resultValue = null;
  foreach (MetaDataMember member in mainTable.RowType.DataMembers) {
    if ((member.IsAssociation) && (member.Association.IsForeignKey)) {
      if (member.Association.ThisKey[0].Name == fieldName) {
        Type associationType = fPointOfSaleData.GetType();
        PropertyInfo associationInfo = associationType.GetProperty(member.Name);
        if (associationInfo == null)
          throw new Exception("Association property not found on member");

        Object associationValue = associationType.InvokeMember(associationInfo.Name, BindingFlags.GetProperty, null, fPointOfSaleData, null);

        if (associationValue != null) {
          Type foreignType = associationValue.GetType();
          PropertyInfo foreignInfo = foreignType.GetProperty(referencedValueFieldName);
          if (foreignInfo == null)
            throw new Exception("Foreign property not found on assiciation member");

          resultValue = foreignType.InvokeMember(foreignInfo.Name, BindingFlags.GetProperty, null, associationValue, null);
        }

        break;
      }
    }
  }
  return resultValue;
}

And the call:

      AttributeMappingSource mapping = new System.Data.Linq.Mapping.AttributeMappingSource();
      MetaModel model = mapping.GetModel(typeof(WhateverClassesDataContext));
      MetaTable table = model.GetTable(typeof(Order));
      Object value = GetForeignValue(table, "CustomerId" /* In the main table */, "Name" /* In the master table */);

The problem is that only works with foreign keys with only one referenced field. But, changing to multiple keys is quite trivial.

This is a method to obtain a value of a field of the master table, you can changed to return the whole object.

PS: I think I make some mistakes about my English, it's quite difficult for me.

score 0 · Answer 3 · answered Jul 26 '12 at 22:07

This question came in a long time ago, but I just ran into this problem. I'm using a DBML and I solved this problem by editing the relationships. If you expand the ParentProperty, you can set the property name by changing the Name property.

Here's the XML from the DBML (the Member attribute changed):

  <Association Name="State_StateAction1" Member="DestinationState" ThisKey="DestinationStateId" OtherKey="Id" Type="State" IsForeignKey="true" />

score 0 · Answer 4 · edited May 23 '17 at 12:20

Here is a variant of Ocelot20's version:

public partial class ProblemClass
{
    partial void OnCreated()
    {
        this._MyNewBetterName = default(EntityRef<FKType>);
    }
    protected EntityRef<FKType> _MyNewBetterName;

    [Association(Name = "FK__SomeLinkHere", Storage = "_MyNewBetterName", ThisKey = "FK_1_Id", OtherKey = "Id", IsForeignKey = true)]
    public EntityRef<FKType> MyNewBetterName
    {
        get
        {
            return this._MyNewBetterName.Entity;
        }

        set
        {
            this.MyNewBetterName = value;
        }
    }
}

With this you don't even need to know the original storage name, however I'm not sure if the setter will work (I only used getter, but it worked like a charm). You can even pack this relation definition to a base class and make the partial inherit it (should you need to reuse the relation across multiple models\contexts this should make it easier), but the catch is, you will have to define OnCreated() inside each partial as those cannot be inherited in c# (see this).

SQLMetal Multiple Foreign Keys Pointing to One Table Issue

4 Answers4

Linked