Lambda字符串为VARCHAR [英] Lambda string as VARCHAR
问题描述
我的一个Join键选择器如下所示:
One of my Join key-selectors looks like this:
x => x.A + "-" + x.B
NHibernate使"-"成为附加参数.此参数获取SQL类型nvarchar，因此整个语句在SQL Server上从varchar转换为nvarchar.
NHibernate makes "-" an extra parameter. This parameter gets the SQL type nvarchar and so the whole statement gets converted on the SQL Server from varchar to nvarchar.
与此有关的问题是，如果查询的列的类型为varchar而不是nvarchar，则SQL Server存在巨大的问题.这是因为该列是参数之外的另一种类型，因此索引不能使用.
The problem with this is, that SQL Server has a huge problem if the queried column is of type varchar instead of nvarchar. This is because the column is of another type than the parameter and so the index can't be used.
我无法更改列的类型，因此我需要定义某种方式，NHibernate 在转换lambda时应使用varchar作为字符串文字.
I cannot change the type of the column so I need to define somehow that NHibernate should use varchar for string literals when converting lambdas.
有什么办法吗?
在Oskar Berggren的帮助下，我设置了此类:
With help from Oskar Berggren I setup this classes:
public static class VarcharFix
{
/// This method returns its argument and is a no-op in C#.
/// It's presence in a Linq expression sends a message to the NHibernate Linq Provider.
public static string AsVarchar(string s)
{
return s;
}
}
public class MyHqlIdent : HqlExpression
{
internal MyHqlIdent(IASTFactory factory, string ident)
: base(HqlSqlWalker.IDENT, ident, factory)
{
}
internal MyHqlIdent(IASTFactory factory, System.Type type)
: base(HqlSqlWalker.IDENT, "", factory)
{
if (IsNullableType(type))
{
type = ExtractUnderlyingTypeFromNullable(type);
}
switch (System.Type.GetTypeCode(type))
{
case TypeCode.Boolean:
SetText("bool");
break;
case TypeCode.Int16:
SetText("short");
break;
case TypeCode.Int32:
SetText("integer");
break;
case TypeCode.Int64:
SetText("long");
break;
case TypeCode.Decimal:
SetText("decimal");
break;
case TypeCode.Single:
SetText("single");
break;
case TypeCode.DateTime:
SetText("datetime");
break;
case TypeCode.String:
SetText("string");
break;
case TypeCode.Double:
SetText("double");
break;
default:
if (type == typeof(Guid))
{
SetText("guid");
break;
}
if (type == typeof(DateTimeOffset))
{
SetText("datetimeoffset");
break;
}
throw new NotSupportedException(string.Format("Don't currently support idents of type {0}", type.Name));
}
}
private static System.Type ExtractUnderlyingTypeFromNullable(System.Type type)
{
return type.GetGenericArguments()[0];
}
// TODO - code duplicated in LinqExtensionMethods
private static bool IsNullableType(System.Type type)
{
return (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>));
}
}
public class MyHqlCast : HqlExpression
{
public MyHqlCast(IASTFactory factory, IEnumerable<HqlTreeNode> children)
: base(HqlSqlWalker.METHOD_CALL, "method", factory, children)
{
}
public static MyHqlCast Create(IASTFactory factory, HqlExpression expression, string targetType)
{
return new MyHqlCast(factory,
new HqlTreeNode[]
{
new MyHqlIdent(factory, "cast"),
new HqlExpressionList(factory, expression,
new MyHqlIdent(factory, targetType))
});
}
}
public class MyBaseHqlGeneratorForMethod : BaseHqlGeneratorForMethod
{
public MyBaseHqlGeneratorForMethod()
: base()
{
SupportedMethods = new MethodInfo[] { typeof(VarcharFix).GetMethod("AsVarchar") };
}
public override HqlTreeNode BuildHql(MethodInfo method, System.Linq.Expressions.Expression targetObject, System.Collections.ObjectModel.ReadOnlyCollection<System.Linq.Expressions.Expression> arguments, HqlTreeBuilder treeBuilder, global::NHibernate.Linq.Visitors.IHqlExpressionVisitor visitor)
{
return MyHqlCast.Create(new ASTFactory(new ASTTreeAdaptor()),
visitor.Visit(targetObject).AsExpression(),
"varchar");
}
}
public class ExtendedLinqtoHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
{
public ExtendedLinqtoHqlGeneratorsRegistry()
{
this.Merge(new MyBaseHqlGeneratorForMethod());
}
}
目前它仍然无法正常工作，但我看到了光;)
For now it's still not working but I see light ;)
var query = aQueryable
.Join(bQueryable,
x => x.AB, x => x.A + VarcharFix.AsVarchar("-") + x.B,
(head, middle) => new ...)
更新3:
随着"-".AsVarchar()被优化为 "-"，我们需要一个虚拟参数，该参数无法像"-".AsVarchar(x.A)那样进行优化-这样，Linq扩展名就可以启动了！
UPDATE 3:
As "-".AsVarchar() gets optimized to "-" we need a dummy parameter, which cannot be optimized like "-".AsVarchar(x.A) - that way the Linq-extension kicks in!
var query = aQueryable
.Join(bQueryable,
x => x.AB, x => x.A + "-".AsVarchar(x.A) + x.B,
(head, middle) => new ...)
推荐答案
执行此操作的方法可能有多种，但这是一种:
There may be multiple ways to do this but here is one:
发明自己的方法，例如:
Invent your own method such as:
/// This method returns its argument and is a no-op in C#.
/// It's presence in a Linq expression sends a message to the NHibernate Linq Provider.
public static string AsVarchar(string s)
{
return s;
}
还要创建一个代表HQL表达片段的类:
Also create a class to represent the HQL expression fragment:
public class MyHqlCast : HqlExpression
{
private MyHqlCast(IASTFactory factory, IEnumerable<HqlTreeNode> children)
: base(HqlSqlWalker.METHOD_CALL, "method", factory, children)
{
}
public static MyHqlCast Create(IASTFactory factory, HqlExpression expression,
string targetType)
{
return new MyHqlCast(factory,
new [] {
new HqlIdent(factory, "cast")),
new HqlExpressionList(factory, expression,
new HqlIdent(factory, targetType)),
});
}
}
然后从BaseHqlGeneratorForMethod派生一个类.在其构造函数中，将SupportedMethods属性设置为AsVarchar()方法.重写BuildHql()方法.它应该输出与cast(@param as varchar)等效的HQL cast结构.通常，您可以在treeBuilder参数上使用Cast()方法，但是不幸的是，它仅接受System.Type，对于这种情况还不够好.而是创建并返回MyHqlCast的实例:
Then derive a class from BaseHqlGeneratorForMethod. In its constructor, set the SupportedMethods property to the AsVarchar() method. Override the BuildHql() method. It should output the HQL cast constructs equivalent to cast(@param as varchar). Normally you would use the Cast() method on the treeBuilder parameter, but unfortunately this accepts just a System.Type, which isn't good enough for this case. Instead create and return an instance of your MyHqlCast:
return MyHqlCast.Create(new ASTFactory(new ASTTreeAdaptor()),
visitor.Visit(arguments[0]).AsExpression(),
"varchar");
您需要通过从DefaultLinqToHqlGeneratorsRegistry派生注册BaseHqlGeneratorForMethod的实现.调用this.Merge(new MyGenerator());在构造函数中.然后通过
Your implementation of BaseHqlGeneratorForMethod then needs to be registered by deriving from DefaultLinqToHqlGeneratorsRegistry. Call this.Merge(new MyGenerator()); in the constructor. Then register your registry type by
nhibernateConfiguration.LinqToHqlGeneratorsRegistry<MyRegistry>();
这篇关于Lambda字符串为VARCHAR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
