Using Multiple Foreign Keys to the same table in LINQ

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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: http://stackoverflow.com/questions/741898/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.