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??

Answer 1

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:

http://efreedom.com/Question/1-1451295/Linq-SQL-Multiple-Foreign-Keys-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.