query.aspx.cs

数据库连接查询

//                     group o by new { 条件 = o.Freight > 100 } into g
//                     select new
//                     {
//                         数量 = g.Count(),
//                         是否超重 = g.Key.条件 ? "是" : "否"
//                     };
//SELECT 
//    (CASE 
//        WHEN [t2].[value2] = 1 THEN @p1
//        ELSE @p2
//     END) AS [value], [t2].[value] AS [数量]
//FROM (
//    SELECT COUNT(*) AS [value], [t1].[value] AS [value2]
//    FROM (
//        SELECT 
//            (CASE 
//                WHEN [t0].[Freight] > @p0 THEN 1
//                WHEN NOT ([t0].[Freight] > @p0) THEN 0
//                ELSE NULL
//             END) AS [value]
//        FROM [dbo].[Orders] AS [t0]
//        ) AS [t1]
//    GROUP BY [t1].[value]
//    ) AS [t2]
//-- @p0: Input Currency (Size = 0; Prec = 19; Scale = 4) [100]
//-- @p1: Input String (Size = 1; Prec = 0; Scale = 0) [是]
//-- @p2: Input String (Size = 1; Prec = 0; Scale = 0) [否]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 过滤相同项 = (from c in ctx.Customers orderby c.Country select c.Country).Distinct();
//SELECT DISTINCT [t0].[Country]
//FROM [dbo].[Customers] AS [t0]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 连接并且过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") select c).Union
//            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);
//SELECT [t3].[CustomerID], [t3].[CompanyName], [t3].[ContactName], [t3].[ContactTitle], [t3].[Address], [t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country], [t3].[Phone], [t3].[Fax]
//FROM (
//    SELECT [t2].[CustomerID], [t2].[CompanyName], [t2].[ContactName], [t2].[ContactTitle], [t2].[Address], [t2].[City], [t2].[Region], [t2].[PostalCode], [t2].[Country], [t2].[Phone], [t2].[Fax]
//    FROM (
//        SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
//        FROM [dbo].[Customers] AS [t0]
//        WHERE [t0].[City] LIKE @p0
//        UNION
//        SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
//        FROM [dbo].[Customers] AS [t1]
//        WHERE [t1].[ContactName] LIKE @p1
//        ) AS [t2]
//    ) AS [t3]
//ORDER BY [t3].[ContactName]
//-- @p0: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]
//-- @p1: Input String (Size = 2; Prec = 0; Scale = 0) [A%]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 连接并且不过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") select c).Concat
//            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);
//SELECT [t3].[CustomerID], [t3].[CompanyName], [t3].[ContactName], [t3].[ContactTitle], [t3].[Address], [t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country], [t3].[Phone], [t3].[Fax]
//FROM (
//    SELECT [t2].[CustomerID], [t2].[CompanyName], [t2].[ContactName], [t2].[ContactTitle], [t2].[Address], [t2].[City], [t2].[Region], [t2].[PostalCode], [t2].[Country], [t2].[Phone], [t2].[Fax]
//    FROM (
//        SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
//        FROM [dbo].[Customers] AS [t0]
//        WHERE [t0].[City] LIKE @p0
//        UNION ALL
//        SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
//        FROM [dbo].[Customers] AS [t1]
//        WHERE [t1].[ContactName] LIKE @p1
//        ) AS [t2]
//    ) AS [t3]
//ORDER BY [t3].[ContactName]
//-- @p0: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]
//-- @p1: Input String (Size = 2; Prec = 0; Scale = 0) [A%]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 取相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Intersect
//            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);
//SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
//FROM (
//    SELECT DISTINCT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
//    FROM [dbo].[Customers] AS [t0]
//    ) AS [t1]
//WHERE (EXISTS(
//    SELECT NULL AS [EMPTY]
//    FROM [dbo].[Customers] AS [t2]
//    WHERE ([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName] LIKE @p0)
//    )) AND ([t1].[City] LIKE @p1)
//ORDER BY [t1].[ContactName]
//-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [A%]
//-- @p1: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 排除相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Except
//            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);
//SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
//FROM (
//    SELECT DISTINCT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
//    FROM [dbo].[Customers] AS [t0]
//    ) AS [t1]
//WHERE (NOT (EXISTS(
//    SELECT NULL AS [EMPTY]
//    FROM [dbo].[Customers] AS [t2]
//    WHERE ([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName] LIKE @p0)
//    ))) AND ([t1].[City] LIKE @p1)
//ORDER BY [t1].[ContactName]
//-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [A%]
//-- @p1: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var in操作 = from c in ctx.Customers
//                   where
//                       (from o in ctx.Orders group o by o.CustomerID into o where o.Count() > 5 select o.Key).Contains(c.CustomerID)
//                   select c;
//SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
//FROM [dbo].[Customers] AS [t0]
//WHERE EXISTS(
//    SELECT NULL AS [EMPTY]
//    FROM (
//        SELECT COUNT(*) AS [value], [t1].[CustomerID]
//        FROM [dbo].[Orders] AS [t1]
//        GROUP BY [t1].[CustomerID]
//        ) AS [t2]
//    WHERE ([t2].[CustomerID] = [t0].[CustomerID]) AND ([t2].[value] > @p0)
//    )
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

        //var in操作2 = from c in ctx.Customers
        //            where new string[] { "Brandenburg", "Cowes", "Stavern" }.Contains(c.City)
        //            select c;
//       SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
//FROM [dbo].[Customers] AS [t0]
//WHERE [t0].[City] IN (@p0, @p1, @p2)
//-- @p0: Input String (Size = 11; Prec = 0; Scale = 0) [Brandenburg]
//-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [Cowes]
//-- @p2: Input String (Size = 7; Prec = 0; Scale = 0) [Stavern]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1


//        var innerjoin = from p in ctx.Products 
//                        join c in ctx.Categories 
//                        on p.CategoryID equals c.CategoryID
//                        select p.ProductName;
//SELECT COUNT(*) AS [value]
//FROM [dbo].[Products] AS [t0]
//INNER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[CategoryID])
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var leftjoin = from p in ctx.Products
//                       join c in ctx.Categories
//                       on p.CategoryID equals c.CategoryID
//                       into pro
//                       from x in pro.DefaultIfEmpty()
//                       select p.ProductName;
//SELECT COUNT(*) AS [value]
//FROM [dbo].[Products] AS [t0]
//LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[CategoryID])
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

        //GridView1.DataSource = 构建匿名类型1;
        //GridView1.DataBind();
        ////var p = from c in ctx.Customers where c.CustomerID.StartsWith("A") select c;
        ////foreach (var cus in p)
        ////    cus.Fax = "aa";
        ////ctx.SubmitChanges();
        //sw.Close();
        
    }
}