Problems when mapping database Entity Framework

Question

Problems when mapping database Entity Framework

Navigation

#1 by (1 votes)

2

I'm having trouble creating a solution for this business rule.

I currently have 1 Client, 1 Correspondent and 1 Shop. Both use the same Address table. Since the cliente , correspondente , and loja tables can not have the auto-generated Id, I have decided to use GUID to avoid duplicate error in the Endereco table. In this case the Endereco table 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 entities. Here's the template I'm trying to do:

        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 client and reference it 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 this error:

 {"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 the similar code it generates the inverted 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 someone could help me, I would appreciate it!

I made the modifications as you suggested and my mapping looks 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);

Do not need to put the dbset of Corresponding Addresses and Customer Addresses?

When I add in the Bank, it does not create anything in the Address table. Sorry for the Correspondent or Customer table. I am using this code to save in the database:

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 doing the correct map of the relationship?

c# entity-framework

asked by anonymous 27.04.2017 / 16:46

1 answer

Reorder list of products with Javascript? Insertion in Tree B with a large numbers C / C ++

score 1 · Accepted Answer

You can not 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 has Correspondente and a Loja and < Cliente , which is not what you want. p>

The correct thing is you 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 will have to make 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
{
    ...
}

E:

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 named References , with one more field called Discriminator .

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

As requested by comment, another thing you can do is derive Endereco from 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:

Endereco can not be abstract and must have DbSet itself:

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

Even with this, addresses can not be reused. To repurpose them, some associative scheme would be necessary.