Inserting/Updating Record on related entities using LINQ

It is a very common scenario that you need to insert rows in related tables using LINQ. Assume you have two tables as below,

Insert a Person with no Office Assignment

If you have requirement to insert a Person without any Office Assignment then it is quiet straight forward task like below

If you need to insert Office Assignment while inserting Person, you can create instance of office assignment and insert along with Person like below,

Now I have created function to perform both Insert and update operation. If you provide person Id as 0 this function will insert a new row else it will update existing row.

When I tried to call above function to update Person with ID 1, I encountered with below exception

To get rid of above exception, you need to call attach with original entity as well. We need to pass modified entity as well as original entity to make a call.

So I created a function as below,

And while attaching you need to call above function as below

Full source code is as below,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Relatedtable
{
    class Program
    {

        static DataClasses1DataContext context;
        static void Main(string[] args)
        {

            context = new DataClasses1DataContext();
            context.Log = Console.Out;
            context.Persons.InsertOnSubmit(
                            new Person
                            {
                                FirstName = "The ",
                                LastName = "Poet",
                                HireDate = DateTime.Now,
                                OfficeAssignment = new OfficeAssignment
                                {
                                    Location = "Jamshedpur"
                                }

                            }
                            );

            context.SubmitChanges();
            Console.ReadKey(true);

            //Person p = new Person {FirstName ="xuz", LastName = "abdefg" };
            //p.OfficeAssignment = new OfficeAssignment { Location = "Mumbai" };
            //p.OfficeAssignment = new OfficeAssignment { Location = "Delhi" };

            //p.
            //context.SubmitChanges();

            Person p = new Person { PersonID = 1, FirstName = "Dhananjay " ,LastName="Kumar"};
            SavePerson(p);

            var result = from r in context.Persons select r;
            foreach (var r in result)
            {
                Console.WriteLine(r.FirstName);
            }
            Console.ReadKey(true);

            var res1 = from r in context.OfficeAssignments select r;
            foreach (var a in res1)
            {
                Console.WriteLine(a.Location+ a.Person.PersonID);
            }

            Console.ReadKey(true);

        }

        //static string GetConnectionString(string serverName)
        //{

        //    System.Data.SqlClient.SqlConnectionStringBuilder builder =
        //                   new System.Data.SqlClient.SqlConnectionStringBuilder();
        //    builder["Data Source"] = devServerName;
        //    builder["integrated Security"] = true;
        //    builder["Initial Catalog"] = "Sample2";
        //    Console.WriteLine(builder.ConnectionString);
        //    Console.ReadKey(true);
        //    return builder.ConnectionString;

        //}

        static void SavePerson(Person p)
        {

            if (p.PersonID == 0)
            {
                context.Persons.InsertOnSubmit(p);
            }
            else
            {

               context.Persons.Attach(p, GetOriginal(p.PersonID));
               context.SubmitChanges();

            }
        }

        static Person GetOriginal(int id)
        {
            DataClasses1DataContext db = new DataClasses1DataContext();
            return db.Persons.Single(r => r.PersonID == id);

        }
    }
}

I hope this post was useful. Thanks for reading

Follow @debugmode_