How can i do insert more data with insert query with more select statment linqToSQL?

Question

How can i use insert into myTABLE (. . . .. . ) select * from(.......). I try to write some linq query but i can not. Look please linq query.

SQL:

INSERT INTO ActualAmountsByLocation (ItemBarcode, Location, LocationName, Amount, isCustomerItem, LastUpdate)
   SELECT * FROM 
      (SELECT DISTINCT 
           m.ItemBarcode, 
           m.ToLocationType + m.ToNo AS Location,
           l.shortdesc AS LocationName,  
           dbo.spGetActualAmountByLocation(m.ItemBarcode, m.ToLocationType + m.ToNo) AS Amount, 
           0 AS isCustomerItem,
           GETDATE() AS LastUpdate 
       FROM 
           dbo.StockMovement m, RefLocations l 
       WHERE 
           m.ToLocationType = 'L' 
           AND m.ToLocationType + m.ToNo = l.code 
           AND m.ItemBarcode = @Barcode 
           AND m.TransactionType = 1
       ) a  
  ORDER BY ItemBarcode, Location

UPDATE ActualAmountsByLocation 
SET isCustomerItem = 1 
WHERE ItemBarcode IN 
      (SELECT barcode FROM StockMaterials WHERE barcode = @Barcode AND ownership = 1)

UPDATE ActualAmountsByLocation 
SET isCustomerItem = 1
WHERE ItemBarcode IN 
      (SELECT barcode FROM StockTools WHERE barcode = @Barcode AND ownership = 1)

UPDATE ActualAmountsByLocation 
SET isCustomerItem = 1 
WHERE ItemBarcode IN 
      (SELECT barcode FROM StockComponents WHERE barcode = @Barcode AND ownership = 1)

SET @ReturnMsg = RTRIM(@ReturnMsg) + 'E25: Transaction completed successfully ' + CHAR(13) + CHAR(10)

Linq:

if (ToLocationType == 'L')
{
   var stokRefLocation = (from m in stockMovementCtx.StockMovements
                          from l in stockMovementCtx.RefLocations
                          where m.ToLocationType == 'L' && m.ToLocationType + m.ToNo == l.code && m.ItemBarcode == Barcode && m.TransactionType == 1
                          orderby m.ItemBarcode, l.shortdesc
                          select new
                          {
                             myItemBarcode= m.ItemBarcode,
                             myLocation = m.ToNo + m.ToNo,
                             myLocationName = l.shortdesc,
                             myAmount = stockMovementCtx.spGetActualAmountByLocation(m.ItemBarcode, m.ToLocationType + m.ToNo),
                                                  myIsCustomerItem = 0,
                                                  myLastUpdate = DateTime.Now
                                              }).Distinct();

   var stokMovement2 = new ActualAmountsByLocation()
                       { 
                           ItemBarcode = stokRefLocation. //.. how can i do that? I want to see stokRefLocation.myItemBarcode
                       }
                   }
               }

Answer 1

The truth is that L2S isn't really optimised for that sort of operation. You could create a stored procedure to do it (that can be added to your L2S datacontext for execution) or write is as a direct database command.

Typically in an ORM you'd update all the records, then submit the changes on the datacontext. If that's too slow, then consider optimising by one of the above methods.