[索引页]
[×××]
作者: webabcd
介绍
以Northwind为 示例数据库,DLINQ(LINQ to SQL)之 调用标量值用户自定义函数;调用表值用户自定义函数;使用数据加载选项加载数据;相关信息的获取;字段的延迟加载;在不同的DataContext之间做更新;缓存
示例
Others.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using DAL;
public partial
class LINQ_DLINQ_Others : System.Web.UI.Page
{
protected
void Page_Load(
object sender, EventArgs e)
{
// 调用标量值用户自定义函数
Call_HelloFunctionScalar();
// 调用表值用户自定义函数
Call_HelloFunctionTable();
// 不使用数据加载选项
DataLoadOptions_Original();
// 使用数据加载选项加载数据
DataLoadOptions_LoadWith();
// 使用数据加载选项加载数据,并且限制所要加载的数据
DataLoadOptions_AssociateWith();
// 相关信息的获取
GetInfo();
// 字段的延迟加载
DelayLoaded();
// 在不同的DataContext之间做更新
IsVersion();
// 缓存
ShowCache();
}
}
/// <summary>
/// 调用标量值用户自定义函数
/// </summary>
void Call_HelloFunctionScalar()
{
NorthwindDataContext ctx =
new NorthwindDataContext();
result.InnerHtml += ctx.HelloFunctionScalar(
"webabcd");
result.InnerHtml +=
"<br />";
result.InnerHtml +=
"<br />";
}
运行结果
Hello:webabcd
调用表值用户自定义函数
使用数据加载选项加载数据,并且限制所要加载的数据
OK
[×××]
[×××]
步步为营VS 2008 + .NET 3.5(13) - DLINQ(LINQ to SQL)之用户自定义函数、在不同的DataContext之间做更新、缓存、获取信息、数据加载选项和延迟加载
作者: webabcd
介绍
以Northwind为 示例数据库,DLINQ(LINQ to SQL)之 调用标量值用户自定义函数;调用表值用户自定义函数;使用数据加载选项加载数据;相关信息的获取;字段的延迟加载;在不同的DataContext之间做更新;缓存
示例
Others.aspx.cs
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
调用标量值用户自定义函数
ALTER FUNCTION dbo.fnHelloFunctionScalar
(
@param nvarchar(100)
)
RETURNS nvarchar(200)
AS
BEGIN
RETURN 'Hello:' + @param
END
(
@param nvarchar(100)
)
RETURNS nvarchar(200)
AS
BEGIN
RETURN 'Hello:' + @param
END
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
Hello:webabcd
调用表值用户自定义函数
ALTER FUNCTION dbo.fnHelloFunctionTable
(
@ID int,
@name nvarchar(100)
)
RETURNS @tbl TABLE ([ID] int, [Name] nvarchar(100))
AS
BEGIN
INSERT INTO @tbl VALUES (@ID, @name)
RETURN
END
(
@ID int,
@name nvarchar(100)
)
RETURNS @tbl TABLE ([ID] int, [Name] nvarchar(100))
AS
BEGIN
INSERT INTO @tbl VALUES (@ID, @name)
RETURN
END
/// <summary>
/// 调用表值用户自定义函数
/// </summary>
void Call_HelloFunctionTable()
{
NorthwindDataContext ctx =
new NorthwindDataContext();
var p = (ctx.HelloFunctionTable(1,
"webabcd")).Single();
result.InnerHtml +=
"ID:" + p.ID;
result.InnerHtml +=
"<br />";
result.InnerHtml +=
"Name:" + p.Name;
result.InnerHtml +=
"<br />";
result.InnerHtml +=
"<br />";
}
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
运行结果
ID:1
Name:webabcd
不使用数据加载选项
ID:1
Name:webabcd
不使用数据加载选项
/// <summary>
/// 不使用数据加载选项
/// </summary>
void DataLoadOptions_Original()
{
NorthwindDataContext ctx =
new NorthwindDataContext();
var categories = from c
in ctx.Categories select c;
foreach (var c
in categories)
{
// 每一次迭代都会执行一条SQL语句来返回当前Category的Product
foreach (var p
in c.Products)
{
string productName = p.ProductName +
"<br>";
}
}
}
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
//
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture]
// FROM [dbo].[Categories] AS [t0]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [3]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [4]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [6]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [7]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [8]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// FROM [dbo].[Categories] AS [t0]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [3]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [4]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [6]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [7]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
// FROM [dbo].[Products] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [8]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
使用数据加载选项加载数据
/// <summary>
/// 使用数据加载选项加载数据
/// </summary>
void DataLoadOptions_LoadWith()
{
NorthwindDataContext ctx =
new NorthwindDataContext();
System.Data.Linq.DataLoadOptions options =
new System.Data.Linq.DataLoadOptions();
// Category以LEFT JOIN的方式关联Product
options.LoadWith<Categories>(c => c.Products);
ctx.LoadOptions = options;
var categories = from c
in ctx.Categories select c;
foreach (var c
in categories)
{
foreach (var p
in c.Products)
{
string productName = p.ProductName +
"<br>";
}
}
}
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
//
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture], [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID], [t1].[CategoryID]
AS [CategoryID2], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued], (
// SELECT COUNT(*)
// FROM [dbo].[Products] AS [t2]
// WHERE [t2].[CategoryID] = [t0].[CategoryID]
// ) AS [ count]
// FROM [dbo].[Categories] AS [t0]
// LEFT OUTER JOIN [dbo].[Products] AS [t1] ON [t1].[CategoryID] = [t0].[CategoryID]
// ORDER BY [t0].[CategoryID], [t1].[ProductID]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT COUNT(*)
// FROM [dbo].[Products] AS [t2]
// WHERE [t2].[CategoryID] = [t0].[CategoryID]
// ) AS [ count]
// FROM [dbo].[Categories] AS [t0]
// LEFT OUTER JOIN [dbo].[Products] AS [t1] ON [t1].[CategoryID] = [t0].[CategoryID]
// ORDER BY [t0].[CategoryID], [t1].[ProductID]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
使用数据加载选项加载数据,并且限制所要加载的数据
/// <summary>
/// 使用数据加载选项加载数据,并且限制所要加载的数据
/// </summary>
void DataLoadOptions_AssociateWith()
{
NorthwindDataContext ctx =
new NorthwindDataContext();
System.Data.Linq.DataLoadOptions options =
new System.Data.Linq.DataLoadOptions();
// Category以LEFT JOIN的方式关联Product
options.LoadWith<Categories>(c => c.Products);
// 限制所关联的Product
options.AssociateWith<Categories>(c => c.Products.Where(p => p.UnitPrice > 20));
ctx.LoadOptions = options;
var categories = from c
in ctx.Categories select c;
foreach (var c
in categories)
{
foreach (var p
in c.Products)
{
string productName = p.ProductName +
"<br>";
}
}
}
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
//
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture], [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID], [t1].[CategoryID]
AS [CategoryID2], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued], (
// SELECT COUNT(*)
// FROM [dbo].[Products] AS [t2]
// WHERE ([t2].[UnitPrice] > @p0) AND ([t2].[CategoryID] = [t0].[CategoryID])
// ) AS [ count]
// FROM [dbo].[Categories] AS [t0]
// LEFT OUTER JOIN [dbo].[Products] AS [t1] ON ([t1].[UnitPrice] > @p0) AND ([t1].[CategoryID] = [t0].[CategoryID])
// ORDER BY [t0].[CategoryID], [t1].[ProductID]
// -- @p0: Input Currency (Size = 0; Prec = 19; Scale = 4) [20]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT COUNT(*)
// FROM [dbo].[Products] AS [t2]
// WHERE ([t2].[UnitPrice] > @p0) AND ([t2].[CategoryID] = [t0].[CategoryID])
// ) AS [ count]
// FROM [dbo].[Categories] AS [t0]
// LEFT OUTER JOIN [dbo].[Products] AS [t1] ON ([t1].[UnitPrice] > @p0) AND ([t1].[CategoryID] = [t0].[CategoryID])
// ORDER BY [t0].[CategoryID], [t1].[ProductID]
// -- @p0: Input Currency (Size = 0; Prec = 19; Scale = 4) [20]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
相关信息的获取
/// <summary>
/// 相关信息的获取
/// </summary>
void GetInfo()
{
NorthwindDataContext ctx =
new NorthwindDataContext();
var categories = from c
in ctx.Categories select c;
result.InnerHtml +=
"ProviderType:" + ctx.Mapping.ProviderType.ToString() +
"<br />";
result.InnerHtml +=
"DatabaseName:" + ctx.Mapping.DatabaseName +
"<br />";
result.InnerHtml +=
"ContextType:" + ctx.Mapping.ContextType.ToString() +
"<br />";
result.InnerHtml +=
"TableName:" + ctx.Mapping.GetTable(
typeof(Categories)).TableName +
"<br />";
result.InnerHtml +=
"Expression:" + categories.Expression.ToString() +
"<br />";
result.InnerHtml +=
"T-SQL:" + categories.Provider.ToString() +
"<br />";
}
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
运行结果
ProviderType:System.Data.Linq.SqlClient.SqlProvider
DatabaseName:C:\DOCUMENTS AND SETTINGS\WANGLEI\桌面\VS2008\WEB\APP_DATA\NORTHWIND.MDF
ContextType:DAL.NorthwindDataContext
TableName:dbo.Categories
Expression:Table(Categories).Select(c => c)
T-SQL:SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description] FROM [dbo].[Categories] AS [t0]
字段的延迟加载
ProviderType:System.Data.Linq.SqlClient.SqlProvider
DatabaseName:C:\DOCUMENTS AND SETTINGS\WANGLEI\桌面\VS2008\WEB\APP_DATA\NORTHWIND.MDF
ContextType:DAL.NorthwindDataContext
TableName:dbo.Categories
Expression:Table(Categories).Select(c => c)
T-SQL:SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description] FROM [dbo].[Categories] AS [t0]
字段的延迟加载
/// <summary>
/// 字段的延迟加载
/// </summary>
void DelayLoaded()
{
// 在对象关系设计器(Object Relational Designer)中设置Categories实体的Picture属性的Delay Loaded为True
NorthwindDataContext ctx =
new NorthwindDataContext();
var category = (from c
in ctx.Categories
select c).Single(c => c.CategoryID == 1);
System.Data.Linq.Binary b = category.Picture;
}
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
//
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description]
// FROM [dbo].[Categories] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[Picture]
// FROM [dbo].[Categories] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// FROM [dbo].[Categories] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SELECT [t0].[Picture]
// FROM [dbo].[Categories] AS [t0]
// WHERE [t0].[CategoryID] = @p0
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
在不同的DataContext之间做更新
/// <summary>
/// 在不同的DataContext之间做更新
/// </summary>
void IsVersion()
{
// 在Categories实体的主键上添加属性IsVersion=true,即Time Stamp为True
Categories c =
null;
using (NorthwindDataContext ctx =
new NorthwindDataContext())
{
ctx.DeferredLoadingEnabled =
false;
c = ctx.Categories.First();
c.Description =
"webabcd";
}
using (NorthwindDataContext ctx =
new NorthwindDataContext())
{
ctx.Categories.Attach(c,
true);
ctx.SubmitChanges();
}
}
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
//
UPDATE [dbo].[Categories]
// SET [CategoryName] = @p2, [Description] = @p3
// WHERE ([CategoryID] = @p0) AND ([CategoryID] = @p1)
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
// -- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
// -- @p2: Input String (Size = 9; Prec = 0; Scale = 0) [Beverages]
// -- @p3: Input String (Size = 7; Prec = 0; Scale = 0) [webabcd]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
// SET [CategoryName] = @p2, [Description] = @p3
// WHERE ([CategoryID] = @p0) AND ([CategoryID] = @p1)
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
// -- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
// -- @p2: Input String (Size = 9; Prec = 0; Scale = 0) [Beverages]
// -- @p3: Input String (Size = 7; Prec = 0; Scale = 0) [webabcd]
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
缓存
/// <summary>
/// 缓存
/// </summary>
void ShowCache()
{
// 查询后数据会被缓存,如果之后以主键查询的话,会先在缓存里查找
NorthwindDataContext ctx =
new NorthwindDataContext();
Categories category = ctx.Categories.Single(c => c.CategoryName ==
"Beverages");
Categories category2 = ctx.Categories.Single(c => c.CategoryName ==
"Beverages");
Categories category3 = ctx.Categories.Single(c => c.CategoryID == 1);
Categories category4 = ctx.Categories.Single(c => c.CategoryID == 1);
Categories category5 = ctx.Categories.Single(c => c.CategoryID == 2);
Categories category6 = ctx.Categories.Single(c => c.CategoryID == 2);
}
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
![InBlock.gif](https://s1.51cto.com/images/editer/InBlock.gif)
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryName] = @p0
-- @p0: Input String (Size = 9; Prec = 0; Scale = 0) [Beverages]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryName] = @p0
-- @p0: Input String (Size = 9; Prec = 0; Scale = 0) [Beverages]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryID] = @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryName] = @p0
-- @p0: Input String (Size = 9; Prec = 0; Scale = 0) [Beverages]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryName] = @p0
-- @p0: Input String (Size = 9; Prec = 0; Scale = 0) [Beverages]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryID] = @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
OK
[×××]
转载于:https://blog.51cto.com/webabcd/345019