-1

How to copy a row with his data linked in other tables (related ) and insert them as a new records in All Tables, with :

ASP.NET MVC / C# / Linq (Method syntax) / Entity Framework Code-First

For Example :

I have these tables =>

enter image description here

I want to copy the last line of the "Customer" table and add it in a new line of the database with a new ID, and at the same time copy all orders related in the table "Order" and duplicate them with "CustoemerID" of the new "Customer" with new ID's , and at the same time copy all orders lines in table "OrderLine" and duplicate them with the new "OrderID"

How to do it ?

Is there a brief code for doing this?

enter image description here

The blue color is the last row in the database, and the green color is the result of the code what i'm looking for, a duplicate copy of the customer and his related data

Primary keys : CustomerID & OrderID & OrderLineID = AUTO INCREMENT Field

Mazilia Tech
  • 3
  • 4
  • Look here if this help you: https://stackoverflow.com/questions/15308747/entity-framework-5-deep-copy-clone-of-an-entity/15322430#15322430 – Leo Dec 26 '18 at 13:58

2 Answers2

0

See if this helps you:

INSERT INTO customer (ID, Name, Address1, Address2, Address3) 
    SELECT 
        3, Name, Address1, Address2, Address3 
    FROM
        customer 
    WHERE
       ID = 2;

INSERT INTO Order (CustomerId, TotalAmount) 
    SELECT CustomerId, TotalAmount 
    FROM Order 
    WHERE CustomerID = 3;

INSERT INTO OrderLine (OrderId, ProductId, Quantity) 
    SELECT ol.OrderId, ol.ProductId, ol.Quantity 
    FROM OrderLine ol 
    WHERE ol.OrderId IN (SELECT o2.OrderId 
                         FROM Order o2 
                         WHERE o2.CustomerID = 3);
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anupam Sharma
  • 368
  • 3
  • 12
  • For SQL Server - this will definitely **FAIL** - since `Order` is a reserved T-SQL keyword, and if you use it as a table name, you **MUST** put it in square brackets - `[Order]` .... – marc_s Dec 24 '18 at 11:14
  • In LINQ please , I think you didn't understand my question well, please read it again – Mazilia Tech Dec 24 '18 at 11:21
  • Thanks Marc, yes i agree. Forgot to wrap reserve keyword within square bracket. – Anupam Sharma Dec 24 '18 at 11:39
  • Mazilia, i missed the point that you are looking LINQ query but it wouldn't be difficult to translate above logic to LINQ? – Anupam Sharma Dec 24 '18 at 11:40
0

I can think of 2 solutions for this question

  1. Using Sql query

  2. Using EF code first you can create a clone method inside your Customer class.

  CREATE TABLE #Customer (
  CustomerId INT Identity(1, 1)
  ,Name NVARCHAR(100)
  ,Address1 NVARCHAR(100)
  ,Address2 NVARCHAR(100)
  ,Address3 NVARCHAR(100)
  )

INSERT INTO #Customer
VALUES (
  'N2'
  ,'A11'
  ,'A22'
  ,'A33'
  )

INSERT INTO #Customer
SELECT Name
  ,Address1
  ,Address2
  ,Address3
FROM #Customer
GROUP BY Name
  ,Address1
  ,Address2
  ,Address3
HAVING Count(*) BETWEEN 0
      AND 1

Using Clone method

   public class Customer
    {
        public int CustomerId { get; set; }
        public string Name { get; set; }
        public string Address1 { get; set; }
        public string Address2 { get; set; }
        public string Address3 { get; set; }


        public Customer Clone()
        {
            // where the reference is not detached
            var employee1 = new Customer
            {
                Name = Name,
                Address1 = Address1,
                Address2 = Address2,
                Address3 = Address3
            };

            // with reference detached
            var serialisedData = JsonConvert.SerializeObject(this);
            var employee2 = JsonConvert.DeserializeObject<Employee>(serialisedData);

            //  return employee1 or employee2;
            return employee1;

        }
    }
Ram Bharlia
  • 62
  • 3