将Microsoft.SqlServer.Types与Dapper一起使用时的RuntimeBinderInternalCompilerException

使用其目标平台设置为以下之一的Sql Server Data Tools项目:

SQL Server 2008

SQL Server 2012

SQL Server 2014

并部署到(localdb)\ Projects或(localdb)\ ProjectsV12

调用返回Geometry,Geography或HierachyId类型的存储过程,例如:

CREATE PROCEDURE [dbo].[SelectSqlGeometry]
    @x Geometry
AS
    SELECT @x as y
RETURN 0

以下调用代码:

var result = Connection.Query("dbo.SelectSqlGeometry", new { x = geometry }, commandType: CommandType.StoredProcedure).First();
bool isSame = ((bool)geometry.STEquals(result.y));

在STEquals系列中导致以下异常.

Microsoft.CSharp.RuntimeBinder.RuntimeBinderInternalCompilerException未由用户代码处理HResult = -2146233088消息=绑定动态操作时发生意外异常
Source = Microsoft.CSharp StackTrace:at Microsoft.CSharp.RuntimeBinder.RuntimeBinder.Bind(DynamicMetaObjectBinder payload,IEnumerable 1 parameters, DynamicMetaObject[] args, DynamicMetaObject& deferredBinding) at Microsoft.CSharp.RuntimeBinder.BinderHelper.Bind(DynamicMetaObjectBinder action, RuntimeBinder binder, IEnumerable1 args,IEnumerable 1 arginfos, DynamicMetaObject onBindingError) at Microsoft.CSharp.RuntimeBinder.CSharpConvertBinder.FallbackConvert(DynamicMetaObject target, DynamicMetaObject errorSuggestion) at System.Dynamic.DynamicMetaObject.BindConvert(ConvertBinder binder) at System.Dynamic.ConvertBinder.Bind(DynamicMetaObject target, DynamicMetaObject[] args) at System.Dynamic.DynamicMetaObjectBinder.Bind(Object[] args, ReadOnlyCollection1参数,LabelTarget returnLabel)在System.Runtime.CompilerServices.CallSiteBinder.BindCore [T](CallSite`1站点,Object [] args)在System.Dynamic.UpdateDelegates.UpdateAndExecute1 [T0,TRet](CallSite站点, T0 arg0)at DATailor.Examples.Dapper.SqlClient.Test.AllTypesDAOTest.TestAllTypesDynamic()

Richard Coll.. 7

虽然根本原因不是Dapper,但是有一个潜在的异常被吞噬.

使用ADO.Net代码,如:

var geometry = Util.CreateSqlGeometry();
SqlDataReader reader=null;
SqlCommand cmd=null;
try
{
    cmd = new SqlCommand("dbo.SelectSqlGeometry", (SqlConnection)Connection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter("@x", geometry) { UdtTypeName = "Geometry" });
    cmd.ExecuteNonQuery();
    reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        var y = (SqlGeometry)reader.GetValue(0);
        var same = geometry.STEquals(y);
    }
    reader.Dispose();
    reader = null;
}
finally
{
    if (reader != null)
    {
        if (!reader.IsClosed) try { cmd.Cancel(); }
            catch {}
        reader.Dispose();
    }
    if (cmd != null) cmd.Dispose();
    Connection.Close();
}

抛出以下异常 reader.GetValue

System.InvalidCastException未处理HResult = -2147467262
消息= [A] Microsoft.SqlServer.Types.SqlGeometry无法强制转换为[B] Microsoft.SqlServer.Types.SqlGeometry.类型A源自'Microsoft.SqlServer.Types,Version = 10.0.0.0,Culture = neutral,PublicKeyToken = 89845dcd8080cc91',位于'默认'位置'C:\ Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\10.0 .0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll".类型B源自'Microsoft.SqlServer.Types,Version = 11.0.0.0,Culture = neutral,PublicKeyToken = 89845dcd8080cc91',位于'默认'位置'C:\ Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Types\11.0 .0.0__89845dcd8080cc91\Microsoft.SqlServer.Types.dll".Source = DynamicGeometryIssue StackTrace:at DynamicGeometryIssue.TestDao.TestGeometry()in c:\ Users\rich\Documents\Visual Studio 2013\Projects\DynamicGeometryIssue\DynamicGeometryIssue\TestDao.cs:第27行,位于DynamicGeometryIssue.Program.Main(String [] args )在System.AppDomain.ExecuteAssembly(String)中的System.AppDomain._nExecuteAssembly(RuntimeAssembly程序集,String [] args)中的c:\ Users\rich\Documents\Visual Studio 2013\Projects\DynamicGeometryIssue\DynamicGeometryIssue\Program.cs:第15行在System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext,ContextCallback callback,Object)中的System.Threading.ThreadHelper.ThreadStart_Context(Object state)上的Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()中的assemblyFile,Evidence assemblySecurity,String [] args) System.Threading.Execu上的System.Threading.ExecutionContext.Run(ExecutionContext executionContext,ContextCallback callback,Object state,Boolean preserveSyncCtx)中的state,Boolean preserveSyncCtx)System.Threading.ThreadHelper.ThreadStart()InnerException中的tionContext.Run(ExecutionContext executionContext,ContextCallback回调,Object状态):

基础异常的原因是SQL Server 2012中已知的重大更改.请参阅 以下MSDN文档的SQL CLR数据类型部分

http://msdn.microsoft.com/en-us/library/ms143179(v=sql.110).aspx

对我有用的解决方案是在app.config或web.config中创建以下bindingRedirect.

或者,使用.NET 4.5,您可以更改连接字符串以包含"Type System Version"属性的值"SQL Server 2012",以强制SqlClient加载程序集的版本11.0.

另一种解决方法是代码:

var geo = SqlGeography.Deserialize(rdr.GetSqlBytes(0));

但是,我不相信这是Dapper的选择.