EF6 Code First Relation between entities

Asked

Viewed 747 times

4

I am trying to create a Condominium data model in ASP.NET with SQL Server. I stumbled on the creation of the interface proprietario(1) with the fracao(many).

The error returned is:

The INSERT statement conflicted with the FOREIGN KEY Constraint "Fk_dbo.Fracao_dbo.Proprietario_proprietarioid". The Conflict occurred in database "Webcond", table "dbo.Proprietario", column 'Proprietarioid'.

My classes:

[Table("Fracao")]
public class Fracao
{


    [ScaffoldColumn(false)]
    public int FracaoID { get; set; }

    public int? ProprietarioID { get; set; }
    public virtual Proprietario Proprietario { get; set; }

    [Required]
    public int CondominioID { get; set; }
    public virtual Condominio Condominio { get; set; }

    [Required]
    public int ZonaID { get; set; }
    public virtual Zona Zona { get; set; }

    [Required, StringLength(4), Display(Name = "Letra")]
    public string Letra { get; set; }

    [Required, Display(Name = "Área")]
    public decimal Area { get; set; }

    [Required, Display(Name = "Permilagem")]
    public decimal Permilagem { get; set; }

    [Required, StringLength(4), Display(Name = "Piso")]
    public string Piso { get; set; }

    [Required, StringLength(10), Display(Name = "Porta")]
    public string Porta { get; set; }

}

[Table("Proprietario")]
public class Proprietario
{

    [ScaffoldColumn(false)]
    public int ProprietarioID { get; set; }

    [Required, StringLength(255), Display(Name = "Nome")]
    public string Nome { get; set; }

    [Required, StringLength(500), Display(Name = "Morada"), DataType(DataType.MultilineText)]
    public string Morada { get; set; }

    [Required, StringLength(30), Display(Name = "CPostal")]
    public string CPostal { get; set; }

    [Required, StringLength(100), Display(Name = "Localidade")]
    public string Localidade { get; set; }

    [StringLength(10), Display(Name = "Telefone")]
    public string Telefone { get; set; }

    [StringLength(10), Display(Name = "Telemovel")]
    public string Telemovel { get; set; }

    [DataType(DataType.EmailAddress), Display(Name = "Email")]
    public string Email { get; set; }

    [StringLength(10), Display(Name = "Contribuinte")]
    public string Contribuinte { get; set; }

    public virtual ICollection<Fracao> Fracoes { get; set; }
}

I tried to create the entities the way I created others that are fine, but these.

It seems to me that the problem is related to the Cascade delete, and that I have to create the appropriate exceptions, but I don’t know which ones.

  • You have no mapping class, all mapping is done in these two classes ?

  • I have no mapping class. This is the solution?

  • The owner you are trying to insert in the Fraction table, exists in the Owner table?

  • 1

    @Betasystems-Rodrigoduarte No Code First a scheme called convention over configuration so you don’t need another class for mapping.

  • @Jhonatas Kleinkauff In the BD boot class I have the data I think is necessary. Ex: new Fracao { Fracaoid = 1, Condominioid=1, Zonaid=2, Proprietarioid=1, Letter = "A", Piso="-4", Porta="GAR 19", Area=34.7m, Permilage=4.6m, }

  • Owner: Proprietarioid= 1, Name= "Proprietary name", Address= "Prop address", Cpostal= "1000-285", Locality= "Lisboa", Telefone= "", Telemovel= "", Email= "", Contributor= "", Contributor= "", ; },

  • @Vitorcanova, it is not a rule that, can be done the mapping.

  • @Asoares, it is not necessary, was another question to understand how its structure is. All the other classes are exactly like this, right ?

  • @Yes, I only put it because I thought you were looking for that link file when using the first Entity.

Show 4 more comments

2 answers

2

In class Proprietario, puts a constructor that initializes the ICollection<Fracoes>. For example:

public class Proprietario {

   public Proprietario( ) {
      Fracoes = new List<Fracao>( );
   }

   ...

   public virtual ICollection<Fracao> Fracoes { get; set; }

}

Thus, correctly configures the One-To-Many relationship between models. There is an article here in English which also explains.

Updating

I created my own Code-first and copied your classes the way they are, with my modification (I didn’t include Zona or Condominio only). In the end, I had a class like this:

Fraction

[Table( "Fracao" )]
public class Fracao
{
    [ScaffoldColumn( false )]
    public int FracaoID { get; set; }

    public int? ProprietarioID { get; set; }
    public virtual Proprietario Proprietario { get; set; }

    [Required, StringLength( 4 ), Display( Name = "Letra" )]
    public string Letra { get; set; }

    [Required, Display( Name = "Área" )]
    public decimal Area { get; set; }

    [Required, Display( Name = "Permilagem" )]
    public decimal Permilagem { get; set; }

    [Required, StringLength( 4 ), Display( Name = "Piso" )]
    public string Piso { get; set; }

    [Required, StringLength( 10 ), Display( Name = "Porta" )]
    public string Porta { get; set; }
}

Proprietary

[Table( "Proprietario" )]
public class Proprietario
{
    public Proprietario( )
    {
        Fracoes = new List<Fracao>( );
    }

    [ScaffoldColumn( false )]
    public int ProprietarioID { get; set; }

    [Required, StringLength( 255 ), Display( Name = "Nome" )]
    public string Nome { get; set; }

    [Required, StringLength( 500 ), Display( Name = "Morada" ), DataType( DataType.MultilineText )]
    public string Morada { get; set; }

    [Required, StringLength( 30 ), Display( Name = "CPostal" )]
    public string CPostal { get; set; }

    [Required, StringLength( 100 ), Display( Name = "Localidade" )]
    public string Localidade { get; set; }

    [StringLength( 10 ), Display( Name = "Telefone" )]
    public string Telefone { get; set; }

    [StringLength( 10 ), Display( Name = "Telemovel" )]
    public string Telemovel { get; set; }

    [DataType( DataType.EmailAddress ), Display( Name = "Email" )]
    public string Email { get; set; }

    [StringLength( 10 ), Display( Name = "Contribuinte" )]
    public string Contribuinte { get; set; }

    public virtual ICollection<Fracao> Fracoes { get; set; }
}

And I ended up with tables and columns like this:

exemplo de tabelas e colunas geradas pelo Code-First do Entity Framework 6

  • Matt: I did as I was in the article and the tables are all populated with data, however SQL Server adds a new column to the Owner: Fracoes_fracaoid being always Null

  • @Asoares now don’t know exactly what’s going on ... I updated my response with my experiences with their own classes. It all worked out.

  • Your answer is also correct. What happened was that I was reviewing the class data (93 fractions and 61 owners) and discovered a fraction that had a Proprietarioid that was not in any of the owners. It was my mistake, but I had reviewed all the data and I had escaped. Thank you!

  • Great! It’s good that you managed to find the "root" of the problem, as they say here :)

1


You could try it with the following, code:

[Table("Fracao")]
    public class Fracao
    {


        [ScaffoldColumn(false)]
        public int FracaoID { get; set; }

        public int? ProprietarioID { get; set; }

        [InverseProperty("ID")]
        [ForeignKey("ProprietarioID")]
        public virtual Proprietario Proprietario { get; set; }

        [Required]
        public int CondominioID { get; set; }
        public virtual Condominio Condominio { get; set; }

        [Required]
        public int ZonaID { get; set; }
        public virtual Zona Zona { get; set; }

        [Required, StringLength(4), Display(Name = "Letra")]
        public string Letra { get; set; }

        [Required, Display(Name = "Área")]
        public decimal Area { get; set; }

        [Required, Display(Name = "Permilagem")]
        public decimal Permilagem { get; set; }

        [Required, StringLength(4), Display(Name = "Piso")]
        public string Piso { get; set; }

        [Required, StringLength(10), Display(Name = "Porta")]
        public string Porta { get; set; }

    }

Because with the [ForeignKey("ProprietarioID")], you speak to the note that the FK is defined within it.

UPDATING

Errata, I wasn’t aware of the fact that your identifier has the same name as the foreign key, so please remove the annotation [InverseProperty("ID")].

UPDATE 2

With the following code, which I replicated entirely on my machine, it worked perfectly, try to do the same, as a test, as a new project:

Dbcontext class:

public class TesteContext : DbContext
{
    public DbSet<Fracao> Fracoes { get; set; }
    public DbSet<Proprietario> Proprietarios { get; set; }
}

Class of the Fraction:

[Table("Fracao")]
public class Fracao
{
    [ScaffoldColumn(false)]
    public int FracaoID { get; set; }

    public int? ProprietarioID { get; set; }

    [ForeignKey("ProprietarioID")]
    public virtual Proprietario Proprietario { get; set; }

    [StringLength(4), Display(Name = "Letra")]
    public string Letra { get; set; }

    [Display(Name = "Área")]
    public decimal Area { get; set; }

    [Display(Name = "Permilagem")]
    public decimal Permilagem { get; set; }

    [StringLength(4), Display(Name = "Piso")]
    public string Piso { get; set; }

    [StringLength(10), Display(Name = "Porta")]
    public string Porta { get; set; }

}

Class of the Owner:

[Table("Proprietario")]
public class Proprietario
{
    [ScaffoldColumn(false)]
    public int ProprietarioID { get; set; }

    [StringLength(255), Display(Name = "Nome")]
    public string Nome { get; set; }

    [StringLength(500), Display(Name = "Morada"), DataType(DataType.MultilineText)]
    public string Morada { get; set; }

    [StringLength(30), Display(Name = "CPostal")]
    public string CPostal { get; set; }

    [StringLength(100), Display(Name = "Localidade")]
    public string Localidade { get; set; }

    [StringLength(10), Display(Name = "Telefone")]
    public string Telefone { get; set; }

    [StringLength(10), Display(Name = "Telemovel")]
    public string Telemovel { get; set; }

    [DataType(DataType.EmailAddress), Display(Name = "Email")]
    public string Email { get; set; }

    [StringLength(10), Display(Name = "Contribuinte")]
    public string Contribuinte { get; set; }

    public virtual ICollection<Fracao> Fracoes { get; set; }
}

And the codes of a console program:

using (var db = new TesteContext())
{
    // Create and save a new Blog 
    Console.Write("Enter a name for a new Blog: ");
    var name = Console.ReadLine();

    var proprietario = new Proprietario { Email = "oi" };
    proprietario = db.Proprietarios.Add(proprietario);
    db.SaveChanges();

    var fracao = new Fracao { Area = 1, Proprietario = proprietario };
    db.Fracoes.Add(fracao);
    db.SaveChanges();

    // Display all Blogs from the database 
    var query = from b in db.Fracoes
                orderby b.Area
                select b;

    Console.WriteLine("All blogs in the database:");
    foreach (var item in query)
    {
         Console.WriteLine(item.Area);
    }

    Console.WriteLine("Press any key to exit...");
    Console.ReadKey();
}

With this code, you can enter a Fraction and an Owner, making the link correctly, if you want to update, you will have to do the following:

var fracao = new Fracao { Area = 1, Proprietario = null, ProprietarioId = proprietario.ProprietarioId };

Try again with these codes and comment if you got it.

  • Did not result: The Inversepropertyattribute on Property 'Proprietario' on type 'Webcond.Models.Fracao' is not Valid. The Property 'ID' is not a Valid navigation Property on the Related type 'Webcond.Models.Proprietario'. Ensure that the Property exists and is a Valid Reference or Collection navigation Property

  • Sorry, I didn’t notice the fact that your ID is the same name as FK... Take out the "Inverseproperty," please.

  • @Rodrigo: With the [Foreignkey("Proprietarioid")] and without the [Inverseproperty("ID")] does not give a Constraint error, but does not put data in the tables

  • @Rodrigo: The result is the same: The INSERT statement conflicted with the FOREIGN KEY Constraint "Fk_dbo.Fracao_dbo.Proprietario_proprietarioid". The Conflict occurred in database "Webcond", table "dbo.Proprietario", column 'Proprietarioid'. The statement has been terminated.

  • I will try to do the opposite: create the tables in SQL Server populate them with data and then with EF create the entities from SQL Server. I’ll let you know the result.

  • @Asoares, this is very strange... Do this and let me know, if you want, we can treat direct by email ([email protected]). Hugs

  • @Rodrigo: Very strange, so much so that I was validating all the data (93 Fractions and 61 Prop) and found the problem in a fraction that the Prop did not contain. Nonsense :|. But it served to know a little better the EF.

  • I’m glad you decided! :)

Show 3 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.