none
inserting the data into more than one table using linq to sql RRS feed

  • Question

  • hi everyone,

    i am new to the linq,

    i am not able to insert the data into more than one table  with the conditiion. so please help me in the coding.

     

    thanking you,

    anilkumar thumma


    anilkumar thumma
    Tuesday, August 2, 2011 5:46 AM

Answers

  • I found a similar requirement of yours as shown below..

    HookedOnLINQ db = 
      new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
     
    // Adding Records – (1) Create a new object and sub-objects, 
    // (2) Add it to the DataContext collection, (3) Call SubmitChanges 
     
    // (1)
    Contacts newContact  = new Contacts();
    newContact.FirstName  = "Troy";
    newContact.LastName  = "Magennis";
    newContact.Phone    = "425 749 0494";
    newContact.Email    = "troy@aspiring-technology.com";
    newContact.DateOfBirth = new DateTime(1980, 08, 07);
    // Create sub-record and add to this contact
    Orders newOrder     = new Orders();
    newOrder.Products    = (from p in db.Products 
                  where p.ProductName == "Asset Blaster Pro" 
                  select p).FirstOrDefault();
    newOrder.DateOfPurchase = DateTime.Now;
     
    // (2)
    newContact.Orders.Add(newOrder);
    db.Contacts.Add(newContact);
     
    // (3)
    db.SubmitChanges();
    
    

     

    Please refer the link http://www.hookedonlinq.com/LINQtoSQL5MinuteOverview.ashx
    • Proposed as answer by Allan Merolla Tuesday, August 2, 2011 2:16 PM
    • Marked as answer by Larcolais Gong Tuesday, August 16, 2011 2:40 AM
    Tuesday, August 2, 2011 10:58 AM
  • Hi Anil,

    If I am understand right, then you have 3 tables joined with foreign keys and you want to update all of the 3 tables. (the foreign key connections are defined in your "condition")

    I have changed your above query how you can do it. You just need to add all of the tables in the result of your query, modify the objects and save it.

    You can do this with creating a new anonymous type like below:

     

    var query= (from user in webuser
      from x in xtable
      from y in ytable
      where(condition) // this tables have the primary key relation
      select new { User = user, X =x, Y=y}).First();
    
    query.User.Name = "new name"; 
    query.X.SomeProperty = "new value";
    ...
    db.SubmitChanges(); //db should be the name of your datacontext

    HTH

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.

    • Edited by Crick3t Tuesday, August 2, 2011 11:10 AM
    • Proposed as answer by Crick3t Tuesday, August 2, 2011 2:21 PM
    • Marked as answer by Larcolais Gong Tuesday, August 16, 2011 2:40 AM
    Tuesday, August 2, 2011 11:04 AM

All replies

  • Hi below is a sample code for insert.

    PubsDatabaseModelDataContext pubs =
    new PubsDatabaseModelDataContext();
    author auth = new author();
    auth.au_id = "000-00-0001";
    auth.au_fname = "Monica";
    auth.au_lname = "Brink";
    auth.address = "555 Some St.";
    auth.state = "WA";
    auth.city = "Redmond";
    auth.contract = false;
    auth.phone = "555-1212";
    auth.zip = "12345";
    pubs.authors.InsertOnSubmit(auth);
    pubs.SubmitChanges();
    

     

    Tuesday, August 2, 2011 6:40 AM
  • hi muthukumar

     thanks for the reply,

    i already mentioned in my question that i have to insert data into more than one table using the single query

    like

    var query= (from user in webuser

                    from x in xtable

                    from y in ytable

                    where(condition) // this  tables have the primary key relation

                    select user).first();

    by using the above query i have to insert the data into the above mentioned tables.

    so please help me in that

    thanking you,

    anilkumar thumma


    anilkumar thumma
    Tuesday, August 2, 2011 7:55 AM
  • Anil,

    I do not understand your requirement.. Do you want to insert records into two tables which has foreign key relationship? What role does your select query perform in your insert operations ? Can you give some more explanaion of your problem.?

    Tuesday, August 2, 2011 10:18 AM
  • thanks for your reply Rajendran.

     

    actually i have to update the customer details by using the  single query.

    using this query the appropriate tebles must be updated.

    and there are 4 tables related to  that updation task. and the tables are foreign key related.

     

     

     

     


    anilkumar thumma
    Tuesday, August 2, 2011 10:33 AM
  • I found a similar requirement of yours as shown below..

    HookedOnLINQ db = 
      new HookedOnLINQ("Data Source=(local);Initial Catalog=HookedOnLINQ");
     
    // Adding Records – (1) Create a new object and sub-objects, 
    // (2) Add it to the DataContext collection, (3) Call SubmitChanges 
     
    // (1)
    Contacts newContact  = new Contacts();
    newContact.FirstName  = "Troy";
    newContact.LastName  = "Magennis";
    newContact.Phone    = "425 749 0494";
    newContact.Email    = "troy@aspiring-technology.com";
    newContact.DateOfBirth = new DateTime(1980, 08, 07);
    // Create sub-record and add to this contact
    Orders newOrder     = new Orders();
    newOrder.Products    = (from p in db.Products 
                  where p.ProductName == "Asset Blaster Pro" 
                  select p).FirstOrDefault();
    newOrder.DateOfPurchase = DateTime.Now;
     
    // (2)
    newContact.Orders.Add(newOrder);
    db.Contacts.Add(newContact);
     
    // (3)
    db.SubmitChanges();
    
    

     

    Please refer the link http://www.hookedonlinq.com/LINQtoSQL5MinuteOverview.ashx
    • Proposed as answer by Allan Merolla Tuesday, August 2, 2011 2:16 PM
    • Marked as answer by Larcolais Gong Tuesday, August 16, 2011 2:40 AM
    Tuesday, August 2, 2011 10:58 AM
  • hi Rajendran

    thank you so much for your help.

    its really helpful for me.


    Thanking You, Anilkumar Thumma
    Tuesday, August 2, 2011 11:01 AM
  • Thnaks..

    Mark it as answer if it helped you..

    Tuesday, August 2, 2011 11:03 AM
  • Hi Anil,

    If I am understand right, then you have 3 tables joined with foreign keys and you want to update all of the 3 tables. (the foreign key connections are defined in your "condition")

    I have changed your above query how you can do it. You just need to add all of the tables in the result of your query, modify the objects and save it.

    You can do this with creating a new anonymous type like below:

     

    var query= (from user in webuser
      from x in xtable
      from y in ytable
      where(condition) // this tables have the primary key relation
      select new { User = user, X =x, Y=y}).First();
    
    query.User.Name = "new name"; 
    query.X.SomeProperty = "new value";
    ...
    db.SubmitChanges(); //db should be the name of your datacontext

    HTH

     


    Regards, Peter

    //If a post answers your question, please click "Mark As Answer".
    //Also if a post seems to be helpful, please click "Mark as Helpful" on that post.

    • Edited by Crick3t Tuesday, August 2, 2011 11:10 AM
    • Proposed as answer by Crick3t Tuesday, August 2, 2011 2:21 PM
    • Marked as answer by Larcolais Gong Tuesday, August 16, 2011 2:40 AM
    Tuesday, August 2, 2011 11:04 AM
  • ya surely i do that if it workful for.

    and i hope it willl be workful.

    and once again thanks for putting efforts on my question.

     


    Thanking You, Anilkumar Thumma
    Tuesday, August 2, 2011 11:08 AM