【Linq】表达式中And、Or和AndAlso、OrElse的区别

前言

最近在EFCore中，做一个动态拼接日期的操作，在查看EFCore生成的sql语句时，发现写的判断都被转换为了bit位，然后才去比较结果，感觉很别扭，而且担心这种bit位判断会影响速度，随后开始百度Linq的表达式区别

微软文档

下面几个链接是微软对And、Or和AndAlso、OrElse的解释，感觉看着有些干硬，没法接地气的理解意思

https://docs.microsoft.com/zh-cn/dotnet/api/system.linq.expressions.expression.and?view=net-6.0

https://docs.microsoft.com/zh-cn/dotnet/api/system.linq.expressions.expression.andalso?view=net-6.0

https://docs.microsoft.com/zh-cn/dotnet/api/system.linq.expressions.expression.or?view=net-6.0

https://docs.microsoft.com/zh-cn/dotnet/api/system.linq.expressions.expression.orelse?view=net-6.0

总结

简单描述

AndAlso，OrElse相对于And，Or最大的特性是会自动实现“最短路径”。
所谓“最短路径”就是指：当第一个被比较的运算式的结果已经能决定运算的最终结果时，就不会再去比较其他运算式，因此可以避免掉额外且不需要的比较运算式。

Linq生成bit位的解释

原因在于：微软文档描述，创建的是一个按位运算的AND

所以Linq中写的每一个lambda表达式，最后转换sql时都有个case when 转换bit的操作

而使用AndAlso和OrElse这2个方法替代And和Or方法，会因为实现"最短路径"，把一些不需要计算的比较运算式剔除掉，最后才会生成我们熟悉的sql语句，没有那些多余的case when 转换bit操作

例子

And和AndAlso

            int a = 1;
            int b = 1;
            (1)对于And，会对所有的表达式都进行判断。1==2、a==1、b==2，这3个表达式都会计算一遍，最后再取逻辑真
            if(1 == 2 And a == 1 And b == 2)
                return true;
            else
                return false;
            (2)对于AndAlso，会从第一个表达式开始计算。也就是先计算1==2，这里已经是逻辑假，所以后面的a==1、b==2就不会再计算了
            if (1 == 2 AndAlso a == 1 AndAlso b == 2)
                return true;
            else
                return false;

Or和OrElse

            int a = 1;
            int b = 1;
            (1)对于Or，会对所有的表达式都进行判断。1==1、a==1、b==2，这3个表达式都会计算一遍，最后再取逻辑假
            if(1 == 1 Or a == 1 Or b == 2)
                return true;
            else
                return false;
            (2)对于OrElse，会从第一个表达式开始计算。也就是先计算1==1，这里已经是逻辑真，所以后面的a==1、b==2就不会再计算了
            if (1 == 1 OrElse a == 1 OrElse b == 2)
                return true;
            else
                return false;

对比EFCore生成的sql语句

And

(1)代码

                var exp = PredicateBuilder.True<Users>();
                exp = exp.And(x => x.Age > 0);
                exp = exp.And(x => x.Age < 20);
                exp = exp.And(x => true);

                var data = db.Users
                    .Where(exp)
                    .ToList();

 

(2)生成的sql

SELECT [u].[Id], [u].[Age], [u].[CreateTime], [u].[Gender], [u].[Name]
FROM [Users] AS [u]
WHERE (((CAST(1 AS bit) & CASE
    WHEN [u].[Age] > 0 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END) & CASE
    WHEN [u].[Age] < 20 THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END) & CAST(1 AS bit)) = CAST(1 AS bit)

 

AndAlso

(1)代码

                var exp = PredicateBuilder.True<Users>();
                exp = exp.AndAlso(x => x.Age > 0);
                exp = exp.AndAlso(x => x.Age < 20);
                exp = exp.AndAlso(x => true);

                var data = db.Users
                    .Where(exp)
                    .ToList();

 

(2)生成的sql

SELECT [u].[Id], [u].[Age], [u].[CreateTime], [u].[Gender], [u].[Name]
FROM [Users] AS [u]
WHERE ([u].[Age] > 0) AND ([u].[Age] < 20)

 

Or

(1)代码

                var exp = PredicateBuilder.False<Users>();
                Dictionary<DateTime, DateTime> lstDate = new Dictionary<DateTime, DateTime>
                {
                    { new DateTime(2022, 6, 12), new DateTime(2022, 6, 13) },
                    { new DateTime(2022, 6, 20), new DateTime(2022, 6, 21) }
                };

                foreach (var item in lstDate)
                {
                    DateTime beginDate_Many = item.Key;
                    DateTime endDate_Many = item.Value.AddDays(1);

                    exp = exp.Or(x => x.CreateTime >= beginDate_Many && x.CreateTime < endDate_Many);
                }

                var data = db.Users
                    .Where(exp)
                    .ToList();

 

(2)生成的sql

exec sp_executesql N'SELECT [u].[Id], [u].[Age], [u].[CreateTime], [u].[Gender], [u].[Name]
FROM [Users] AS [u]
WHERE ((CAST(0 AS bit) | CASE
    WHEN ([u].[CreateTime] >= @__beginDate_Many_0) AND ([u].[CreateTime] < @__endDate_Many_1) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END) | CASE
    WHEN ([u].[CreateTime] >= @__beginDate_Many_2) AND ([u].[CreateTime] < @__endDate_Many_3) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END) = CAST(1 AS bit)',N'@__beginDate_Many_0 datetime2(7),@__endDate_Many_1 datetime2(7),@__beginDate_Many_2 datetime2(7),@__endDate_Many_3 datetime2(7)',@__beginDate_Many_0='2022-06-12 00:00:00',@__endDate_Many_1='2022-06-14 00:00:00',@__beginDate_Many_2='2022-06-20 00:00:00',@__endDate_Many_3='2022-06-22 00:00:00'

 

OrElse

(1)代码

                var exp = PredicateBuilder.False<Users>();
                Dictionary<DateTime, DateTime> lstDate = new Dictionary<DateTime, DateTime>
                {
                    { new DateTime(2022, 6, 12), new DateTime(2022, 6, 13) },
                    { new DateTime(2022, 6, 20), new DateTime(2022, 6, 21) }
                };

                foreach (var item in lstDate)
                {
                    DateTime beginDate_Many = item.Key;
                    DateTime endDate_Many = item.Value.AddDays(1);

                    exp = exp.OrElse(x => x.CreateTime >= beginDate_Many && x.CreateTime < endDate_Many);
                }

                var data = db.Users
                    .Where(exp)
                    .ToList();

 

(2)生成的sql

exec sp_executesql N'SELECT [u].[Id], [u].[Age], [u].[CreateTime], [u].[Gender], [u].[Name]
FROM [Users] AS [u]
WHERE (([u].[CreateTime] >= @__beginDate_Many_0) AND ([u].[CreateTime] < @__endDate_Many_1)) OR (([u].[CreateTime] >= @__beginDate_Many_2) AND ([u].[CreateTime] < @__endDate_Many_3))',N'@__beginDate_Many_0 datetime2(7),@__endDate_Many_1 datetime2(7),@__beginDate_Many_2 datetime2(7),@__endDate_Many_3 datetime2(7)',@__beginDate_Many_0='2022-06-12 00:00:00',@__endDate_Many_1='2022-06-14 00:00:00',@__beginDate_Many_2='2022-06-20 00:00:00',@__endDate_Many_3='2022-06-22 00:00:00'

 

 测试使用的类

Linq扩展类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace Test.EFCoreLinq
{
    public class ParameterRebinder : ExpressionVisitor
    {
        private readonly Dictionary<ParameterExpression, ParameterExpression> map;

        public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
        {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {
            return new ParameterRebinder(map).Visit(exp);
        }

        protected override Expression VisitParameter(ParameterExpression p)
        {
            if (map.TryGetValue(p, out ParameterExpression replacement))
            {
                p = replacement;
            }
            return base.VisitParameter(p);
        }
    }

    public static class PredicateBuilder
    {

        public static Expression<Func<T, bool>> True<T>() { return f => true; }
        public static Expression<Func<T, bool>> False<T>() { return f => false; }
        public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
        {
            // build parameter map (from parameters of second to parameters of first)  
            var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);

            // replace parameters in the second lambda expression with parameters from the first  
            var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

            // apply composition of lambda expression bodies to parameters from the first expression   
            return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
        }

        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.And);
        }

        public static Expression<Func<T, bool>> AndAlso<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.AndAlso);
        }

        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.Or);
        }

        public static Expression<Func<T, bool>> OrElse<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.OrElse);
        }
    }
}

Users类

using System;

namespace Test.EFCoreLinq.Entity
{
    public class Users
    {
        /// <summary>
        /// 主键
        /// </summary>
        public string Id { get; set; }
        /// <summary>
        /// 姓名
        /// </summary>
        public string Name { get; set; }
        /// <summary>
        /// 年龄
        /// </summary>
        public int Age { get; set; }
        /// <summary>
        /// 性别
        /// </summary>
        public int Gender { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime CreateTime { get; set; }
    }
}