static void RunTVPEntityQuery() { using (var db = new L2S.AdventureWorksDataContext("Data Source=(local);Database=AdventureWorks2012;Integrated Security=true")) { db.Connection.Open(); SqlConnection con = (SqlConnection)db.Connection; var sql = @" select * from Sales.SalesOrderDetail where SalesOrderID in (select Value from @ids) "; SqlCommand cmd = new SqlCommand(sql, con); SqlParameter pIds = cmd.Parameters.Add(new SqlParameter("@ids", SqlDbType.Structured)); db.ExecuteCommand("if not exists (select * from sys.table_types where name = 'Int_TableType') CREATE TYPE Int_TableType AS TABLE(Value int NOT NULL)"); pIds.TypeName = "Int_TableType"; //create a list of ID's var ids = Enumerable.Range(43659, 1000); pIds.Value = ids.AsDataReader(); List <L2S.SalesOrderDetail> od; using (var rdr = cmd.ExecuteReader()) { od = db.Translate <L2S.SalesOrderDetail>(rdr).ToList(); } Console.WriteLine(od.Count()); } }
static void RunL2SMergeTest() { using (var db = new L2S.AdventureWorksDataContext()) using (var con = db.Connection) { //db.ObjectTrackingEnabled = false; var lo = new System.Data.Linq.DataLoadOptions(); lo.LoadWith <L2S.SalesOrderHeader>(o => o.SalesOrderDetails); db.LoadOptions = lo; db.Connection.Open(); var customerId = (from o in db.SalesOrderHeaders where o.SalesOrderDetails.Count() > 2 select o).Take(1).First().CustomerID; var q = from o in db.SalesOrderHeaders where o.CustomerID == customerId select o; var orders = q.ToList(); foreach (var o in orders) { foreach (var od in o.SalesOrderDetails) { od.UnitPrice = od.UnitPrice * 1.04M; } } string sql = @" declare @output Sales_SalesOrderDetail_type MERGE Sales.SalesOrderDetail AS target USING ( SELECT SalesOrderID, SalesOrderDetailID, UnitPrice FROM @OrderDetails ) AS source (SalesOrderID, SalesOrderDetailID, UnitPrice) ON ( target.SalesOrderID = source.SalesOrderID and target.SalesOrderDetailID = source.SalesOrderDetailID ) WHEN MATCHED THEN UPDATE SET target.UnitPrice = source.UnitPrice, target.ModifiedDate = GETDATE() OUTPUT inserted.* into @output; select * from @output; "; var cmd = new SqlCommand(sql, (SqlConnection)db.Connection); var pOrderDetails = cmd.Parameters.Add(new SqlParameter("@OrderDetails", SqlDbType.Structured)); pOrderDetails.TypeName = "Sales_SalesOrderDetail_type"; var sql2 = @" if not exists (select * from sys.table_types where name = 'Sales_SalesOrderDetail_type') CREATE TYPE Sales_SalesOrderDetail_type as TABLE ( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL, [LineTotal] MONEY, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ModifiedDate] [datetime] NOT NULL ) "; db.ExecuteCommand(sql2); //project the results into an Anonymous type matching the TVP declaration var dtq = from od in orders.SelectMany(o => o.SalesOrderDetails).Take(1000) select new { SalesOrderID = od.SalesOrderID, SalesOrderDetailId = od.SalesOrderDetailID, CarrierTrackingNumber = od.CarrierTrackingNumber, OrderQty = od.OrderQty, ProductID = od.ProductID, SpecialOfferID = od.SpecialOfferID, UnitPrice = od.UnitPrice, UnitPriceDiscount = od.UnitPriceDiscount, LineTotal = od.LineTotal, rowguid = od.rowguid, ModifiedDate = od.ModifiedDate }; var i = 0; var f = new { a = i++, b = i++, c = i++ }; //flatten the all the OrderDetails into a single DataReader for sending to the server pOrderDetails.Value = dtq.AsDataReader(); IList <L2S.SalesOrderDetail> results; try { using (var dr = cmd.ExecuteReader()) { results = db.Translate <L2S.SalesOrderDetail>(dr).ToList(); } } catch (SqlException ex) { throw; } foreach (var r in results) { Console.WriteLine("{0} {1}", r.SalesOrderDetailID, r.UnitPrice); } if (dtq.Count() != results.Count()) { throw new InvalidOperationException("Wrong number of rows affected by MERGE"); } } }