使用Linq，ASP.NET MVC插入多个数据库表

I have a rather simple scenario where I have two tables in which I want to add data. They are managed with primary key/foreign key. I want to add new data into TABLE A and then retrieve the Id and insert into TABLE B.

我有一个相当简单的场景，我有两个表，我想在其中添加数据。它们使用主键/外键进行管理。我想在表A中添加新数据，然后检索Id并插入表B.

I can certainly do it with a stored procedure, but I'm looking at trying to do it using Linq.

我当然可以使用存储过程来完成它，但我正在尝试使用Linq来完成它。

What is the best approach ?

什么是最好的方法？

I can certainly get the ID and do two separate inserts but that doesn't certainly seem to be a very good way of doing things.

我当然可以获得ID并进行两次单独的插入，但这似乎并不是一种非常好的做事方式。

db.Table.InsertOnSubmit(dbObject);
db.SubmitChanges();

Int32 id = dbOject.Id;

//Rest of the code

Any way to elegantly do this?

任何方式优雅地做到这一点？

2 个解决方案

#1

Do you have the relationship defined between the 2 tables in the object relational designed? If so, you can have linq take care of assigning the ID property of the second table automatically.

您是否在对象关系设计中的两个表之间定义了关系？如果是这样，你可以让linq自动分配第二个表的ID属性。

Example...
Table A – Order
OrderId
OrderDate

Table B – Order Item
OrderItemId
OrderId
ItemId

示例...表A - 订单OrderId OrderDate表B - 订单项OrderItemId OrderId ItemId

Code (Using LINQ-to-SQL):

代码（使用LINQ-to-SQL）：

Order order = new Order();
Order.OrderDate = DateTime.Now();
dataContext.InsertOnSubmit(order);

OrderItem item1 = new OrderItem();
Item1.ItemId = 123;
//Note: We set the Order property, which is an Order object
// We do not set the OrderId property
// LINQ will know to use the Id that is assigned from the order above
Item1.Order = order;  
dataContext.InsertOnSubmit(item1);

dataContext.SubmitChanges();

#2

hi i insert data into three table using this code

您好我使用此代码将数据插入三个表

  Product_Table AddProducttbl = new Product_Table();
        Product_Company  Companytbl = new Product_Company();
        Product_Category Categorytbl = new Product_Category();

        // genrate product id's
        long Productid = (from p in Accountdc.Product_Tables 
                        select p.Product_ID ).FirstOrDefault();
        if (Productid == 0)
            Productid++;
        else
            Productid = (from lng in Accountdc.Product_Tables 
                         select lng.Product_ID ).Max() + 1;
        try
        {
            AddProducttbl.Product_ID = Productid;
            AddProducttbl.Product_Name = Request.Form["ProductName"];
            AddProducttbl.Reorder_Label = Request.Form["ReorderLevel"];
            AddProducttbl.Unit = Convert.ToDecimal(Request.Form["Unit"]);
            AddProducttbl.Selling_Price = Convert.ToDecimal(Request.Form["Selling_Price"]);
            AddProducttbl.MRP = Convert.ToDecimal(Request.Form["MRP"]);
            // Accountdc.Product_Tables.InsertOnSubmit(AddProducttbl );
            // genrate category id's
            long Companyid = (from c in Accountdc.Product_Companies
                              select c.Product_Company_ID).FirstOrDefault();
            if (Companyid == 0)
                Companyid++;
            else
                Companyid = (from Ct in Accountdc.Product_Companies
                             select Ct.Product_Company_ID).Max() + 1;

            Companytbl.Product_Company_ID = Companyid;
            Companytbl.Product_Company_Name = Request.Form["Company"];

            AddProducttbl.Product_Company = Companytbl;
            //Genrate Category id's
            long Categoryid = (from ct in Accountdc.Product_Categories
                               select ct.Product_Category_ID).FirstOrDefault();
            if (Categoryid == 0)
                Categoryid++;
            else
                Categoryid = (from Ct in Accountdc.Product_Categories
                              select Ct.Product_Category_ID).Max() + 1;
            Categorytbl.Product_Category_ID = Categoryid;
            Categorytbl.Product_Category_Name = Request.Form["Category"];
            AddProducttbl.Product_Category = Categorytbl;

            Accountdc.Product_Tables.InsertOnSubmit(AddProducttbl);
            Accountdc.SubmitChanges();

        }
        catch 
        {
            ViewData["submit Error"] = "No Product Submit";
        }