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

ALTER FUNCTION dbo.fnHelloFunctionScalar 
( 
        @param nvarchar(100) 
) 
RETURNS nvarchar(200) 

AS 

BEGIN 
         
        RETURN 'Hello:' + @param 
         
END

 /// <summary> 
        /// 调用标量值用户自定义函数 
        /// </summary> 
        void Call_HelloFunctionScalar() 
        { 
                NorthwindDataContext ctx = new NorthwindDataContext(); 
 
                result.InnerHtml += ctx.HelloFunctionScalar("webabcd"); 
                result.InnerHtml += "<br />"; 
                result.InnerHtml += "<br />"; 
        }

//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

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

//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

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

//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

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

/// <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; 
        }

//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

/// <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(); 
                } 
        }

//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

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

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