linqメモ

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