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

imageimage

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

image

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

image

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.

image

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

image

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.

image

So I created a function as below,

image

And while attaching you need to call above function as below

image

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 Smile

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Advertisements

3 thoughts on “Inserting/Updating Record on related entities using LINQ

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s