### サンプルコード
//-------------------------------------------------------------- //●skip-take(sql-row_number) // Titles.Select(s => s.Title).Skip(3).Take(3).Dump(); //-------------------------------------------------------------- // --Region Parameters // DECLARE @p0 Int = 3 // DECLARE @p1 Int = 3 // -- EndRegion // SELECT[t1].[title] // FROM( // SELECT ROW_NUMBER() OVER(ORDER BY[t0].[title]) AS[ROW_NUMBER], [t0].[title] // FROM[titles] AS[t0] // ) AS [t1] // WHERE[t1].[ROW_NUMBER] // BETWEEN @p0 + 1 AND @p0 + @p1 // ORDER BY [t1].[ROW_NUMBER] //-------------------------------------------------------------- //●take(sql-top) // Titles.Select(s => s.Title).Take(3).Dump(); //-------------------------------------------------------------- // SELECT TOP (3)[t0].[title] // FROM[titles] AS[t0] //-------------------------------------------------------------- //●contains(sql-like) // Titles.Where(a => a.Title.Contains("Anger")).Select(a => a.Title).Dump(); //-------------------------------------------------------------- // --Region Parameters // DECLARE @p0 VarChar(1000) = '%Anger%' // -- EndRegion // SELECT[t0].[title] // FROM[titles] AS[t0] // WHERE[t0].[title] LIKE @p0 //-------------------------------------------------------------- //●where-contains-array(sql-in) //string[] searchKeys = {"business","mod_cook"} ; //Titles.Where(s => searchKeys.Contains(s.Type)).Select(s => s.Title).Dump(); //-------------------------------------------------------------- // --Region Parameters // DECLARE @p0 NVarChar(1000) = 'business' // DECLARE @p1 NVarChar(1000) = 'mod_cook' // -- EndRegion // SELECT[t0].[title] // FROM[titles] AS[t0] // WHERE[t0].[type] IN(@p0, @p1) //-------------------------------------------------------------- //●where(sql-where) // Titles.Where(t => t.Type == "business").Select(t => t.Title).Dump(); //-------------------------------------------------------------- // --Region Parameters // DECLARE @p0 VarChar(1000) = 'business' // -- EndRegion // SELECT[t0].[title] // FROM[titles] AS[t0] // WHERE[t0].[type] = @p0 //-------------------------------------------------------------- //●where(sql-where) // Titles // .Where(t => t.Pubdate >= DateTime.Parse("1991/06/15")) // .Where(t => t.Pubdate <= DateTime.Parse("1991/06/18")) // .Select(t => new { // Title = t.Title, // Pubdate= t.Pubdate // } ) // .OrderBy(t => t.Pubdate) // .Dump(); //-------------------------------------------------------------- // --Region Parameters // DECLARE @p0 DateTime = '1991-06-18 00:00:00.000' // DECLARE @p1 DateTime = '1991-06-15 00:00:00.000' // -- EndRegion // SELECT[t0].[title] // AS[Title], [t0].[pubdate] // AS[Pubdate] // FROM[titles] AS [t0] // WHERE([t0].[pubdate] <= @p0) AND([t0].[pubdate] >= @p1) // ORDER BY[t0].[pubdate] //-------------------------------------------------------------- //●distinct(sql-distinct) // Titles.Select(t => t.Type).Distinct().Dump(); //-------------------------------------------------------------- // SELECT DISTINCT[t0].[type] // FROM[titles] AS[t0] //-------------------------------------------------------------- //●max,min,avg(sql-max) // Titles.Select(t => t.Price).Max().Dump(); // Titles.Select(t => t.Price).Min().Dump(); // Titles.Select(t => t.Price).Average().Dump(); //-------------------------------------------------------------- // SELECT MAX([t0].[price]) AS[value] FROM[titles] AS[t0] // SELECT MIN([t0].[price]) AS[value] FROM[titles] AS[t0] // SELECT AVG([t0].[price]) AS[value] FROM[titles] AS[t0] //-------------------------------------------------------------- //●count(sql-count) // Titles.Where(t => t.Price >= 20).Select(t => t.Title).Count().Dump(); //-------------------------------------------------------------- // --Region Parameters // DECLARE @p0 Decimal(6, 4) = 20 // -- EndRegion // SELECT COUNT(*) AS[value] FROM[titles] AS[t0] WHERE[t0].[price] >= @p0 //-------------------------------------------------------------- //●group(sql-group) // Titles.GroupBy(t => t.Type).Select(t => t).Dump(); //-------------------------------------------------------------- // SELECT[t0].[type] AS[Key] FROM[titles] AS[t0] GROUP BY[t0].[type] //-------------------------------------------------------------- //●groupby(sql-group) // Titles.GroupBy(t => t.Type) // .Where(grp => grp.Average(a => a.Price) > 20) // .Select(t => t).Dump(); //-------------------------------------------------------------- // --Region Parameters // DECLARE @p0 Decimal(6, 4) = 20 // -- EndRegion // SELECT[t1].[type] // AS[Key] // FROM( // SELECT AVG([t0].[price]) AS[value], [t0].[type] // FROM[titles] AS[t0] // GROUP BY [t0].[type] // ) AS[t1] // WHERE[t1].[value] > @p0 //-------------------------------------------------------------- //●subquerys(sql-subquery-simple) // Titles.Where(t => t.Price == Titles.Max(m => m.Price)).Select(t => t.Title).Dump(); //-------------------------------------------------------------- // SELECT[t0].[title] FROM[titles] AS[t0] // WHERE[t0].[price] = (( // SELECT MAX([t1].[price]) FROM[titles] AS[t1] // )) //-------------------------------------------------------------- //●exists(単、副問合せ） // Titles.Select(s => s) // .Where(t1 => Publishers.Select(t2 => t2.Pub_id).Contains(t1.Pub_id)).Dump(); //-------------------------------------------------------------- // SELECT[t0].[title_id] AS[Title_id], [t0].[title] // AS[Title], [t0].[type] // AS[Type], [t0].[pub_id] // AS[Pub_id], [t0].[price] // AS[Price], [t0].[advance] // AS[Advance], [t0].[royalty] // AS[Royalty], [t0].[ytd_sales] // AS[Ytd_sales], [t0].[notes] // AS[Notes], [t0].[pubdate] // AS[Pubdate] // FROM[titles] AS [t0] WHERE EXISTS( // SELECT NULL AS[EMPTY] // FROM [publishers] AS [t1] // WHERE [t1].[pub_id] = [t0].[pub_id] // ) //-------------------------------------------------------------- //●exists(複、副問合せ） //-------------------------------------------------------------- //Titles.Select(s => s) // .Where(t1 => Publishers.Where(p => p.Country == "USA") // .Any(t2 => t2.Pub_id == t1.Pub_id)).Dump(); //-------------------------------------------------------------- //--Region Parameters //DECLARE @p0 VarChar(1000) = 'USA' //-- EndRegion //SELECT[t0].[title_id] AS[Title_id], [t0].[title] AS[Title], [t0].[type] AS[Type], [t0].[pub_id] AS[Pub_id], [t0].[price] AS[Price], [t0].[advance] AS[Advance], [t0].[royalty] AS[Royalty], [t0].[ytd_sales] AS[Ytd_sales], [t0].[notes] AS[Notes], [t0].[pubdate] AS[Pubdate] // FROM[titles] AS[t0] // WHERE EXISTS( // SELECT NULL AS[EMPTY] // FROM[publishers] AS[t1] // WHERE([t1].[pub_id] = [t0].[pub_id]) AND([t1].[country] = @p0) // ) //-------------------------------------------------------------- //●内部結合(join) //-------------------------------------------------------------- //Titles.Join(Publishers, a => a.Pub_id, b => b.Pub_id, // (a, b) => new { a,b }).Dump(); //-------------------------------------------------------------- // SELECT[t0].[title_id] AS[Title_id], [t0].[title] // AS[Title], [t0].[type] // AS[Type], [t0].[pub_id] // AS[Pub_id], [t0].[price] // AS[Price], [t0].[advance] // AS[Advance], [t0].[royalty] // AS[Royalty], [t0].[ytd_sales] // AS[Ytd_sales], [t0].[notes] // AS[Notes], [t0].[pubdate] // AS[Pubdate], [t1].[pub_id] // AS[Pub_id2], [t1].[pub_name] // AS[Pub_name], [t1].[city] // AS[City], [t1].[state] // AS[State], [t1].[country] // AS[Country] // FROM[titles] AS [t0] // INNER JOIN[publishers] AS [t1] // ON[t0].[pub_id] = [t1].[pub_id] //-------------------------------------------------------------- //●外部結合(outer join) //-------------------------------------------------------------- // Titles.GroupJoin(Publishers, t => t.Pub_id, p => p.Pub_id, // (t, g) => new {temp1 = t, temp2 = g}) // .SelectMany(o => o.temp2.DefaultIfEmpty(), // (x, p2) => new // { // title_id = x.temp1.Title_id, // title_name = x.temp1.Title, // price = x.temp1.Price, // pub_name = (p2 != null ? p2.Pub_name : null) // }).Dump(); //-------------------------------------------------------------- // SELECT[t0].[title_id], [t0].[title] // AS[title_name], [t0].[price], // (CASE // WHEN[t2].[test] IS NOT NULL THEN CONVERT(NVarChar(MAX),[t2].[pub_name]) // ELSE NULL // END) AS[pub_name] // FROM[titles] AS[t0] // LEFT OUTER JOIN( // SELECT 1 AS[test], [t1].[pub_id], [t1].[pub_name] // FROM[publishers] AS[t1] // ) AS[t2] ON[t0].[pub_id] = [t2].[pub_id] //-------------------------------------------------------------- //●Union //-------------------------------------------------------------- // var t1 = Titles.Select(s => s); // var t2 = Titles.Select(s => s); // t1.Union(t2).Dump(); //重複なし(union) // t1.Concat(t2).Dump(); //重複あり(union-all) //-------------------------------------------------------------- // SELECT[t2].[title_id] AS[Title_id], [t2].[title] // AS[Title], [t2].[type] // AS[Type], [t2].[pub_id] // AS[Pub_id], [t2].[price] // AS[Price], [t2].[advance] // AS[Advance], [t2].[royalty] // AS[Royalty], [t2].[ytd_sales] // AS[Ytd_sales], [t2].[notes] // AS[Notes], [t2].[pubdate] // AS[Pubdate] // FROM( // SELECT[t0].[title_id], [t0].[title], [t0].[type], [t0].[pub_id], [t0].[price], [t0].[advance], [t0].[royalty], [t0].[ytd_sales], [t0].[notes], [t0].[pubdate] // FROM [titles] AS [t0] // UNION // SELECT [t1].[title_id], [t1].[title], [t1].[type], [t1].[pub_id], [t1].[price], [t1].[advance], [t1].[royalty], [t1].[ytd_sales], [t1].[notes], [t1].[pubdate] // FROM [titles] AS [t1] // ) AS[t2] //-------------------------------------------------------------- // SELECT[t2].[title_id] AS[Title_id], [t2].[title] // AS[Title], [t2].[type] // AS[Type], [t2].[pub_id] // AS[Pub_id], [t2].[price] // AS[Price], [t2].[advance] // AS[Advance], [t2].[royalty] // AS[Royalty], [t2].[ytd_sales] // AS[Ytd_sales], [t2].[notes] // AS[Notes], [t2].[pubdate] // AS[Pubdate] // FROM( // SELECT[t0].[title_id], [t0].[title], [t0].[type], [t0].[pub_id], [t0].[price], [t0].[advance], [t0].[royalty], [t0].[ytd_sales], [t0].[notes], [t0].[pubdate] // FROM [titles] AS [t0] // UNION ALL // SELECT[t1].[title_id], [t1].[title], [t1].[type], [t1].[pub_id], [t1].[price], [t1].[advance], [t1].[royalty], [t1].[ytd_sales], [t1].[notes], [t1].[pubdate] // FROM [titles] AS [t1] // ) AS[t2]
