LINQ中连接操作符(九) --单一条件

LINQ中的连接操作符主要包括Join()和GroupJoin()两个.

一、内连接

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConnectOperation
{
    public class Category
    {
        public int Id { get; set; }
        public string CategoryName { get; set; }
        public DateTime CreateTime { get; set; }
    }

    public class Product
    {
        public int Id { get; set; }
        public int CategoryId { get; set; }
        public string Name { get; set; }
        public double Price { get; set; }
        public DateTime CreateTime { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            // 初始化数据
            List<Category> listCategory = new List<Category>()
            {
              new Category(){ Id=1,CategoryName="计算机",CreateTime=DateTime.Now.AddYears(-1)},
              new Category(){ Id=2,CategoryName="文学",CreateTime=DateTime.Now.AddYears(-2)},
              new Category(){ Id=3,CategoryName="高校教材",CreateTime=DateTime.Now.AddMonths(-34)},
              new Category(){ Id=4,CategoryName="心理学",CreateTime=DateTime.Now.AddMonths(-34)}
            };
            List<Product> listProduct = new List<Product>()
            {
               new Product(){Id=1,CategoryId=1, Name="C#高级编程第10版", Price=100.67,CreateTime=DateTime.Now},
               new Product(){Id=2,CategoryId=1, Name="Redis开发和运维", Price=69.9,CreateTime=DateTime.Now.AddDays(-19)},
               new Product(){Id=3,CategoryId=2, Name="活着", Price=57,CreateTime=DateTime.Now.AddMonths(-3)},
               new Product(){Id=4,CategoryId=3, Name="高等数学", Price=97,CreateTime=DateTime.Now.AddMonths(-1)},
               new Product(){Id=5,CategoryId=6, Name="国家宝藏", Price=52.8,CreateTime=DateTime.Now.AddMonths(-1)}
            };

            // 1、查询表达式
            var queryExpress1 = from c in listCategory
                                join p in listProduct on c.Id equals p.CategoryId
                                select new { Id = c.Id, CategoryName = c.CategoryName, ProductName = p.Name, PublishTime = p.CreateTime };
            Console.WriteLine("查询表达式输出写法一:");
            foreach (var item in queryExpress1)
            {
                Console.WriteLine($"id:{item.Id},CategoryName:{item.CategoryName},ProductName:{item.ProductName},PublishTime:{item.PublishTime}");
            }
            var queryExpress2 = from c in listCategory
                                from p in listProduct
                                where c.Id.Equals(p.CategoryId)
                                select new { Id = c.Id, CategoryName = c.CategoryName, ProductName = p.Name, PublishTime = p.CreateTime };
            Console.WriteLine("查询表达式输出写法二:");
            foreach (var item in queryExpress2)
            {
                Console.WriteLine($"id:{item.Id},CategoryName:{item.CategoryName},ProductName:{item.ProductName},PublishTime:{item.PublishTime}");
            }

            // 2、方法语法
            Console.WriteLine("方法语法输出:");
            var queryFun = listCategory.Join(listProduct, c => c.Id, p => p.CategoryId, (c, p) => new { Id = c.Id, CategoryName = c.CategoryName, ProductName = p.Name, PublishTime = p.CreateTime });
            foreach (var item in queryFun)
            {
                Console.WriteLine($"id:{item.Id},CategoryName:{item.CategoryName},ProductName:{item.ProductName},PublishTime:{item.PublishTime}");
            }

            Console.ReadKey();
        }
    }
}

二、左连接

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConnectOperation
{
    public class Category
    {
        public int Id { get; set; }
        public string CategoryName { get; set; }
        public DateTime CreateTime { get; set; }
    }

    public class Product
    {
        public int Id { get; set; }
        public int CategoryId { get; set; }
        public string Name { get; set; }
        public double Price { get; set; }
        public DateTime CreateTime { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            // 初始化数据
            List<Category> listCategory = new List<Category>()
            {
              new Category(){ Id=1,CategoryName="计算机",CreateTime=DateTime.Now.AddYears(-1)},
              new Category(){ Id=2,CategoryName="文学",CreateTime=DateTime.Now.AddYears(-2)},
              new Category(){ Id=3,CategoryName="高校教材",CreateTime=DateTime.Now.AddMonths(-34)},
              new Category(){ Id=4,CategoryName="心理学",CreateTime=DateTime.Now.AddMonths(-34)}
            };
            List<Product> listProduct = new List<Product>()
            {
               new Product(){Id=1,CategoryId=1, Name="C#高级编程第10版", Price=100.67,CreateTime=DateTime.Now},
               new Product(){Id=2,CategoryId=1, Name="Redis开发和运维", Price=69.9,CreateTime=DateTime.Now.AddDays(-19)},
               new Product(){Id=3,CategoryId=2, Name="活着", Price=57,CreateTime=DateTime.Now.AddMonths(-3)},
               new Product(){Id=4,CategoryId=3, Name="高等数学", Price=97,CreateTime=DateTime.Now.AddMonths(-1)},
               new Product(){Id=5,CategoryId=6, Name="国家宝藏", Price=52.8,CreateTime=DateTime.Now.AddMonths(-1)}
            };

            // 1、查询表达式
            var listLeftExpression = from c in listCategory
                                     join p in listProduct on c.Id equals p.CategoryId
                                     into cpList
                                     from cp in cpList.DefaultIfEmpty()
                                     select new
                                     {
                                         Id = c.Id,
                                         CategoryName = c.CategoryName,
                                         ProductName = cp == null ? "无产品名称" : cp.Name,
                                         PublishTime = cp == null ? "无创建时间" : cp.CreateTime.ToString()
                                     };
            Console.WriteLine("查询表达式输出:");
            foreach (var item in listLeftExpression)
            {
                Console.WriteLine($"id:{item.Id},CategoryName:{item.CategoryName},ProductName:{item.ProductName},PublishTime:{item.PublishTime}");
            }

            Console.WriteLine("方法语法输出:");
            // 2、方法语法
            var listLeftFun = listCategory.GroupJoin(listProduct,
                              c => c.Id, p => p.CategoryId,
                              (c, cpList) => cpList.DefaultIfEmpty().Select(cp =>
                              new
                              {
                                  Id = c.Id,
                                  CategoryName = c.CategoryName,
                                  ProductName = cp == null ? "无产品名称" : cp.Name,
                                  PublishTime = cp == null ? "无创建时间" : cp.CreateTime.ToString()
                              })).ToList();
            foreach (var item in listLeftFun)
            {
                foreach (var p in item)
                {
                    Console.WriteLine($"CategoryId:{p.Id},CategoryName:{p.CategoryName},ProduceName:{p.ProductName},PublishTime:{p.PublishTime}");
                }
            }

            Console.ReadKey();
        }
    }
}

三、右连接

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConnectOperation
{
    public class Category
    {
        public int Id { get; set; }
        public string CategoryName { get; set; }
        public DateTime CreateTime { get; set; }
    }

    public class Product
    {
        public int Id { get; set; }
        public int CategoryId { get; set; }
        public string Name { get; set; }
        public double Price { get; set; }
        public DateTime CreateTime { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            // 初始化数据
            List<Category> listCategory = new List<Category>()
            {
              new Category(){ Id=1,CategoryName="计算机",CreateTime=DateTime.Now.AddYears(-1)},
              new Category(){ Id=2,CategoryName="文学",CreateTime=DateTime.Now.AddYears(-2)},
              new Category(){ Id=3,CategoryName="高校教材",CreateTime=DateTime.Now.AddMonths(-34)},
              new Category(){ Id=4,CategoryName="心理学",CreateTime=DateTime.Now.AddMonths(-34)}
            };
            List<Product> listProduct = new List<Product>()
            {
               new Product(){Id=1,CategoryId=1, Name="C#高级编程第10版", Price=100.67,CreateTime=DateTime.Now},
               new Product(){Id=2,CategoryId=1, Name="Redis开发和运维", Price=69.9,CreateTime=DateTime.Now.AddDays(-19)},
               new Product(){Id=3,CategoryId=2, Name="活着", Price=57,CreateTime=DateTime.Now.AddMonths(-3)},
               new Product(){Id=4,CategoryId=3, Name="高等数学", Price=97,CreateTime=DateTime.Now.AddMonths(-1)},
               new Product(){Id=5,CategoryId=6, Name="国家宝藏", Price=52.8,CreateTime=DateTime.Now.AddMonths(-1)}
            };

            // 1、查询表达式
            var listRightExpression = from p in listProduct
                                      join c in listCategory on p.CategoryId equals c.Id
                                      into cpList
                                      from cp in cpList.DefaultIfEmpty()
                                      select new
                                      {
                                          Id = p.Id,
                                          CategoryName = cp == null ? "无分类名称" : cp.CategoryName,
                                          ProductName = p.Name,
                                          PublishTime = p.CreateTime
                                      };
            Console.WriteLine("查询表达式输出:");
            foreach (var item in listRightExpression)
            {
                Console.WriteLine($"id:{item.Id},CategoryName:{item.CategoryName},ProductName:{item.ProductName},PublishTime:{item.PublishTime}");
            }

            Console.WriteLine("方法语法输出:");
            // 2、方法语法
            var listRightFun = listProduct.GroupJoin(listCategory,
                              p => p.CategoryId, c => c.Id,
                              (p, cpList) => cpList.DefaultIfEmpty().Select(cp =>
                              new
                              {
                                  Id = p.Id,
                                  CategoryName = cp == null ? "无分类名称" : cp.CategoryName,
                                  ProductName = p.Name,
                                  PublishTime = p.CreateTime.ToString()
                              })).ToList();
            foreach (var item in listRightFun)
            {
                foreach (var p in item)
                {
                    Console.WriteLine($"id:{p.Id},CategoryName:{p.CategoryName},ProduceName:{p.ProductName},PublishTime:{p.PublishTime}");
                }
            }

            Console.ReadKey();
        }
    }
}