CODEWITHSUNDEEP
entity-frameworkentity-framework-4ef-code-first
will
will

Reputation: 1012

EF CodeFirst - One-to-One relationship with non nullable columns - For Experts

My database has a convention from a third party company, that "all columns in the database must be 'not null'".

Now I'm mapping all tables using EFCodefirst and I ran into a problem.

For example, I have an entity SA1 that has a one-to-one relation to a SA3 entity, and I would like to add a new SA1 with its a1_vend property having an empty string.

What I did to solve this problem was add a SA3 entity with an empty string in the PK, but I didn't like this approach. I'd like a better solution to my problem.

My EFCodefirst classes:

[ComplexType]
public class Endereco
{
    public string Logradouro { get; set; }
    public string Numero { get; set; }
    public string CEP { get; set; }
}

public class SA3
{
    public string Codigo { get; set; }
    public string Nome { get; set; }
}


public class SA1
{
    public string Codigo { get; set; }
    public string Nome { get; set; }
    public Endereco Endereco { get; set; }
    public Endereco EnderecoCobranca { get; set; }
    public bool IsDeleted { get { return false; } }

    public string a1_vend { get; set; }
    public SA3 Vendedor { get; set; }

    public SA1()
    {
        Endereco = new Endereco();
        EnderecoCobranca = new Endereco();
    }
}


public class SA3Map : EntityTypeConfiguration<SA3>
{
    public SA3Map()
    {


        ToTable("sa3010");

        HasKey(x => x.Codigo);

        Property(x => x.Codigo)
            .HasColumnName("a3_cod");

        Property(x => x.Nome)
            .HasColumnName("a3_nome");
    }
}

public class SA1Map : EntityTypeConfiguration<SA1>
{
    public SA1Map()
    {
        ToTable("sa1010");

        HasKey(x => x.Codigo);

        Property(x => x.Codigo)
            .HasColumnName("a1_cod")
            .IsRequired();
        Property(x => x.Nome)
            .HasColumnName("a1_nome")
            .IsRequired();
        Property(x => x.Endereco.Logradouro)
            .HasColumnName("a1_end")
            .IsRequired();
        Property(x => x.Endereco.Numero)
            .HasColumnName("a1_num")
            .IsRequired();
        Property(x => x.Endereco.CEP)
            .HasColumnName("a1_cep")
            .IsRequired();
        Property(x => x.EnderecoCobranca.Logradouro)
            .HasColumnName("a1_endcob")
            .IsRequired();
        Property(x => x.EnderecoCobranca.CEP)
            .HasColumnName("a1_cepcob")
            .IsRequired();
        Property(x => x.EnderecoCobranca.Numero)
            .HasColumnName("a1_numcob")
            .IsRequired();
        Property(x => x.a1_vend)
            .IsRequired();

        HasRequired(x => x.Vendedor)
            .WithMany()
            .HasForeignKey(x => new { x.a1_vend })
            .WillCascadeOnDelete(false);
    }
}

My sample program:

class Program
{
   static void Main(string[] args)
    {
        MyContext ctx = new MyContext();
        var novoVendedor = new SA3()
        {
            Codigo = "",
            Nome = "Empty, don´t remove this row"
        };
        ctx.Vendedores.Add(novoVendedor);

        var novoCliente = new SA1()
        {
            Codigo = "000001",
            a1_vend = "", //I can´t use null here because my database convention
            Endereco = new Endereco() { Numero = "99", CEP = "13280000", Logradouro = "Rua Teste" },
            Nome = "Cliente S/A",
            EnderecoCobranca = new Endereco { CEP = "13444999", Numero = "S/N", Logradouro = "Rua Cobranca" }
        };
        ctx.Clientes.Add(novoCliente);
        ctx.SaveChanges();



    }
}

Upvotes: 0

Views: 272

Answers (1)

sazh
sazh

Reputation: 1842

IF the following are true:

  1. SA1 -> SA3 is a one-to-one relationship.
  2. The a1_vend column is in your sa1010 table.
  3. You cannot make the a1_vend column nullable

you will not be able to create an SA1 object without having an SA3 object to reference first.

If you cannot make the a1_vend column nullable, your other option is to remove the a1_vend column from the sa1010 table and create a mapping table that maps SA1 objects to SA3 objects (would just need two columns: SA1.Codigo and a1_vend which I'm guessing is the same as SA3.Codigo)

Then you would change your SA1Map as follows:

        ...
        Property(x => x.EnderecoCobranca.Numero)
            .HasColumnName("a1_numcob")
            .IsRequired();
        //Property(x => x.a1_vend) // removing this
            //.IsRequired();

        HasRequired(x => x.Vendedor)
            .WithMany()
            .Map(m =>
                    {
                        m.ToTable("sa1010sa3010Map");
                        m.MapLeftKey("sa1_Codigo");
                        m.MapRightKey("sa3_Codigo");
                    });
    }

Upvotes: 1

Related Questions