.NET：LINQ

语言集成查询（Language Integrated Query，缩写：LINQ），是微软的一项技术，新增的一种自然查询语言。

LINQ加载数据

---

LINQ特殊化操作符

---

namespace Test
{
    public class demo2
    {
        /// <summary>
        /// LINQ特殊化操作符
        /// </summary>
        public static void test()
        {
            var customers = new List<Customer>
            {
                new Customer{ Name = "张三", Age = 20, Visible = false},
                new Customer{ Name = "李四", Age = 18},
                new Customer{ Name = "王五", Age = 22}
            };

            var query = from c in customers
                        where c.Age < 22
                        select c;

            foreach (var customer in query)
            {
                Console.WriteLine(customer);
            }
        }
    }

    public interface IVisible
    { 
        bool Visible { get; set; }
    }

    public class Customer : IVisible
    { 
        public string Name { get; set; }
        public int Age { get; set; }
        public bool Visible { get; set; }

        public Customer()
        {
            Visible = true;
        }

        public override string ToString()
        {
            return $"{Name} 今年 {Age} 岁了";
        }
    }

    public static class demo2Extensions
    {
        public static IEnumerable<TSource> Where<TSource>(
            this IEnumerable<TSource> source,
            Func<TSource, bool> predicate) where TSource : IVisible
        {
            return Enumerable.Where(source, item => item.Visible && predicate(item));
        }
    }
}

LINQ扩展

---

namespace Test
{
    public static class demo1
    {
        /// <summary>
        /// LINQ扩展函数
        /// </summary>
        public static void test()
        {
            var stock = new Stock { Name = "Stock Demo" };
            stock.Quotes = new List<Quote>
            {
                new Quote{Stock = stock, Price = 200, Date = DateTime.Parse("2020/3/29") },
                new Quote{Stock = stock, Price = 150, Date = DateTime.Parse("2020/3/21") },
                new Quote{Stock = stock, Price = 220, Date = DateTime.Parse("2020/3/24") },
                new Quote{Stock = stock, Price = 180, Date = DateTime.Parse("2020/3/26") },
                new Quote{Stock = stock, Price = 100, Date = DateTime.Parse("2020/3/23") }
            };
            Console.WriteLine($"Stock:{stock}");

            var minQuote = stock.Quotes.Minitem(q => q.Price);
            Console.WriteLine(minQuote.Price);
        }
    }

    public class Quote
    {
        public Stock Stock { get; set; }
        public decimal Price { get; set; }
        public DateTime Date { get; set; }

    }

    public class Stock 
    {
        public string Name { get; set; }
        public List<Quote> Quotes { get; set; }
        public override string ToString()
        {
            return $"{Name}: MIN {Quotes.Min(q => q.Price)} - MAX {Quotes.Max(q => q.Price)}";
        }
    }

    public static class demo1Extensions
    {
        public static Quote MinPrice(this IEnumerable<Quote> source)
        {
            return source.Aggregate((t,s) => t.Price < s.Price ? t : s);
        }

        public static TSource Minitem<TSource, TCompareValue>(this IEnumerable<TSource> source,
            Func<TSource, TCompareValue> comparerExpression)
        {
            var comparer = Comparer<TCompareValue>.Default;
            return source.Aggregate((minValue, item) =>
            {
                var result = comparer.Compare(comparerExpression(minValue), comparerExpression(item));
                return result < 0 ? minValue : item;
            });
        }
    }
}

PLINQ

---

namespace Test
{
    public static class demo4
    {
        /// <summary>
        /// PLINQ
        /// 当数据量少的时候使用PLINQ开线程需要时间，反倒运行时间比普通LINQ更长
        /// PLINQ只发生在内存中，sql的查询在数据库中，所以不支持to sql
        /// 循环需要是线程安全的，尽量避免在lock中执行任务等待，可能导致死锁
        /// PLINQ和其他LINQ提供程序一起使用LINQ时，必须确保他们是以兼容的方式来操作的，PLINQ只支持以IEnumerable作为源
        /// </summary>
        public static void test1()
        {
            var students = new ConcurrentDictionary<int, Student>();
            //并行生成1000万条数据
            Parallel.For(0, 10_000_000, i =>
              {
                  var single = new Student
                  {
                      Id = i,
                      Name = "name" + i,
                      Age = i % 100,
                      CreateTime = DateTime.Now.AddSeconds(i)
                  };
                  students.TryAdd(i,single);
            });
            Console.WriteLine("数据已生成");

            //PLINQ查询
            var watch = Stopwatch.StartNew();
            watch.Start();
            var query1 = (from n in students.Values.AsParallel()
                          where n.Age > 18 && n.Age < 80 
                         select n).ToList();
            Console.WriteLine("PLinq耗时：{0}",watch.ElapsedMilliseconds);

            //LINQ查询
            var query2 = (from n in students.Values
                          where n.Age > 18 && n.Age < 80
                          select n).ToList();
            Console.WriteLine("Linq耗时：{0}",watch.ElapsedMilliseconds);
        }
    }

    public class Student
    { 
        public int Id { get;set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public DateTime CreateTime { get; set; }
    }
}

MapReduce

---

namespace Test
{
    public static class demo5
    {
        /// <summary>
        /// MapReduce编程模型
        /// 运行并行的方式处理大数据
        /// 基本思想：将数据处理的问题分解为两个独立的且可以并行执行的操作
        /// 1.Map映射，对数据源进行操作，为每个数据项计算出一个键值，运行的结果是一个键值对的集合，并且根据键进行分组
        /// 2.Reduce规约，对Map产生的键值进行操作，返回结果值
        /// </summary>
        public static void test()
        {
            var words = File.ReadAllText("Data.txt").Split(' ');
            var map = words.AsParallel().ToLookup(p => p, k => 1);
            var reduce = from IGrouping<string, int> wordMap in map.AsParallel()
                         where wordMap.Count() > 1
                         select new { Word = wordMap.Key, Count = wordMap.Count() };
            foreach (var word in reduce)
            {
                Console.WriteLine("word:{0},count:{1}",word.Word,word.Count);
            }
        }
    }
}

LINQ提供程序实现

---

LINQ中的两种数据源类型

• IEnumerable 针对内存对象的数据源
• IQueryable 针对外部数据源

IQueryable 源码

    /// <summary>
    /// Provides functionality to evaluate queries against a specific data source wherein the type of the data is not specified.
    /// </summary>
    public interface IQueryable : IEnumerable
    {
        /// <summary>
        /// Gets the expression tree that is associated with the instance of <see cref="IQueryable"/>.
        /// </summary>
        Expression Expression { get; }

        /// <summary>
        /// Gets the type of the element(s) that are returned when the expression tree associated with this instance of <see cref="IQueryable"/> is executed.
        /// </summary>
        Type ElementType { get; }

        /// <summary>
        /// Gets the query provider that is associated with this data source.
        /// </summary>
        IQueryProvider Provider { get; }
    }

实现扩展

namespace Test
{
    public static class demo6
    {
        public static void test()
        { 
            
        }
    }

    public class CustomQuery<T> : IQueryable<T>
    {
        private readonly BaseProvider _provider;

        public Type ElementType => typeof(T);
        public Expression Expression { get; }
        public IQueryProvider Provider => _provider;

        public CustomQuery(BaseProvider provider)
        {
            _provider = provider ?? throw new ArgumentNullException(nameof(provider));
            Expression = Expression.Constant(this);
        }

        public CustomQuery(BaseProvider provider, Expression expression)
        {
            if (expression == null)
            {
                throw new ArgumentNullException(nameof(expression));
            }

            if (!typeof(IQueryable<T>).IsAssignableFrom(expression.Type))
            {
                throw new ArgumentOutOfRangeException(nameof(expression));
            }

            Expression = expression;
            _provider = provider ?? throw new ArgumentNullException(nameof(provider));
        }

        public IEnumerator<T> GetEnumerator()
        {
            return Provider.Execute<IEnumerable<T>>(Expression).GetEnumerator();
        }

        IEnumerator IEnumerable.GetEnumerator()
        {
            return Provider.Execute<IEnumerable>(Expression).GetEnumerator();
        }

        public override string ToString()
        {
            return _provider.GetQueryText(Expression);
        }
    }

    public abstract class BaseProvider : IQueryProvider
    {
        public abstract string GetQueryText(Expression expression);
        public abstract object Execute(Expression expression);

        IQueryable<T> IQueryProvider.CreateQuery<T>(Expression expression)
        {
            return new CustomQuery<T>(this,expression);
        }

        IQueryable IQueryProvider.CreateQuery(Expression expression)
        {
            return (IQueryable)Activator.CreateInstance(typeof(CustomQuery<>).MakeGenericType(expression.Type),
                this, expression);
        }

        T IQueryProvider.Execute<T>(Expression expression)
        {
            return (T)Execute(expression);
        }

        object IQueryProvider.Execute(Expression expression) 
        {
            return Execute(expression);
        }
    }

    internal class CustomTranslator : ExpressionVisitor
    {
        private StringBuilder _stringBuilder;

        internal string Translate(Expression expression)
        {
            _stringBuilder = new StringBuilder();
            Visit(expression);
            return _stringBuilder.ToString();
        }

        private static Expression StripQuotes(Expression expression)
        {
            while (expression.NodeType == ExpressionType.Quote) expression = ((UnaryExpression)expression).Operand;
            return expression;
        }

        protected override Expression VisitMethodCall(MethodCallExpression methodCallExpression)
        {
            if (methodCallExpression.Method.DeclaringType != typeof(Queryable) || methodCallExpression.Method.Name != "Where")
            {
                throw new NotSupportedException($"不支持‘{methodCallExpression.Method.Name}’方法");
            }

            _stringBuilder.Append("SELECT * FROM (");
            Visit(methodCallExpression.Arguments[0]);
            _stringBuilder.Append(") AS t WHERE ");
            var lambda = (LambdaExpression)StripQuotes(methodCallExpression.Arguments[1]);
            Visit(lambda.Body);
            return methodCallExpression;
        }

        protected override Expression VisitUnary(UnaryExpression expression)
        {
            switch (expression.NodeType)
            {
                case ExpressionType.Not:
                    _stringBuilder.Append(" NOT ");
                    Visit(expression.Operand);
                    break;
                default:
                    throw new NotSupportedException($"不支持‘{expression.NodeType}’操作符");
            }
            return expression;
        }

        protected override Expression VisitBinary(BinaryExpression binaryExpression)
        {
            _stringBuilder.Append("(");
            Visit(binaryExpression.Left);
            switch (binaryExpression.NodeType)
            {
                case ExpressionType.And:
                    _stringBuilder.Append(" AND ");
                    break;
                case ExpressionType.Or:
                    _stringBuilder.Append(" OR ");
                    break;
                case ExpressionType.Equal:
                    _stringBuilder.Append(" = ");
                    break;
                case ExpressionType.NotEqual:
                    _stringBuilder.Append(" <> ");
                    break;
                case ExpressionType.LessThan:
                    _stringBuilder.Append(" < ");
                    break;
                case ExpressionType.LessThanOrEqual:
                    _stringBuilder.Append(" <= ");
                    break;
                case ExpressionType.GreaterThan:
                    _stringBuilder.Append(" > ");
                    break;
                case ExpressionType.GreaterThanOrEqual:
                    _stringBuilder.Append(" >= ");
                    break;
                default:
                    throw new NotSupportedException($"不支持‘{binaryExpression.NodeType}’运算符");
            }

            Visit(binaryExpression.Right);
            _stringBuilder.Append(")");
            return binaryExpression;
        }

        protected override Expression VisitConstant(ConstantExpression constantExpression)
        {
            if (!(constantExpression.Value is IQueryable queryable))
            {
                if (constantExpression.Value == null)
                {
                    _stringBuilder.Append("NULL");
                }
                else 
                {
                    switch (Type.GetTypeCode(constantExpression.Value.GetType()))
                    {
                        case TypeCode.Boolean:
                            _stringBuilder.Append((bool)constantExpression.Value ? 1 : 0);
                            break;
                        case TypeCode.String:
                            _stringBuilder.Append("'");
                            _stringBuilder.Append(constantExpression.Value);
                            _stringBuilder.Append("'");
                            break;
                        case TypeCode.Object:
                            throw new NotSupportedException($"不支持‘{constantExpression.Value}’类型");
                        default:
                            _stringBuilder.Append(constantExpression.Value);
                            break;
                    }
                }
            }
            else
            {
                _stringBuilder.Append("SELECT * FRM ");
                _stringBuilder.Append(queryable.ElementType.Name);
            }

            return constantExpression;
        }

        protected override Expression VisitMember(MemberExpression memberExpression)
        {
            if (memberExpression.Expression == null || memberExpression.Expression.NodeType != ExpressionType.Parameter)
            {
                throw new NotSupportedException($"不支持‘{memberExpression.Member.Name}’成员");
            }
            _stringBuilder.Append(memberExpression.Member.Name);
            return memberExpression;
        }
    }

    public class CustomProvider : BaseProvider
    {
        readonly SqlConnection _connection;

        public CustomProvider(SqlConnection connection)
        {
            _connection = connection;
        }

        private string Translate(Expression expression)
        {
            return new CustomTranslator().Translate(expression);
        }

        public override string GetQueryText(Expression expression)
        {
            return Translate(expression);
        }

        public override object Execute(Expression expression)
        {
            var command = new SqlCommand { Connection = _connection, CommandText = Translate(expression) };
            Console.WriteLine(command.CommandText);
            // DbDataReader reader = command.ExcuteReader();
            // ...
            return null;
        }
    }
}