join
句ではできない結合について。
成果物
コード
using System; using System.Collections.Generic; using System.Linq; namespace Concept.Linq.Lesson0 { class Product { public string Name { get; set; } public int CategoryID { get; set; } } class Category { public string Name { get; set; } public int ID { get; set; } } class Main { private List<Category> categories = new List<Category>() { new Category(){Name="Beverages", ID=001}, new Category(){ Name="Condiments", ID=002}, new Category(){ Name="Vegetables", ID=003}, }; private List<Product> products = new List<Product>() { new Product{Name="Tea", CategoryID=001}, new Product{Name="Mustard", CategoryID=002}, new Product{Name="Pickles", CategoryID=002}, new Product{Name="Carrots", CategoryID=003}, new Product{Name="Bok Choy", CategoryID=003}, new Product{Name="Peaches", CategoryID=005}, new Product{Name="Melons", CategoryID=005}, new Product{Name="Ice Cream", CategoryID=007}, new Product{Name="Mackerel", CategoryID=012}, }; public void Run() { CrossJoin(); NonEquijoin(); } void CrossJoin() { var crossJoinQuery = from c in categories from p in products select new { c.ID, p.Name }; Console.WriteLine("Cross Join Query:"); foreach (var v in crossJoinQuery) { Console.WriteLine($"{v.ID,-5}{v.Name}"); } } void NonEquijoin() { var nonEquijoinQuery = from p in products let catIds = from c in categories select c.ID where catIds.Contains(p.CategoryID) == true select new { Product = p.Name, CategoryID = p.CategoryID }; Console.WriteLine("Non-equijoin query:"); foreach (var v in nonEquijoinQuery) { Console.WriteLine($"{v.CategoryID,-5}{v.Product}"); } } } }
Cross Join Query: 1 Tea 1 Mustard 1 Pickles 1 Carrots 1 Bok Choy 1 Peaches 1 Melons 1 Ice Cream 1 Mackerel 2 Tea 2 Mustard 2 Pickles 2 Carrots 2 Bok Choy 2 Peaches 2 Melons 2 Ice Cream 2 Mackerel 3 Tea 3 Mustard 3 Pickles 3 Carrots 3 Bok Choy 3 Peaches 3 Melons 3 Ice Cream 3 Mackerel Non-equijoin query: 1 Tea 2 Mustard 2 Pickles 3 Carrots 3 Bok Choy
A. 交差結合
from c in categories from p in products select new { c.ID, p.Name };
交差結合とは積集合を作る。(集合Aと集合Bの全パターンでできた集合Cを作る)
B. 非等値結合
クエリは以下。
from p in products let catIds = from c in categories select c.ID where catIds.Contains(p.CategoryID) == true select new { Product = p.Name, CategoryID = p.CategoryID };
categories
に含まれるID
のみ対象とする。
categories
に含まれるID
は以下の通り、001
, 002
, 003
の3つ。
new Category(){Name="Beverages", ID=001}, new Category(){ Name="Condiments", ID=002}, new Category(){ Name="Vegetables", ID=003},
そのID
を持つproducts
は以下。それらだけを対象とする。
new Product{Name="Tea", CategoryID=001}, new Product{Name="Mustard", CategoryID=002}, new Product{Name="Pickles", CategoryID=002}, new Product{Name="Carrots", CategoryID=003}, new Product{Name="Bok Choy", CategoryID=003},
そのID
を持たないproducts
は以下。それらは対象外。
new Product{Name="Peaches", CategoryID=005}, new Product{Name="Melons", CategoryID=005}, new Product{Name="Ice Cream", CategoryID=007}, new Product{Name="Mackerel", CategoryID=012},
所感
クエリ構文だけでは表現できないのが残念。だが、そんなに難しいわけでもない。
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
- bash 5.0.3(1)-release
$ uname -a Linux raspberrypi 4.19.75-v7l+ #1270 SMP Tue Sep 24 18:51:41 BST 2019 armv7l GNU/Linux