Inserting Data in Two Tables in Entity Framework 6.0

Question

Scenario is,

I have Many Tables in my DB . In which two tables (Student,Address) have relation ( 1 to Many) one student can have many Address.

COde is below

 private void button4_Click(object sender, EventArgs e)
        {
            Student disconnectedStudent = new Student() { StudentId="TESTId",Name = "New Student" ,Address_Id="Address1"};
            disconnectedStudent.Address = new Address() { Address_Id = "Address1", City = "City1" };

            using (var ctx = new SMSEntities())
            {
                //add disconnected Student entity graph to new context instance - ctx
                ctx.Students.Add(disconnectedStudent);

                // get DbEntityEntry instance to check the EntityState of specified entity
                var studentEntry = ctx.Entry(disconnectedStudent);
                var addressEntry = ctx.Entry(disconnectedStudent.Address);

                listBox1.Items.Add( studentEntry.State);
                listBox1.Items.Add(addressEntry.State);
                //try
                //{

                    ctx.SaveChanges();
                //}
              /*  catch(Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }*/
            }
        }

When I try to insert, ListBox Shows Entity States as "Added" but when ctx.SaveChanges(); try to execute it generate DBUpdateException

Answer 1

I think there is something miss of association properties.

one student can have many Address.

So, you entities structure has relation similar as follows.

class Student
{
   public ICollection<Address> {get;set;}
   //...other memebers
}

class Address
{
   //..other members
   public Student StudentOwner {get;set;}
}

obviously , your Entity model has some miss.

---

DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.

Answer 2

 public partial class Address
    {
        public Address()
        {
            this.Students = new HashSet<Student>();
            this.EmployeeDetails = new HashSet<EmployeeDetail>();
        }
    
        public string Address_Id { get; set; }
        public string City { get; set; }
        public string Area { get; set; }
        public string StreetAddress { get; set; }
        public string Province { get; set; }
        public string AddressType { get; set; }
        public byte[] Pic { get; set; }
    
        public virtual ICollection<Student> Students { get; set; }
        public virtual ICollection<EmployeeDetail> EmployeeDetails { get; set; }
    }

 public partial class Student
    {
        public Student()
        {
            this.AcademicInfoes = new HashSet<AcademicInfo>();
            this.Enrollments = new HashSet<Enrollment>();
        }
    
        public string StudentId { get; set; }
        public string Name { get; set; }
        public string Father_Name { get; set; }
        public Nullable<System.DateTime> DOB { get; set; }
        public string Caste { get; set; }
        public string Religion { get; set; }
        public string Gender { get; set; }
        public string FCNIC { get; set; }
        public string FEducation { get; set; }
        public string FProfession { get; set; }
        public string Address_Id { get; set; }
        public string Office_No { get; set; }
        public string Landline { get; set; }
        public string Cell { get; set; }
        public string AnyDisease { get; set; }
        public string Status { get; set; }
        public Nullable<int> Age { get; set; }
        public string Reg_No { get; set; }
        public Nullable<int> Class_Id { get; set; }
        public Nullable<System.DateTime> AdmissionDate { get; set; }
        public Nullable<int> Discount { get; set; }
        public Nullable<int> Group_Id { get; set; }
        public Nullable<int> RejectedBy { get; set; }
        public string RecjectionRemarks { get; set; }
        public Nullable<int> AcademyId { get; set; }
        public Nullable<int> ApprovedBy { get; set; }
    
        public virtual ICollection<AcademicInfo> AcademicInfoes { get; set; }
        public virtual AcademyInfo AcademyInfo { get; set; }
        public virtual Address Address { get; set; }
        public virtual ClassDetail ClassDetail { get; set; }
        public virtual ICollection<Enrollment> Enrollments { get; set; }
        public virtual GroupDetail GroupDetail { get; set; }

I think I was doing reverse. One Student can have only One Address and one Address Belongs to many STudents :D  Address is Master and Student is Detail Enitity in this scenario, im very new to EF :D.... Please correct me

Answer 3

Address's PK is FK in Student table.

Answer 4

Why did you add the HashSet<> initializing statement in entity class contruct?

I think it is not necessary.

At Runtime. EF would build a new Entity class Inherit from code first entity and override the virtual properties. 

you could run App in the debug mode, and check out the Entity instance type at runtime by yourself.

---

DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.

Answer 5

If the entity is in a disconnected state, then a connection state is created,  the entity put into a connected state and the state of the entity is set to a Modified state so that EF will persist the entity to the database.

That's why you got the DBUpdateException,  and you also got the zero rows were affected, because no entity was in a Modified state, which came from the database.

Also, you are posting to the wrong forum.

https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetentityframework

Answer 6

HashSet<> is added by EF 6.0 by default.

Answer 7

HashSet<> is added by EF 6.0 by default.

      really? why my EF 6.0 solution has not did like that? ...

     EDIT. 

     Oh. sorry, i had miss. i seen them

     but, i don't know why EF generate these. I think it is a little redundant...

   any other reason?

---

DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO.

Answer 8

DA924,

Why Should i change the state to Modified? im not updating, im inserting New data to both Master Table(Address) and to Detail Table(Student) at the same time. Entity state should be Added,

Answer 9

Methew,

I was Wrong,One Student can only have one Address . Address can be belong to many students( its silly but it exist ,I will modify to one to one relation instead of one to Many).tell me how to add Data in this Scenario

Answer 10

DA924,

Why Should i change the state to Modified? im not updating, im inserting New data to both Master Table(Address) and to Detail Table(Student) at the same time. Entity state should be Added,

Did it work? And why are you getting the exception if what you say is supposed to work?

var student = New Student();

set some property values on student

db.Student.Add(student);

 Don't set any properties on student and try to save it and see if it works.

Answer 11

One Address to Many Student.

it is not wrong..., you should not modify it to one to one.

I doubt  there is another reason cause the problem.

could you pick out the inner exception of the DbUpdateException to check more exception details?

just Adding new entity is a simple case... i don't believe the reason is much complicated.

maybe there some field constrains like NOT NULL cause the issue..

---

DON'T TRY SO HARD,THE BEST THINGS COME WHEN YOU LEAST EXPECT THEM TO

Answer 12

yeah it is works when I just add student. I have done Master detail.. See below Code

 private void button4_Click(object sender, EventArgs e)
        {
           // Student disconnectedStudent = new Student() { StudentId="TESTId",Name = "New Student" ,Address_Id="Address1"};
            Address addr= new Address() { Address_Id = "Address1", City = "City1" };
           Student st= new Student() { StudentId="TESTId",Name = "New Student"};
           addr.Students.Add(st);
            using (var ctx = new SMSEntities())
            {
                //add disconnected Student entity graph to new context instance - ctx
                ctx.Addresses.Add(addr);

                // get DbEntityEntry instance to check the EntityState of specified entity
               // var studentEntry = ctx.Entry(addr);
                var addressEntry = ctx.Entry(addr);

               // listBox1.Items.Add( studentEntry.State);
                //listBox1.Items.Add(addressEntry.State);
                try
                {

                    ctx.SaveChanges();
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }
        }

as Address is Master Table, So Address is Parent. Adding Students in Address and then Address must added to DBContext. and When DBContext.SaveChanges(); Executed it notice that Address and all Students Entities(may be more than one) have Added State, So it will Execute Insert comand on database. First it will insert Address then all students.

Answer 13

disconnectedStudent.Address = new Address() { Address_Id = "Address1", City = "City1" };

Studentst= new Student() { StudentId="TESTId",Name = "New Student"};

What is that? And why is that one goes away from using an Int Identity column as the Primary key letting the database engine set the primary key itself on and insert/add and letting EF populate the foreign key to child objects? 

Why are you doing this?

Answer 14

I have some tables  with PK as vacrhar type because its my requirment. Address PK Would be like this

ADD0120160001

in which ADD is prefix,01 is for month and 2016 is for year and 0001 is for number,0002 will be for second entry in particular month with particular year. I hope you got it

And Student Id will be like this.

STD[MON][Year][0001 to 9999]

Answer 15

I have some tables  with PK as vacrhar type because its my requirment. Address PK Would be like this

ADD0120160001

in which ADD is prefix,01 is for month and 2016 is for year and 0001 is for number,0002 will be for second entry in particular month with particular year. I hope you got it

And Student Id will be like this.

STD[MON][Year][0001 to 9999]

Myself, I would have stopped that dead in its tracks. There is no reason that can't be some alternative identification column with an Index on it in the table for querying on the column with it basically being a static column once set. 

But behind the whole thing,  the primary-key on all tables would be an Int Identity column and letting the DB engine assign the primary key and letting EF by itself populate foreign-key property in child objects by itself.

Then on a Linq query you can query on the  on parent and do an Include for a child objects easily.

The fact that you instantiated the object as new sets the Int primary-key property = 0 that tells EF to do an insert of the object into the database. Otherwise, if you got an existing object, the primary-key property is > 0, it would tell EF to go find the record and update it based on the object's content.

That's why I will always take DB First over Code First along with DB First builds the objects itself based on the DB table schemas, and I don't have to do it. 

Code First? <pfft>

Answer 16

well im getting confuse. Im doing in DB First.so you mean I must use Int as PK type?

Answer 17

well im getting confuse. Im doing in DB First.so you mean I must use Int as PK type?

That makes it even worst then that you are using DB First,  and you are doing this. You should be using an  Int Identity column as the primary-key and let the DB and EF take care of primary and foreign-key population with the DB tables and primary and foreign key properties of the entities.

Take note, when you save the entity on an insert/add, EF populates the value of the primary-key Identity column back to the entity primary-key property during the save process. So you can hold the key in a variable. Then you set the child entity foreign-property to the save value and save the child individually.

Or you can add all new children entities into a new parent entity and save the parent, and EF take care of all key property  population  by itself. You add a new child  into an existing parent and do the save, then EF will take care of key population in the new child object by itself.  Or you can populate the child foreign-key property manually using the parents primary-key property  on a manual individual save of the child.  

  

Answer 18

As I said its my Requirement... PK pattern as I described is Easy to remember, would be handy to investigate entry date easily, and most important its range will be for life time, no round over or Cycles in range....So according to u , I should not include requirment like this? Its Powerfull mechanism PK pattern in Bussiness Industry

Answer 19

This has to be a first.

I feel stronger words might be called for.

.

NEVER use a meaningful or natural key.

ALWAYS use a GUID or an autogenerated int.

( Also known as a surrogate key. )

No matter how much you think that some data will never change or some rule will never be broken.

It will happen.

Inevitably, that key you assumed would be unique will end up not being unique.

That value that was never going to change will have to change.

If you want month or whatever as well, put that in another key.

---

Hope that helps.

Technet articles: WPF: Layout Lab; All my Technet Articles

Answer 20

Inevitably, that key you assumed would be unique will end up not being unique.

---

Its will be Unique. Its will be created by a Procedure. Procedure will fetch Max Value of PK and then generate last four alphabets based on that along With Month,Year. Simply Saying,Its Mannual creation of  PK with Unique Pattern.Its range will be unlimited

Answer 21

As I said its my Requirement... PK pattern as I described is Easy to remember, would be handy to investigate entry date easily, and most important its range will be for life time, no round over or Cycles in range....So according to u , I should not include requirment like this? Its Powerfull mechanism PK pattern in Bussiness Industry

This must be your first encounter with any ORM period. Your requirements do not work well with any ORM, like nHibernate, EF or others. Your requirements don't work well look at the trouble you are already having with DB Frist being the easiest to use out of the 3 EF database types.

I suggest that you stopping using EF,  and instead, use SQL Command objects,  inline T-SQL or sprocs parametrized and custom objects like DTO(s) in a List<T>.

You can lead a horse to the water, but one cannot make the horse drink. :)

 

Answer 22

Yeah I got You, Thanks, Now Changed my All PKs to Type Int. now it will be handle by EF automatically? just making PK Type Int?

Answer 23

Yeah I got You, Thanks, Now Changed my All PKs to Type Int. now it will be handle by EF automatically? just making PK Type Int?

https://en.wikipedia.org/wiki/Identity_column

https://msdn.microsoft.com/en-us/library/ms177173.aspx?f=255&MSPPError=-2147217396

EF is in charge of nothing. The DB engine is in charge. EF is in charge of EF matters of getting the primary auto incremented primary assigned by the DB engine populated back to all parent object property and its children foreign key property on inserts of a new object into the database that is dependent upon foreign-key relationship using an Identity auto incremented column as the primary key of a table. 

Your lack of knowledge about this kind of leads me to think that you don't know how to set up table foreign key constraints at the DB table schema level, which the EF DB First Model is derived from what is sees in the DB table schemas at Model creation time.     

If you don't know how to  administer a MS SQL Server database and tables for foreign-key constraints, then you need to stop and figure it out,  and the you point the DB First Model Creation Wizard at it so that the Wizard  knows how to setup the virtual model correctly.

The below is a DB First Tutorial, which has the C# tutorial link at the top, but it has lost the pictures the VB tutorial still has. I suggest that you stop and take the tutorial to figure out what is going on.

You come in here talking all kind of smack when you don't seem to know some of the basics of DB administration or how to use an ORM effectively.

http://www.vbforums.com/showthread.php?540421-Tutorial-An-Introduction-to-the-ADO-NET-Entity-Framework

Answer 24

Yeah This is my first project Based on SQL Server and .Net WCF. I worked in ORacle

Answer 25

Yeah This is my first project Based on SQL Server and .Net WCF. I worked in ORacle

It's no excuse as I work MS SQL Server and Oracle, and when I came over to Oracle, my knowledge of SQL Server allowed me to work with Oracle effectively. Oracle tables have foreign-key constraints too and auto incremented  generated numbers for a numeric column.

So you have no excuse here none. And WCF and an ORM is another learning curve you'll have to deal with too it's no cake-walk.