EF relationship from Foxpro migration, with composite key

Question

I’m migrating a legacy Visualfox system on . Net where the tables were made all without relationship, in ADO, without Foreign key in relationships. Just comparing a string of the name "Type" to differentiate if the record in a Table, for example: Address belongs to Correspondent, Store or Customer.

The system is very large and there are several relationships similar to this for example: Store and Correspondent have a table Contact also relating the ID and differentiating by type(string).

It is very difficult to mirror this rule in EF (if possible, because I am a beginner and I am still learning). The table looks like this:

EnderecoId - ReferenceId          - Tipo                 - Logradouro
1          - 1                    - Loja                 - Rua tal tal tal
2          - 1                    - Correspondente       - Rua tal tal tal
3          - 1                    - Correspondente       - Rua tal tal tal
4          - 1                    - Cliente              - Rua tal tal tal

Is it possible to make these rules using a Type string as a way to differentiate addresses? If it is not possible, it would be right to use Composite Key in this case?

Answer 1

The Entity Framework natively does not have this ability to intuit the type of the related table automatically, by using a column, for example.

If I were you, I would do a job on SQL to improve this table. Remembering the approach 1, add 3 new columns to your table:

ALTER TABLE Enderecos
ADD LojaId int null,
    ClienteId int null,
    CorrespondenteId int null;

Faria 3 UPDATEs to popular these columns:

UPDATE Enderecos
SET LojaId = ReferenceId
WHERE Tipo = 'Loja' ;

UPDATE Enderecos
SET ClienteId = ReferenceId
WHERE Tipo = 'Cliente' ;

UPDATE Enderecos
SET CorrespondenteId = ReferenceId
WHERE Tipo = 'Correspondente' ;

We will therefore have:

EnderecoId + ReferenceId  + Tipo            + Logradouro      + LojaId + ClienteId + CorrespondenteId
1          | 1            | Loja            | Rua tal tal tal | 1      | null      | null
2          | 1            | Correspondente  | Rua tal tal tal | null   | 1         | null
3          | 1            | Correspondente  | Rua tal tal tal | null   | 1         | null
4          | 1            | Cliente         | Rua tal tal tal | null   | null      | 1

Only with this the Entity Framework is able to understand the relationship by approach 1 (using inheritance).