修复达梦EFCore驱动布尔类型兼容问题
<p>dm库相比其他库本身缺少一些语法差异，也可以说是缺陷。<br>比如：<br>
0和1无法直接在sql中当作真假值用，where 0这种写法不支持，报错：查询使用值表达式作为过滤条件；<br>
t.field is null 也无法直接作为select项；<br>
不支持OUTER APPLY等SQL语法；<br>
以及数据库函数中的又只能用0和1作为布尔参数值。</p>
<p>但是dm.efcore生成的语句就是这样的，现在来给修复办法。</p>
<h4 id="1查询使用值表达式作为过滤条件">1.查询使用值表达式作为过滤条件</h4>
<p>这个问题其实不好修，正常的修复办法需要重写很多代码，主要是EFCore里面的这个代码SqlNullabilityProcessor，主要就是为了优化sql语句的</p>
<ul>
<li>a == true -> a</li>
</ul>
<p>a == true -> a 变成where a这样，就是这个代码导致的，但是很遗憾达梦不支持。</p>
<pre><code class="language-csharp">public class SqlNullabilityProcessor
{
.....
private SqlExpression OptimizeComparison(
SqlBinaryExpression sqlBinaryExpression,
SqlExpression left,
SqlExpression right,
bool leftNullable,
bool rightNullable,
out bool nullable)
{
var leftNullValue = leftNullable && left is SqlConstantExpression or SqlParameterExpression;
var rightNullValue = rightNullable && right is SqlConstantExpression or SqlParameterExpression;
// a == null -> a IS NULL
// a != null -> a IS NOT NULL
if (rightNullValue)
{
var result = sqlBinaryExpression.OperatorType == ExpressionType.Equal
? ProcessNullNotNull(_sqlExpressionFactory.IsNull(left), leftNullable)
: ProcessNullNotNull(_sqlExpressionFactory.IsNotNull(left), leftNullable);
nullable = false;
return result;
}
// null == a -> a IS NULL
// null != a -> a IS NOT NULL
if (leftNullValue)
{
var result = sqlBinaryExpression.OperatorType == ExpressionType.Equal
? ProcessNullNotNull(_sqlExpressionFactory.IsNull(right), rightNullable)
: ProcessNullNotNull(_sqlExpressionFactory.IsNotNull(right), rightNullable);
nullable = false;
return result;
}
if (TryGetBool(right, out var rightBoolValue)
&& !leftNullable
&& left.TypeMapping!.Converter == null)
{
nullable = leftNullable;
// only correct in 2-value logic
// a == true -> a
// a == false -> !a
// a != true -> !a
// a != false -> a
return sqlBinaryExpression.OperatorType == ExpressionType.Equal ^ rightBoolValue
? OptimizeNonNullableNotExpression(_sqlExpressionFactory.Not(left))
: left;
}
if (TryGetBool(left, out var leftBoolValue)
&& !rightNullable
&& right.TypeMapping!.Converter == null)
{
nullable = rightNullable;
// only correct in 2-value logic
// true == a -> a
// false == a -> !a
// true != a -> !a
// false != a -> a
return sqlBinaryExpression.OperatorType == ExpressionType.Equal ^ leftBoolValue
? OptimizeNonNullableNotExpression(_sqlExpressionFactory.Not(right))
: right;
}
// only correct in 2-value logic
// a == a -> true
// a != a -> false
if (!leftNullable
&& left.Equals(right))
{
nullable = false;
return _sqlExpressionFactory.Constant(
sqlBinaryExpression.OperatorType == ExpressionType.Equal,
sqlBinaryExpression.TypeMapping);
}
if (!leftNullable
&& !rightNullable
&& sqlBinaryExpression.OperatorType is ExpressionType.Equal or ExpressionType.NotEqual)
{
var leftUnary = left as SqlUnaryExpression;
var rightUnary = right as SqlUnaryExpression;
var leftNegated = IsLogicalNot(leftUnary);
var rightNegated = IsLogicalNot(rightUnary);
if (leftNegated)
{
left = leftUnary!.Operand;
}
if (rightNegated)
{
right = rightUnary!.Operand;
}
// a == b <=> !a == !b -> a == b
// !a == b <=> a == !b -> a != b
// a != b <=> !a != !b -> a != b
// !a != b <=> a != !b -> a == b
nullable = false;
return sqlBinaryExpression.OperatorType == ExpressionType.Equal ^ leftNegated == rightNegated
? _sqlExpressionFactory.NotEqual(left, right)
: _sqlExpressionFactory.Equal(left, right);
}
nullable = false;
return sqlBinaryExpression.Update(left, right);
}
}
</code></pre>
<p>本来像的修复办法是重写这个类，但是这个方法不是虚方法还是私有的，无法重写，就需要从调用这个方法的所有方法全部重写，这就太麻烦了，这种重写还会有其他问题，比如下次升级efcore时，新版本的这个方法有改动，但我重写了就需要再重新重写一次。<br>
在想不到办法的时候，无意间注意到这个方法转换0 和1 的地方就时TryGetBool这个几个if判断中（本来就时想改这里），都有下面这个判断<br>
XXX.TypeMapping!.Converter == null<br>
那么我就想如果boolTypeMapping.Converter不是null的不就可以绕过这个转换了吗。<br>
所以就有了下面这个解决办法，直接上代码，不解释了，都能看懂：</p>
<pre><code class="language-csharp">public class MyDmBoolTypeMapping : BoolTypeMapping
{
public MyDmBoolTypeMapping(string storeType, DbType? dbType)
: base(new RelationalTypeMappingParameters(
new(typeof(bool), new DmBooleanConverter(), null), storeType))
{
}
protected MyDmBoolTypeMapping(RelationalTypeMappingParameters parameters)
: base(parameters)
{
}
protected override string GenerateNonNullSqlLiteral(object value)
{
if (!(bool)value)
{
return "0";
}
return "1";
}
protected override RelationalTypeMapping Clone(RelationalTypeMappingParameters parameters)
{
return (RelationalTypeMapping)(object)new MyDmBoolTypeMapping(parameters);
}
private sealed class DmBooleanConverter : ValueConverter<bool, bool>
{
public DmBooleanConverter()
: base(b => b, b => b)
{
}
}
}
public class MyDmTypeMappingSource：DmTypeMappingSource
{
public MyDmTypeMappingSource( TypeMappingSourceDependencies dependencies, RelationalTypeMappingSourceDependencies relationalDependencies)
: base(dependencies, relationalDependencies)
{
var f = typeof(DmTypeMappingSource).GetField("_bool",BindingFlags.Instance| BindingFlags.NonPublic);
f.SetValue(this,new MyDmBoolTypeMapping("BIT", DbType.Boolean));
}
}
</code></pre>
<p>上面代码就时给Bool类型的加上了Converter，它不再是null了，同样像上一个dmefcore驱动文章说的一样，用把这个MyDmTypeMappingSource替换服务类</p>
<pre><code class="language-csharp">protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.ReplaceService<IRelationalTypeMappingSource, MyDmTypeMappingSource>();
}
</code></pre>
<p>到这里就解决了a == true -> a这一种“查询使用值表达式作为过滤条件”报错问题，但是不彻底，还会有其他比如 a.Contains(t.Id) -> false这样的情况，当然还有其他很多情况。<br>
无法直接改代码修复，我又继续思考，然后我发现达梦不支持0和1作为条件，但是支持false和true这样的作为条件，那么我就把这个MyDmBoolTypeMapping中的GenerateNonNullSqlLiteral方法改成返回FALSE和TRUE不就可以了吗，所以就有了下面的代码，验证发现完美解决问题</p>
<pre><code class="language-csharp">public class MyDmBoolTypeMapping : BoolTypeMapping
{
public MyDmBoolTypeMapping(string storeType, DbType? dbType)
: base(new RelationalTypeMappingParameters(
new(typeof(bool), new DmBooleanConverter(), null), storeType))
{
}
protected MyDmBoolTypeMapping(RelationalTypeMappingParameters parameters)
: base(parameters)
{
}
protected override string GenerateNonNullSqlLiteral(object value)
{
if (!(bool)value)
{
return "FALSE";
}
return "TRUE";
}
protected override RelationalTypeMapping Clone(RelationalTypeMappingParameters parameters)
{
return (RelationalTypeMapping)(object)new MyDmBoolTypeMapping(parameters);
}
private sealed class DmBooleanConverter : ValueConverter<bool, bool>
{
public DmBooleanConverter()
: base(b => b, b => b)
{
}
}
}
</code></pre>
<h4 id="2数据库函数中的又只能用0和1作为布尔参数值">2.数据库函数中的又只能用0和1作为布尔参数值</h4>
<p>先说函数这个问题，因为我们改成了FALSE和TRUE导致的，那么我再继续重写：</p>
<pre><code class="language-csharp">public class MyDmSqlNullabilityProcessor : SqlNullabilityProcessor
{
protected override SqlExpression VisitSqlFunction(SqlFunctionExpression sqlFunctionExpression, bool allowOptimizedExpansion, out bool nullable)
{
var exp = base.VisitSqlFunction(sqlFunctionExpression, allowOptimizedExpansion, out nullable);
if (exp is SqlFunctionExpression functionExpression)
{
var arguments = functionExpression.Arguments.Select(e =>
{
if (e is SqlConstantExpression constantExpression && constantExpression.Value is bool value)
{
return _sqlExpressionFactory.Constant(value ? 1 : 0, new IntTypeMapping("INT", DbType.Int32));
}
return e;
}).ToArray();
return functionExpression.Update(functionExpression.Instance, arguments);
}
return exp;
}
}
</code></pre>
<h4 id="3其他问题">3.其他问题</h4>
<p>主要修复代码就是下面这个，关键代码是这行UpdateShaperExpression(Visit(shapedQueryExpression.ShaperExpression))<br>
同样需要其他类new它并且跟其他服务类一样替换注入，就不多解释了。</p>
<pre><code class="language-csharp">public class MySearchConditionConvertingExpressionVisitor : SearchConditionConvertingExpressionVisitor
{
protected override Expression VisitExtension(Expression extensionExpression)
=> extensionExpression switch
{
ShapedQueryExpression shapedQueryExpression
=> shapedQueryExpression
.UpdateQueryExpression(Visit(shapedQueryExpression.QueryExpression))
.UpdateShaperExpression(Visit(shapedQueryExpression.ShaperExpression)),
_ => base.VisitExtension(extensionExpression),
};
}
</code></pre>
<p>完</p>
</div>
<div id="MySignature" role="contentinfo">
<div id="AllanboltSignature">
<p id="PSignature" style="border-top-color: #e0e0e0; border-top-width: 1px; border-top-style: dashed; border-right-color: #e0e0e0; border-right-width: 1px; border-right-style: dashed; border-bottom-color: #e0e0e0; border-bottom-width: 1px; border-bottom-style: dashed; border-left-color: #e0e0e0; border-left-width: 1px; border-left-style: dashed; padding-top: 10px; padding-right: 10px; padding-bottom: 10px; padding-left: 80px; background-image: url(https://images.cnblogs.com/cnblogs_com/pains/109838/r_copyright.png); background-attachment: initial; background-origin: initial; background-clip: initial; font-family: 微软雅黑; font-size: 11px; background-color: #e5f1f4; background-position: 1% 50%; background-repeat: no-repeat no-repeat; ">
作者：Rick Carter
<br />
出处：http://pains.cnblogs.com/
<br />
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
</p>
</div><br><br>
来源：https://www.cnblogs.com/pains/p/19183953
頁:
[1]