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屋!