locked
Entity Framework 7 Transaction Scope RRS feed

  • Question

  • Hi,

    I have 2 tables called user and person. PersonId is a PK and Identity Column in Person Table. This person ID is a FK in User table.

    1. I want to insert data into Person Table and User Table. I want to define transaction scope such that if one fails it has to roll back.

    2. Currently I am doing 2 database interactions, one to insert data to person table and insert the data to User table. Below is the code for that. Is this correct way or is there any other suggest way which supports concurrency and multi thread.

    try
                    {
    
                        var personObj = new Person
                        {
                            Email = item.Email,
                            Address = item.Address,
                            FullName = item.FullName,
                            Users = null
    
                        };
    
                        DatabaseContext.context.People.Add(personObj);
    
    
                        DatabaseContext.context.SaveChanges();
    
    
                        DatabaseContext.context.user.Add(new Entities.Account.User { UserID=item.UserID, PersonID = personObj.PersonID,Lastchange=DateTime.Now });
    
                        DatabaseContext.context.SaveChanges();
                        ts.Complete();
    
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                        
                    }

    Sunday, February 7, 2016 11:00 PM

Answers

  • Yes its using Identity Auto Incremented integer value as primary for this person and User tables.

    Thanks,

    Ramya.

    What would you do if EF was not there and you where using T-SQL in a sproc that was doing this for you?

    1) add the parent object via EF that returns the primary-key back to the object when inserted.

    2) take the primary-key from the parent object and hold on to it in variable.  

    3) add each child object populating the child's foreign-key property from the parent's primary-key variable. 

    4) Put the whole thing in a Transaction.Scope.

    Just do it yourself if it's too much trouble.


    Monday, February 15, 2016 5:35 PM

All replies

  • The Person object should have a User object defined with in it. 

    var po = new Person();

    po.Users = new List<User>;

    populate Person object properties.

    var uo = new User()

    populate uo object properties.

    po.Users.Add(uo);

    Save object

    EF user Transaction automatically so either it is all successful  or rollback. Also EF will take care of assigning primary and foreign key properties by itself.   

    Monday, February 8, 2016 1:39 AM
  • Thank you. Its worked for me.

    I am continuously getting the below error. DO you know why? I am using single ton context to make it thread safe.

    An exception of type 'System.InvalidOperationException' occurred in EntityFramework.Core.dll but was not handled in user code

    Additional information: The connection was not closed. The connection's current state is connecting.


    Monday, February 8, 2016 10:05 AM

  • I execute the above code you suggested and tried to retrieve the person object - then I am getting the above error connection state was not closed. The connection's current state is connecting.


    Monday, February 8, 2016 10:16 AM

  • I execute the above code you suggested and tried to retrieve the person object - then I am getting the above error connection state was not closed. The connection's current state is connecting.



    The best I can tell is close the connection after the insert. The ID of the Person object is populated back to Person object's primary-key property after the insert. So you can get the ID just after the insert, save it and return it out of a method as a return value to be used in subsequent code in another method to open the connection and get the Person object that was just inserted by its ID. 
    Monday, February 8, 2016 2:08 PM
  • Hi PradyuS,

    The lifetime of the context begins when the instance is created and ends when the instance is either disposed or garbage-collected. Use using if you want all the resources that the context controls to be disposed at the end of the block. When you use using, the compiler automatically creates a try/finally block and calls dispose in the finally block.

    I modify your code snippet as below for your reference.

    var personObj = new Person
                {
                    Email = item.Email,
                    Address = item.Address,
                    FullName = item.FullName,
                    Users = null
                };
    
                using (var db = new Demo()) //demo is your dbcontext
                {
                    db.People.Add(personObj);
                    db.user.Add(new Entities.Account.User { UserID = item.UserID, PersonID = personObj.PersonID, Lastchange = DateTime.Now });
                    db.SaveChanges();
                }
    

    Best regards,

    Cole Wu
    Tuesday, February 9, 2016 9:59 AM
  • This wont work because I am using single Ton dbcontex

    using (var db = new Demo()) //demo is your dbcontext

    it should be like 

    Demo.Person.Add(personObj) //demo is your dbcontext
    Wednesday, February 10, 2016 11:40 AM
  • Hi,

    Can I know how we can update the user table with personID. The above code just creates a new row in user table.

    I did like below but we are doing savechanges twice. Please suggest good way

                        var user= DatabaseContext.context.User.Where(x => x.UserID == item.UserID).Select(x => x).FirstOrDefault();
    
                        
                            var po = new Person
                            {
                               Email = item.Email,
                            Address = item.Address,
                            FullName = item.FullName,
                            Users = null
    			
    
                            };
                            DatabaseContext.context.Person.Add(po
                            );
    
                            DatabaseContext.context.SaveChanges();
                            user.PersonID = po.PersonID;
                            user.LastModified=DateTime.Now;
                          
                            DatabaseContext.context.Set<User>().Attach(user);
                            DatabaseContext.context.Entry(user).Property(x => x.PersonId).IsModified = true;
                            DatabaseContext.context.Entry(user).Property(x => x.LastModified).IsModified = true;
    
                          
                            DatabaseContext.context.SaveChanges();

    Thanks,

    Ramya.

    Wednesday, February 10, 2016 11:47 AM
  • Can I know how we can update the user table with personID. The above code just creates a new row in user table.

    I did like below but we are doing savechanges twice. Please suggest good way

    I gave you the way to do it. Instead of you setting User inside of Parent to null, do the way that it is being shown to you.

    You create  the Person object and populate it. Within Person, you create the a new List<User> collection. You create a new User object, populate it and ADD it to the User collection that is inside the Parent object. You can add 10,000 new User objects to the po.Users collection.

    Then you can do ONE SAVE and EF is going to save the Person, take the Person primary key that was assigned by the DB engine and populate it to the 10,000 User objects using their foreign-key property in the po.Users collection and assign primary-keys to each User object.

    EF is going to do all of this by itself, and it doesn't you do anything other than you get the parent and all the objects in the parent's child collection populated correctly without you touching the Parent primary-key property, the child object's primary and foreign property.

    This is OOP 101 and usage of a collection 101.

    Capiche?

    var po = new Person();

    po.Users = new List<User>;

    populate Person object properties.

    var uo = new User()

    populate uo object properties.

    po.Users.Add(uo);

    Save object

    Wednesday, February 10, 2016 2:12 PM
  • Thanks I am doing this now in one call.

    In First Screen I get the details of the Person and then click on Update to update both Person and User Table on Second Screen. 

    Getting the below error while updating in the second screen

    {"The instance of entity type 'Person' cannot be tracked because another instance of this type with the same key is already being tracked. For new entities consider using an IIdentityGenerator to generate unique key values."}

    Friday, February 12, 2016 3:39 PM
  • Thanks I am doing this now in one call.

    In First Screen I get the details of the Person and then click on Update to update both Person and User Table on Second Screen. 

    Getting the below error while updating in the second screen

    {"The instance of entity type 'Person' cannot be tracked because another instance of this type with the same key is already being tracked. For new entities consider using an IIdentityGenerator to generate unique key values."}


    I will assume that you are using an Identity auto incremented Integer value as the primary key for all table schemas that the EF entity/objects are derived from. It is the MS SQL Server DB engine that is assigning primary-keys to table records in the DB by using auto incremented Identity primary keys.  
    Friday, February 12, 2016 10:18 PM
  • Yes its using Identity Auto Incremented integer value as primary for this person and User tables.

    Thanks,

    Ramya.

    Monday, February 15, 2016 1:52 PM
  • Yes its using Identity Auto Incremented integer value as primary for this person and User tables.

    Thanks,

    Ramya.

    What would you do if EF was not there and you where using T-SQL in a sproc that was doing this for you?

    1) add the parent object via EF that returns the primary-key back to the object when inserted.

    2) take the primary-key from the parent object and hold on to it in variable.  

    3) add each child object populating the child's foreign-key property from the parent's primary-key variable. 

    4) Put the whole thing in a Transaction.Scope.

    Just do it yourself if it's too much trouble.


    Monday, February 15, 2016 5:35 PM
  • What is your status?
    Tuesday, February 16, 2016 11:08 PM