Putting serialized Entity Framework objects back in Database

Question

I am new to EF and am having trouble deserializing an object and then putting it back in the SQL Server Express (2014) DB . For this demo, I have just two tables, "Patient" and "Session". A Patient has 0 or more Sessions. The idea is to allow the user to export a session to disk. At some later point, he may want to put it back in the DB.

I've put my code below. I create 2 entities (Patient and Session), save them to DB. I checked that they are there. I then serialize the Session, and then remove the Session. I then (successfully) deserialize the session. I then call:

context2.Sessions.Add(session2); 
context2.SaveChanges();

On the SaveChanges line I get an exception informing me that:

System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_Patient'. Cannot insert duplicate key in object 'dbo.Patient'. The duplicate key value is (9749184b-380b-438a-a2fe-80413c3a616a).

Can someone tell me what I'm doing wrong? Should I be using 'Attach' instead of 'Add'? It sure looks like it's trying to recreate the Patient entry. Ideally, if it's saved to a Database that already has that particular Patient (as decided by primary key PatientGUID), it should use that. If there is not Patient with that PatientGUID, it should create one. Can someone get me going? If I can provide more info, or info in a different form, please let me know.

I've posted code and table layout below.

Thanks, Dave

static void Main(string[] args)
    {
        BinaryFormatter formatter = new BinaryFormatter();
        MemoryStream stream = new MemoryStream();
        using (PlayContext context = new PlayContext())
        {

            try
            {

                Patient pt = new Patient();
                Guid ptGuid = Guid.NewGuid();
                pt.PatientGUID = ptGuid;
                pt.PatientNumber = 1;


                Session session = new Session();
                session.SessionGUID = Guid.NewGuid();
                session.SessionNum = 101;
                pt.Sessions.Add(session);

                context.Patients.Add(pt);

                context.SaveChanges();



                try
                {
                    formatter.Serialize(stream, session);
                }
                catch (Exception ex)
                {
                    int x = 1;
                }
                context.Sessions.Remove(session);
                context.SaveChanges();


            }
            catch (Exception ex)
            {

            }

        }
        using (PlayContext context2 = new PlayContext())  // I use a new context as we'd often be getting entity back from file weeks later and from different program
        {
            try
            {
                stream.Seek(0, SeekOrigin.Begin);
                Session session2 = (Session)formatter.Deserialize(stream);
                context2.Sessions.Add(session2);
                context2.SaveChanges();
            }
            catch (Exception ex)
            {

            }
        }
    }

For the two tables, I have

Patient Table
PatientGUID uniqueidentifier NOT Allow Nulls  Primary Key
PatientNumber int  NOT Allow Nulls

Session Table
SessionGUID   uniqueidentifier NOT Allow Nulls Primary Key
PatientGUID   uniqueidentifier NOT Allow Nulls  (foreign key to PatientGUID in patient table)

SessionNum int NOT Allow Nulls

Insert and Update Specific for Session Table are set to 'Cascade'

When you deserialize session information inside this data is there patient object too? — Marc Cals, Jun 12 '15 at 18:33
Yes Marc, there is the patient object in the deserialized session object, and I suspect this is part of the problem. Is EF trying to be "helpful" by trying to save the Patient object a second time? Hence the "duplicate key" error? How do I prevent this? Thanks — Dave, Jun 12 '15 at 19:19
Thank you for the answers Marc and sjager. I really appreciate them. I think I have things working now by using elements of both your answers. Unfortunately, I can only pick one! Now on to my next big problem: Serializing a Session, serializes the Patient, which in turn could have thousands of Sessions. If I can't figure it out, I'll post a separate question. Cheers. — Dave, Jun 15 '15 at 18:01

score 1 · Answer 1 · edited May 23 '17 at 12:22

I would consider making "Save/Update" functions in EF conditional based on entity ID. An example template might look like:

Session SaveSession(Session input) {
    using(var context = new DbContext()){
        if(input.ID != 0) {
            // Entity exists, modify the original
            var mySession = context.Sessions.Find(input.ID);
            mySession.Name = input.Name;
            mySession.Color = input.Color;
            // Etc.

            // Make sure that foreign keys are pulled from current context and not duplicated
            mySession.Patient = context.Patients.Find(input.Patient.ID);

            context.Entry(mySession).EntityState = EntityState.Modified;
        } else {
            // Entity does not exist, add fresh

            // Make sure that foreign keys are pulled from current context and not duplicated
            input.Patient = context.Patients.Find(input.Patient.ID);

            context.Add(input);
        }

        context.SaveChanges();
        return input;
    }
}

This template can get fairly messy when dealing with very complex data models, but I've found that it is quite good at clearly telling EntityFramework what you want it to do. At the very least, I hope this will help you with your duplication problems!

Another tip, EF will automatically assign an ID to an Entity with an empty public int ID property, so you wouldn't have to set it manually. Here's a Stack question regarding that: How to generate and auto increment Id with Entity Framework

score 0 · Accepted Answer · answered Jun 12 '15 at 18:37

Entity framework doesn't work well with deattached entities, and with your code Entity doesn't know that your Patient exist in database, for this reason try to insert patient too.

Try adding context2.Patient.Attach(session2.Patient); before adding session to context.

Putting serialized Entity Framework objects back in Database

2 Answers2