Linq 2 SQL, cannot create association

Question

I'm working with a bit of a legacy database, but here's the basic idea:

Item
Id INT PK NOT NULL
Description VARCHAR(50) NOT NULL
TranslationId INT NULLABLE

Translation
Id INT PK NOT NULL
Language VARCHAR(10) PK NOT NULL
Translation VARCHAR(50) NOT NULL

Basically every Item MAY have many Translations, seems simple enough. These tables don't have foreign keys, so I'm manually trying to create the association in the DBML, however it fails because of the NULL difference between translationID and the PK in the Translation table.

Is there anyway I can get this association to work, eg:

item.Translations

Error message: Cannot create an association "Item_Translation". Properties do not have matching types: "TranslationId", "Id".

I know it has to do with TranslationId being nullable because another another table with an association to Translations works correctly, however its TranslationId column is NOT NULL.

Sample data from Translation:

Id Language Translation
1  en-us    some text
1  en-gb    some text
2  en-us    some text
2  en-gb    some text

Answer 1

Currently you have this set up so that a translation can have many items, not the way you state where an item can have many translations.  To fix that, you need an itemId in your Translation table, and to remove the translationId from your Item table.

Once you get that taken care of, if you are having trouble, please post the error message you are getting.

EDIT:

LINQ 2 SQL is not built to recognize what you are trying to do.  When you create a foreign key, you can say it in words, the parent has many children.  The parent has the primary key, and the child table has the pointer to that primary key (the foreign key).  How you have set this up is the opposite.  In your case, you are linking not the full primary key, but instead an portion of the primary key which provides enough information to invert the relationship.

I understand your issue with needing to link the translations table from multiple tables, so I would normally argue for a redesign here.  Since it seems to do what you are looking for, I will save that for another thread.  My suggestion is to add your own property to the classes, and remove the association in the designer.

You can right click anywhere in the designer and choose View Code, then just manually add the property:

    

namespace MyNamespace
{
    // put your using statements inside the namespace for this file!!!

    partial class Item
    {
        public IQueryable<Translation> Translations
        {
            MyDataContext dc = new MyDataContext();
            return dc.Translations.Where(t => t.Id == this.translationId);
        }
    }
}