locked
Insert into command over several tables - possible in EF ? RRS feed

  • Question

  • Hello,

    is it possible to execute a sort of  "insert into" command in EF over several tables having 1:N and N:M relations?
    Sunday, November 23, 2008 8:48 PM

Answers

  • With Northwind, if you do something like this:

    Code Snippet

    context.AddToOrderSet(new Order { Customer = new Customer { CustomerID = "MEZIL", CompanyName = "Winwise", ContactName = "Matthieu" }, OrderDate = DateTime.Now });

    context.SaveChanges();

     

     

    you will add a new Customer and a new Order even if you just do one Add into the context.

     

    For N:M relationships, it's the same:

    Code Snippet

    var c = new Customer { CustomerID = "MEZIL", CompanyName = "Winwise", ContactName = "Matthieu" };

    var cd = new CustomerDemographics { CustomerTypeID = "Type", CustomerDesc = "Desc" };

    c.CustomerDemographics.Add(cd);

    context.AddToCustomerSet(c);

    context.SaveChanges();

     

     

     

    Sunday, November 23, 2008 9:35 PM

All replies

  • With Northwind, if you do something like this:

    Code Snippet

    context.AddToOrderSet(new Order { Customer = new Customer { CustomerID = "MEZIL", CompanyName = "Winwise", ContactName = "Matthieu" }, OrderDate = DateTime.Now });

    context.SaveChanges();

     

     

    you will add a new Customer and a new Order even if you just do one Add into the context.

     

    For N:M relationships, it's the same:

    Code Snippet

    var c = new Customer { CustomerID = "MEZIL", CompanyName = "Winwise", ContactName = "Matthieu" };

    var cd = new CustomerDemographics { CustomerTypeID = "Type", CustomerDesc = "Desc" };

    c.CustomerDemographics.Add(cd);

    context.AddToCustomerSet(c);

    context.SaveChanges();

     

     

     

    Sunday, November 23, 2008 9:35 PM
  • The way the context works, you can set up your object graph and when you call ObjectContext.SaveChanges(), all the inserts will be executed for all changed/new/deleted elements in the graph.

     

    The EF does not directly support DML, however, if that is what you are asking. For DML, you need to go down to stored procedures/SSDL functions.

    Sunday, November 23, 2008 9:35 PM
  • thank you very interesting Smile
    Sunday, November 23, 2008 10:32 PM
  •  Noam Ben-Ami // MSFT wrote:

    The way the context works, you can set up your object graph and when you call ObjectContext.SaveChanges(), all the inserts will be executed for all changed/new/deleted elements in the graph.

     

    The EF does not directly support DML, however, if that is what you are asking. For DML, you need to go down to stored procedures/SSDL functions.



    Hm... now I must ask again: DML = Data Manipulation Language (DML) is a family of computer languages used by computer programs or database users to retrieve, insert, delete and update data in a database.


    from wikipedia site.

    BUT EF supports DML just not the literal commands like delete in EF its DeleteObject() or insert in EF its AddObject();

    you confused me. What are you talking about?
    Thursday, November 27, 2008 1:45 PM
  • Lisa,

     

    Can you give an example of what you are trying to achieve?

     

    I.e. do you want to generate an "insert into ... select ... from ..." statement? Or something else?

     

    Set-based operations are not supported out-of-the box by EF or L2S. However, you can always write extensions to generate set-based update/delete/insert. I have covered set-based update/delete for Linq-to-SQL in a couple of blog articles:

     

    http://blog.huagati.com/res/index.php/2008/11/25/architecture-linq-to-sql-and-set-based-operations-delete-statements/

    http://blog.huagati.com/res/index.php/2008/11/05/architecture-linq-to-sql-and-set-based-operations-update-statements/

     

    ...and an article on "insert into...select..." is coming soon.

     

    (The technique used and the code in the articles won't work with EF, but since L2S is a "semi-related" technology I thought the articles above may still be of interest to you.)

    Thursday, November 27, 2008 2:09 PM

  • What I wanna do is pretty simple and surely does not need any of your Set-based stuff.

    I was just confused by a statement above. I just wanna do add/delete/retrieve Data on 1:N:M relations which is possible with EF.


    Thursday, November 27, 2008 3:53 PM