LINQのクエリーサンプル

サンプルコード

//----------------------------------------
// select そんまま返す
//---------------------------------------- 
var q1 = from sa in Sales select sa;
q1.Dump();
/*
   SELECT [t0].[stor_id] AS [Stor_id], [t0].[ord_num] AS [Ord_num], [t0].[ord_date] AS [Ord_date], [t0].[qty] AS [Qty], [t0].[payterms] AS [Payterms], [t0].[title_id] AS [Title_id]
   FROM [sales] AS [t0]
*/

//----------------------------------------
// select newして返す
//---------------------------------------- 
var q2 = from st in Stores select new { StoreName = st.Stor_name };
q2.Dump();
/*
   SELECT [t0].[stor_name] AS [StoreName]
   FROM [stores] AS [t0]
*/

//----------------------------------------
// distinctで返す､クエリー式にdistinctがないため､このように書きます
//----------------------------------------
var q3 = (from sa in Sales
          join st in Stores on sa.Stor_id equals st.Stor_id
          select new
          {
              StoreName = st.Stor_name
          }).Distinct();

q3.Dump();
/*
   SELECT DISTINCT [t1].[stor_name] AS [StoreName]
   FROM [sales] AS [t0]
   INNER JOIN [stores] AS [t1] ON [t0].[stor_id] = [t1].[stor_id]
*/

//----------------------------------------
// where
//----------------------------------------
var q4 = from st in Stores where st.State == "CA" && st.Zip.StartsWith("9") select st;
q4.Dump();
/*
   SELECT [t0].[stor_id] AS [Stor_id], [t0].[stor_name] AS [Stor_name], [t0].[stor_address] AS [Stor_address], [t0].[city] AS [City], [t0].[state] AS [State], [t0].[zip] AS [Zip]
   FROM [stores] AS [t0]
   WHERE ([t0].[state] = @p0) AND ([t0].[zip] LIKE @p1)
*/

//----------------------------------------
// group
//----------------------------------------
var q5 = from st in Stores group st by st.State;
q5.Dump();
/*
   SELECT [t0].[state] AS [Key]
   FROM [stores] AS [t0]
   GROUP BY [t0].[state]
*/

//----------------------------------------
// group
//----------------------------------------
(from s in Sales orderby s.Qty descending select s).Dump();
/*
   SELECT [t0].[stor_id] AS [Stor_id], [t0].[ord_num] AS [Ord_num], [t0].[ord_date] AS [Ord_date], [t0].[qty] AS [Qty], [t0].[payterms] AS [Payterms], [t0].[title_id] AS [Title_id]
   FROM [sales] AS [t0]
   ORDER BY [t0].[qty] DESC
*/

//----------------------------------------
// into(クエリ結果を一時的に保存)
//----------------------------------------
var q6 = from st in Stores group st by st.State into g orderby g.Key descending select g;
q6.Dump();
/*
   SELECT [t1].[state] AS [Key]
   FROM (
       SELECT [t0].[state]
       FROM [stores] AS [t0]
       GROUP BY [t0].[state]
       ) AS [t1]
   ORDER BY [t1].[state] DESC
*/

//----------------------------------------
// let(一時変数)
//----------------------------------------
var q7 = from sa in Sales
         let qtyLevel = (sa.Qty / 10) * 10
         group sa by qtyLevel into tempSales
         orderby tempSales.Key ascending
         select new
         {
            QtyLevel = tempSales.Key,
             Data = (from ts in tempSales orderby ts.Qty descending select ts)
         };
q7.Dump();

//----------------------------------------
//　内部結合
//----------------------------------------
var q8 = from sale in Sales
         join title in Titles on sale.Title_id equals title.Title_id
         orderby sale.Qty descending
         select new
         {
             Title = title.Title,
             Qty = sale.Qty
         };
q8.Dump();

//----------------------------------------
// 内部結合2
//----------------------------------------
var ij = from s in Sales
         join t in Titles on s.Title_id equals t.Title_id
         select new
         {
             TitleId = s.Title_id,
            OrderNum = s.Ord_num,
            Title = t.Title
         };

ij.Dump();
/*
   SELECT [t0].[title_id] AS [TitleId], [t0].[ord_num] AS [OrderNum], [t1].[title] AS [Title]
   FROM [sales] AS [t0]
   INNER JOIN [titles] AS [t1] ON [t0].[title_id] = [t1].[title_id]
*/



//----------------------------------------
//　グループ化結合
//----------------------------------------
var q9 = from st in Stores
         group st by st.State into tempGroup
         orderby tempGroup.Key descending
         select new
         {
            State = tempGroup.Key,
             Group = (from g in tempGroup orderby g.Stor_id descending select g)
         };
q9.Dump();

//----------------------------------------
//　外部結合
//----------------------------------------
var lr = from sa in Sales
         join t in Titles on sa.Title_id equals t.Title_id into tempTitle
         from title in tempTitle.DefaultIfEmpty()
         join st in Stores on sa.Stor_id equals st.Stor_id into tempStore
         from store in tempStore.DefaultIfEmpty()
         select new
         {
             TitleId = sa.Title_id,
            OrderNum = sa.Ord_num,
            Title = title.Title,
             StoreName = store.Stor_name
         };

lr.Dump();
/*
   SELECT [t0].[title_id] AS [TitleId], [t0].[ord_num] AS [OrderNum], [t1].[title] AS [Title], [t2].[stor_name] AS [StoreName]
   FROM [sales] AS [t0]
   LEFT OUTER JOIN [titles] AS [t1] ON [t0].[title_id] = [t1].[title_id]
   LEFT OUTER JOIN [stores] AS [t2] ON [t0].[stor_id] = [t2].[stor_id]
*/


//----------------------------------------
//　uion(重複なし)
//----------------------------------------
var q10 = (from store in Stores select store.State).Union(from store in Stores select store.State);
q10.Dump();

//----------------------------------------
//　uion all(重複あり)
//----------------------------------------
var q11 = (from store in Stores select store.State).Concat(from store in Stores select store.State);
q11.Dump();

//----------------------------------------
//　except､左側のシーケンスにある要素のみを抽出
//----------------------------------------
var q12 = (from store in Stores select store.State).Except(from store in Stores where store.State == "WA" select store.State);
q12.Dump();

//----------------------------------------
//　incorrect､両方のシーケンスにある要素を抽出
//----------------------------------------
var q13 = (from store in Stores where store.State == "CA" select store.State).Intersect(from store in Stores where store.State == "CA" select store.State);
q13.Dump();

//----------------------------------------
//　count
//----------------------------------------
var q14 = from store in Stores select store;
q14.Count().Dump();
q14.Count(a => a.State == "WA").Dump();

//----------------------------------------
//　sum
//----------------------------------------
var q16 = from sale in Sales
          group sale by sale.Stor_id into tempGroup
          orderby tempGroup.Sum(a => a.Qty)
          select new {
            StoreId = tempGroup.Key,
            count = tempGroup.Sum(a => a.Qty)
          };
          
q16.Dump();
/*
   SELECT [t1].[stor_id] AS [StoreId], [t1].[value2] AS [count]
   FROM (
       SELECT SUM(CONVERT(Int,[t0].[qty])) AS [value], SUM(CONVERT(Int,[t0].[qty])) AS [value2], [t0].[stor_id]
       FROM [sales] AS [t0]
       GROUP BY [t0].[stor_id]
       ) AS [t1]
   ORDER BY [t1].[value]
*/

//----------------------------------------
//　max,min
//----------------------------------------
var q17 = from sale in Sales
          group sale by sale.Stor_id into tempGroup
          orderby tempGroup.Max(a => a.Qty) descending
          select new
          {
            StoreId = tempGroup.Key,
            MaxCount = tempGroup.Max(a => a.Qty),
            MinCount = tempGroup.Min(a => a.Qty),
            DataCount = tempGroup.Count()
          };

q17.Dump();
/*
   SELECT [t1].[stor_id] AS [StoreId], [t1].[value2] AS [MaxCount], [t1].[value3] AS [MinCount], [t1].[value4] AS [DataCount]
   FROM (
       SELECT MAX([t0].[qty]) AS [value], MAX([t0].[qty]) AS [value2], MIN([t0].[qty]) AS [value3], COUNT(*) AS [value4], [t0].[stor_id]
       FROM [sales] AS [t0]
       GROUP BY [t0].[stor_id]
       ) AS [t1]
   ORDER BY [t1].[value] DESC
*/

//----------------------------------------
//　average
//----------------------------------------
var q18 = from sale in Sales
          group sale by sale.Stor_id into tempGroup
          orderby tempGroup.Sum(a => a.Qty)
          select new
          {
            StoreId = tempGroup.Key,
            count = tempGroup.Average(a => a.Qty)
          };

q18.Dump();
/*
   SELECT [t1].[stor_id] AS [StoreId], [t1].[value2] AS [count]
   FROM (
       SELECT SUM(CONVERT(Int,[t0].[qty])) AS [value], AVG(CONVERT(Int,[t0].[qty])) AS [value2], [t0].[stor_id]
       FROM [sales] AS [t0]
       GROUP BY [t0].[stor_id]
       ) AS [t1]
   ORDER BY [t1].[value]
*/

//----------------------------------------
//　range
//----------------------------------------
// foreach (var i in Enumerable.Range(0, 10))
// {
//     Console.WriteLine(i.ToString());
// }


//----------------------------------------
//　repeat
//----------------------------------------
// foreach (var i in Enumerable.Repeat(1,100))
// {
//     Console.WriteLine(i.ToString());
// }


//----------------------------------------
//　any､合致する要素がシーケンス内に存在するか否かを確認
//----------------------------------------
var q19 = from s in Sales where s.Qty >= 75 select s;
q19.Any().Dump();   // true
q19.Any(a => a.Qty >=75 ).Dump();    //true

//----------------------------------------
//　all､シーケンスの全要素が指定された条件に合致しているか否かを判別
//----------------------------------------
var q20 = from s in Sales where s.Stor_id == "6380" select s;
q20.All(a => a.Stor_id == "6380").Dump(); // true
q20.All(a => a.Stor_id == "9999").Dump();   // false

//----------------------------------------
//　contain
//----------------------------------------
var q21 = from st in Stores where st.Stor_name.Contains("Book") select st;
q21.Dump();

//----------------------------------------
//　take､skipはクエリーにはtakeはない
//----------------------------------------
var q22 = (from st in Stores orderby st.Stor_id select st).Skip(2).Take(3);
q22.Dump();