LINQ: Grouping and Joining Data

1 篇文章 / 0 new

發表回應前，請先登入

週一, 2015-06-15 14:10

author

使用 MsSQL 範例資料庫

Nested Queries

var CategoriesAndProducts = from category in Categories
select category;

SELECT t0.* FROM Categories AS t0

SELECT t0.CategoryName
    ,(SELECT COUNT(*) FROM Products AS t1
    WHERE t1.CategoryID = t0.CategoryID
    ) AS ProductCount
FROM Categories AS t0

var CategoriesAndProducts = from category in Categories
   select new
   {
      CategoryName = category.CategoryName,
      ProductCount =
         (from product in Products
          where product.Category == category
          select product).Count(),
      Products =
         (from product in Products
          where product.Category == category
          select product)
   };

SELECT t0.CategoryName
    ,(SELECT COUNT(*) FROM Products AS t2 WHERE t2.CategoryID = t0.CategoryID) AS ProductCount
    , t1.*
    ,(SELECT COUNT(*) FROM Products AS t3 WHERE t3.CategoryID = t0.CategoryID) AS VALUE
FROM Categories AS t0
LEFT OUTER JOIN Products AS t1 ON t1.CategoryID = t0.CategoryID
ORDER BY t0.CategoryID, t1.ProductID

Grouping Data

var CategoriesAndProducts = Products.GroupBy(p => p.Category)
        .Select(catGroup => new {
                 CategoryName = catGroup.Key.CategoryName,
                 ProductCount = catGroup.Count(),
                 Products = catGroup
        }
);
或則
var CategoriesAndProducts = from product in Products
      group product by product.Category into categoryProducts
      select new
      {
         CategoryName = categoryProducts.Key.CategoryName,
         ProductCount = categoryProducts.Count(),
         Products = categoryProducts
      };

SELECT t3.CategoryName, t1.VALUE AS ProductCount, t3.CategoryID, t3.Description, t3.Picture
FROM (
    SELECT COUNT(*) AS VALUE, t0.CategoryID
    FROM Products AS t0
    GROUP BY t0.CategoryID
    ) AS t1
LEFT OUTER JOIN (
    SELECT t2.CategoryID, t2.CategoryName, t2.Description, t2.Picture
    FROM Categories AS t2
    ) AS t3 ON t3.CategoryID = t1.CategoryID

Joins

下為 Group Join, Inner Join, Left Outer Join, Cross Join 這類 LINQ 的寫法

Group Joins

var CategoriesAndProducts = from category in Categories
      join product in Products on
         category equals product.Category into categoryProducts
      select new
      {
         CategoryName = category.CategoryName,
         ProductCount = categoryProducts.Count(),
         Products = categoryProducts
      };

SELECT t0.CategoryName
    , (SELECT COUNT(*) FROM Products AS t2 WHERE t0.CategoryID = t2.CategoryID) AS ProductCount
    , t1.*
    , (SELECT COUNT(*) FROM Products AS t3 WHERE t0.CategoryID = t3.CategoryID) AS VALUE
FROM Categories AS t0
LEFT OUTER JOIN Products AS t1 ON t0.CategoryID = t1.CategoryID
ORDER BY t0.CategoryID, t1.ProductID

Inner Joins

var CategoriesAndProducts = from category in Categories
      join product in Products on
         category equals product.Category
      select new
      {
         category.CategoryName,
         product
      };

SELECT t0.CategoryName, t1.* FROM Categories AS t0
INNER JOIN Products AS t1 ON t0.CategoryID = t1.CategoryID

Left Outer Joins

var CategoriesAndProducts = from category in Categories
    join product in Products on category equals product.Category into categoryProducts
    from product in categoryProducts.DefaultIfEmpty()
select new
{
   category.CategoryName,
   product
};

SELECT t0.CategoryName, t2.*
FROM Categories AS t0
LEFT OUTER JOIN (SELECT t1.* FROM Products AS t1) AS t2 ON t0.CategoryID = t2.CategoryID

Cross Joins

var CategoriesAndProducts = from category in Categories
      from product in Products
      select new
      {
         category.CategoryName,
         product.ProductName,
         product.UnitPrice
      };

SELECT t0.CategoryName, t1.ProductName, t1.UnitPrice
FROM Categories AS t0, Products AS t1

from
https://msdn.microsoft.com/en-us/library/bb397895.aspx
http://www.4guysfromrolla.com/articles/080509-1.aspx
https://smehrozalam.wordpress.com/2009/06/10/c-left-outer-joins-with-linq/
http://www.thinqlinq.com/Post.aspx/Title/Left-Outer-Joins-in-LINQ-with-E...

關鍵字:

LINQ