Using Multiple Foreign Keys to the same table in LINQ

I have a table Users and a table Items

In the Items table, I have fields such as

ModifiedBy CreatedBy AssignedTo

which all have a userId integer. The database is set up to have these as foreign keys back to the Users table.

When using LINQToSQL, the relationships which are automatically built from the dbml end up giving me names like User, User1 and User2

e.g. myItem.User1.Name or myItem.User2.Name

Obviously this isn't very readable and I'd like it be along the lines of

myItem.CreatedByUser.Name or 开发者_如何学GomyItem.ModifiedByUser.Name etc

I could change the names of the relationships but that means I have to redo that every time I change the db schema and refresh the dbml.

Is there any way round this?

The simple answer: No.

Someone has suggested the idea of creating partial Association classes where the property names get defined, but that won't work either: Renaming LINQ 2 SQL Entity Properties Through Partial Classes.

Your choice is to either spend a little time learning more about LINQ-to-SQL "behind-the-scenes" so that you can manually make the necessary modifications or to just change the property names through the Properties window. Personally, I just delete/redrag/rename, because not setting a property correctly is a pain to debug because the exceptions that get thrown give you little to no clue as to what caused it. I even went so far as to create a unit test library that takes each MetaTable object in the model and verifies the field count, the ServerDataType contents of each field, the association count, the names of each association, and the names of each end of the association. Every few changes, I run the unit tests to make sure that the model is intact.

Firstly, no... the names are created based on the second table in the relationship.

But what you should know is that you don't have to "refresh" (meaning, delete the table in the DBML then re-drag-and-drop it).

For the project I'm working on, we have over 200 tables... about 50 of which we have manually tweaked after dragging them from the database. We never delete and re-drag tables as there have been so many changes post-auto-generation.

I just add a small partial class to extend the object with suitably named properties, example below:

namespace Database.TableModels {
    partial class WTSR_Induction {
        public EmailTemplate ConfirmationEmailTemplate {
            get { return EmailTemplate1; }
        }
        public EmailTemplate InviteEmailTemplate {
            get { return EmailTemplate; }
        }
    }
}

In this example, the WTSR_Inductions table has two links to the EmailTemplates table, hence the EmailTemplate and EmailTemplate1 properties.

A bit late but you can do this by selecting the relationship on the linq model and go to properties and update the parent property name.

You could use linq to sql without the dbml it may be extra work upfront but from the perspective of a change to a table column name it may be easier than changes to the dbml as you have described.

I suggest creating extension methods mapping the names you want to the names you get from the autogenerated code. That way after each auto-generation you don't have to change the autogenerated code, but only your own extension methods. That, plus the unit tests to do sanity checks as suggested elsewhere on this page should work fine.

I have just faced this problem myself and I'm off to try to implement my own suggestion.

EDIT: This seems relevant: SQLMetal Multiple Foreign Keys Pointing to One Table Issue