0

What is the best way to set a 1:1 relationship between two entities of different types? Currently I have a SQL database of third party, where the schema is set up like this:

DECLARE TABLE Car
(
    ID as int
)

DECLARE TABLE Person
(
    ID as int,
    CarId as varchar(50)
)

So I have set up my models as code first like this

public class Car
{
    [Key, Column ("Id")]
    public int Id { get; set; }
}

public class Person
{
    [Key, Column ("Id")]
    public int Id { get; set; }

    [Column("CarId")]
    public string CarId { get; set; }

    [ForeignKey("CarId")]
    public virtual Car Car { get; set; }
}

I don't know why database was set up like this, so obviously a model like this does not work.

First issue - to be able to retrieve CarId as int, I solved by looking at this link. I set up _carId as private string and set up one more property CarId as int and I int.Parse data from _carId, which is working nicely:

public class Car
{
    [Key, Column ("Id")]
    public int Id { get; set; }
}

public class Person
{
    [Key, Column ("Id")]
    public int Id { get; set; }

    [Column("CarId")]
    protected internal string _carId { get; set; }

    [NotMapped]
    public string CarId { get => int.Parse (_carId); set => _carId = value.ToString(); }

    [Required]
    [ForeignKey("_carId")]
    public virtual Car Car { get; set; }
}

The issue that I couldn't solve is that this change is still unable to map these two entities together, so that in the code I could use it like this:

    var car = _repository.GetPerson(123).Car;

The error that occurs is this:

The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'CarId' on entity 'Person' does not match the type of property 'ID' on entity 'Car' in the referential constraint 'Person_Car'.

If I try to use CarId as ForeignKey, the error is the following:

The foreign key name 'CarId' was not found on the dependent type 'Person'. The Name value should be a comma separated list of foreign key property names.'

donatasj87
  • 760
  • 9
  • 23
  • SQL server (and thus EF) does not support that. You can use JOINs to simulate the relationships. – Steve Greene Feb 18 '20 at 19:45
  • So there's no possible workaround? Like casting it before mapping to the model? For example if you use property type as enum, it will be casted to int under the hood. – donatasj87 Feb 20 '20 at 10:56
  • There are workarounds like having a computed column. EF Core may have some new features that you could take advantage of as well. https://learn.microsoft.com/en-us/ef/core/modeling/keys?tabs=data-annotations – Steve Greene Feb 20 '20 at 19:56

0 Answers0