Problems when mapping Entity Framework database

Question

I’m having trouble creating a solution to this business rule.

I currently have 1 Customer, 1 Correspondent and 1 Store. Both use the same address table. Like the tables cliente, correspondente and loja cannot have the Id self manageable, I decided to use GUID to avoid duplication error in the table Endereco. In this case the table Endereco should look like this:

EnderecoId - ReferenceId          - Logradouro
1          - GuidDoCorrespondente - Rua tal tal tal
2          - GuidDaLoja           - Rua tal tal tal
3          - GuidCliente          - Rua tal tal tal

But I’m having trouble mapping the entities. Follow the model I’m trying to make:

        EnderecoMap() 
        {

        HasKey(x => x.EnderecoId);

        Property(x => x.Logradouro)
            .IsRequired()
            .HasMaxLength(60);

        Property(x => x.Numero)
            .IsRequired();

        HasRequired(x => x.Correspondente)
            .WithMany(x => x.Enderecos)
            .HasForeignKey(x => x.ReferenceId);

        HasRequired(x => x.Cliente)
            .WithMany(x => x.Enderecos)
            .HasForeignKey(x => x.ReferenceId);

        HasRequired(x => x.Loja)
            .WithMany(x => x.Enderecos)
            .HasForeignKey(x => x.ReferenceId);
        }

I always get the following errors:

If I add a customer and reference like this:

public void AdicionandoLivro()
    {
        var cliente = new Cliente("Default",   
                  "[email protected]");
        cliente.AddEndereco(new Endereco(cliente.ClienteId, "Rua"));


        _clienteRepository.Add(cliente);
        _uow.Commit();
    }

I get that mistake:

 {"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_dbo.Endereco_dbo.Correspondente_ReferenceId\". The conflict occurred in database \"LivrariaEF\", table \"dbo.Correspondente\", column 'CorrespondenteId'.\r\nThe statement has been terminated."}`

When I add a Correspondente with similar code generates me reversed error:

{"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_dbo.Endereco_dbo.Cliente_ReferenceId\". The conflict occurred in database \"LivrariaEF\", table \"dbo.Cliente\", column 'ClienteId'.\r\nThe statement has been terminated."}

If anyone could help me, I’d really appreciate it!

---

I made the modifications as you suggested and my mapping was like this:

ClienteMap()
HasMany(x => x.EnderecosCliente)
            .WithOptional(x => x.Cliente)
            .HasForeignKey(x => x.ClienteId);

CorrespondenteMap()
HasMany(x => x.EnderecosCorrespondente)
            .WithOptional(x => x.Correspondente)
            .HasForeignKey(x => x.CorrespondenteId);

• No need to put the corresponding Address dbset and Addressclient?

When I add it to the Database, it creates nothing in the Address table. Only in the Corresponding table or Client table. I’m using this code to save in the bank:

var correspondente = new Correspondente()
        {
            CorrespondenteId = Guid.NewGuid(),
            Nome = "Walmart",
            EnderecosCorrespondente = new List<EnderecoCorrespondente>()
            {
                new EnderecoCorrespondente() { Logradouro = "Rua 1",  Numero = "1" },
                new EnderecoCorrespondente() { Logradouro = "Rua 2", Numero = "2" }
            }
        };

        _correspondenteRepository.Add(correspondente);
        _uow.Commit();

Am I making correct relationship map?

Answer 1

You can’t do this:

    HasRequired(x => x.Correspondente)
        .WithMany(x => x.Enderecos)
        .HasForeignKey(x => x.ReferenceId);

    HasRequired(x => x.Cliente)
        .WithMany(x => x.Enderecos)
        .HasForeignKey(x => x.ReferenceId);

    HasRequired(x => x.Loja)
        .WithMany(x => x.Enderecos)
        .HasForeignKey(x => x.ReferenceId);

This requires that Endereco have a Correspondente and one Loja and one Cliente, what is not what you want.

The right thing is for you to do the following:

public class Endereco
{
    [Key]
    public Guid EnderecoId { get; set; }
    public int? CorrespondenteId { get; set; }
    public int? LojaId { get; set; }
    public int? ClienteId { get; set; }

    ...

    public virtual Correspondente Correspondente { get; set; }
    public virtual Loja Loja { get; set; }
    public virtual Cliente Cliente { get; set; }
}

Now, if the idea is to have only one foreign key, you’ll have to do Correspondente, Loja and Cliente inherit from the same ancestor:

public class Reference
{
    [Key]
    public Guid ReferenceId { get; set; }

    ...
    public virtual ICollection<Endereco> Enderecos { get; set; }
}

public class Cliente: Reference
{
    ...
}

public class Correspondente: Reference
{
    ...
}

public class Loja: Reference
{
    ...
}

And:

public class Endereco
{
    [Key]
    public Guid EnderecoId { get; set; }
    public Guid ReferenceId { get; set; }

    ...

    public virtual Reference Reference { get; set; }
}

The problem is that the three entities will be in a table called References, with a field more called Discriminator.

This is a limitation of SQL Server (and relational databases in general): not the Entity Framework. For proper referential integrity (with key conference), Endereco need to reference only one table. Foreign keys cannot be assigned to three different tables.

---

As prompted by comment, another thing you can do is derive Endereco in three different entities:

public class Endereco
{
    [Key]
    public Guid EnderecoId { get; set; }

    ...
}

public class EnderecoCorrespondente : Endereco
{
    public int? CorrespondenteId { get; set; }

    ...
    public virtual Correspondente Correspondente { get; set; }
}

public class EnderecoLoja : Endereco
{
    public int? LojaId { get; set; }

    ...
    public virtual Loja Loja { get; set; }
}

public class EnderecoCliente : Endereco
{
    public int? ClienteId { get; set; }

    ...
    public virtual Cliente Cliente { get; set; }
}

And the inverse relationship:

public class Cliente
{
    ...
    public virtual ICollection<EnderecoCliente> EnderecosCliente { get; set; }
}

public class Correspondente
{
    ...
    public virtual ICollection<EnderecoCorrespondente> EnderecosCorrespondente { get; set; }
}

public class Loja
{
    ...
    public virtual ICollection<EnderecoLoja> EnderecosLoja { get; set; }
}

This makes the address table unified. Two points here:

1. Endereco can’t be abstract and must have his own DbSet:

   public DbSet<Endereco> Enderecos { get; set; }
   

2. Even with this, addresses cannot be reused. To re-approve them, it would be necessary some associative scheme.