C#の概念 LINQ（カスタム結合）

　join句ではできない結合について。

成果物

• github

コード

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},

所感

　クエリ構文だけでは表現できないのが残念。だが、そんなに難しいわけでもない。

対象環境

• 2019-12-11
• 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