语言集成查询(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;
}
}
}