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");
}
}
}
