LINQ結合クエリー
14223 ワード
public class Category
{
public int IdCategory { get; set; }
public string Name { get; set; }
}
public class Product
{
public string IdProduct { get; set; }
public int IdCategory { get; set; }
public string Description { get; set; }
}
class Program
{
List<Category> categories = new List<Category>(){
new Category{ IdCategory=1, Name="Pasta"},
new Category{ IdCategory=2, Name="Beverages"},
new Category{ IdCategory=3, Name="Other Food"}
};
List<Product> products = new List<Product>(){
new Product{ IdCategory=1, IdProduct="PASTA01", Description="Tortellini"},
new Product{ IdCategory=1, IdProduct="PASTA02", Description="Spaghtti"},
new Product{ IdCategory=1, IdProduct="PASTA03", Description="Fusilli"},
new Product{ IdCategory=2, IdProduct="BEV01", Description="Water"},
new Product{ IdCategory=2, IdProduct="BEV02", Description="Orange Juice"}
};
static void Main(string[] args)
{
Program p = new Program();
p.InnerJoin();
p.GroupJoin();
p.LeftJoin();
}
public void InnerJoin()
{
Console.WriteLine("------InnerJoin Test-----");
//
var categoriesAndProducts =
categories.Join(products,
c => c.IdCategory,
p => p.IdCategory,
(c, p) => new { c.IdCategory, CategoryName = c.Name, ProductDescription = p.Description }
);
//
//var categoriesAndProducts =
// from c in categories
// join p in products on c.IdCategory equals p.IdCategory
// select new { c.IdCategory, CategoryName = c.Name, ProductDescription = p.Description };
// IdCategory=3 ， Other Food Category
foreach (var item in categoriesAndProducts)
{
Console.WriteLine(item);
}
}
// SQL
public void GroupJoin()
{
Console.WriteLine("------GroupJoin Test-----");
//
var categoriesAndProducts =
categories
.GroupJoin(products,
c => c.IdCategory,
p => p.IdCategory,
(c, p) => new { c.IdCategory, CategoryName = c.Name, Products = p }
);
//
//var categoriesAndProducts =
// from c in categories
// join p in products on c.IdCategory equals p.IdCategory
// into productByCategory
// select new { c.IdCategory, CategoryName = c.Name, Products = productByCategory };
foreach (var category in categoriesAndProducts)
{
Console.WriteLine("{0} - {1}", category.IdCategory, category.CategoryName);
foreach (var product in category.Products)
Console.WriteLine("\t{0}", product.Description);
}
}
// ， ，
public void LeftJoin()
{
Console.WriteLine("------LeftJoin Test-----");
//
var categoriesAndProducts =
categories
.GroupJoin(products,
c => c.IdCategory,
p => p.IdCategory,
(c, p) => new { c.IdCategory, CategoryName = c.Name, Products = p }
);
foreach (var g in categoriesAndProducts)
{
var temp = g.Products
.DefaultIfEmpty(new Product { IdCategory = 0, Description = string.Empty, IdProduct = string.Empty })
.Select(p => new { g.IdCategory, g.CategoryName, p.Description });
foreach (var item in temp)
{
Console.WriteLine(item);
}
}
//
//var categoriesAndProducts =
// from c in categories
// join p in products on c.IdCategory equals p.IdCategory
// into productByCategory
// from pc in productByCategory.DefaultIfEmpty(
// new Product { IdCategory = 0, Description = string.Empty, IdProduct = string.Empty })
// select new { c.IdCategory, CategoryName = c.Name, ProductDescription = pc.Description };
//// IdCategory=3 ，
//foreach (var item in categoriesAndProducts)
//{
// Console.WriteLine(item);
//}
}
}