从 varchar 到 uniqueidentifier 的 SQL 转换在视图中失败

2022-01-03 00:00:00 sql sql-server sql-server-2012 entity-framework

我被困在以下场景中.我有一个数据库，其中包含一个包含客户数据的表和一个表，我在其中放置了用于监控 B2B 站点上发生的事情的记录.

I'm stuck on the following scenario. I have a database with a table with customer data and a table where I put records for monitoring what is happening on our B2B site.

客户表如下:

  • ID，整数，非空
  • GUID，uniqueidentfier，not null，主键
  • 其他东西...

监控表:

  • ID，整数，非空
  • USERGUID，唯一标识符，空
  • PARAMETER2，varchar(50)，空
  • 其他东西...

在 PARAMETER1 中是客户指南以及存储的其他数据类型.

In PARAMETER1 are customer guids as wel as other data types stored.

现在的问题是根据客户的上次访问日期对他们进行排序，最近访问的客户必须位于网格的顶部.

Now the question came to order our customers according their last visit date, the most recent visited customers must come on the top of a grid.

我正在使用实体框架，但在比较字符串和 guid 类型时遇到问题，所以我决定在我的监控表顶部创建一个视图:

I'm using Entity Framework and I had problems of comparing the string and the guid type, so I decided to make a view on top of my monitoring table:

SELECT        
   ID, 
   CONVERT(uniqueidentifier, parameter2) AS customerguid, 
   USERguid, 
   CreationDate
FROM            
   MONITORING
WHERE        
   (dbo.isuniqueidentifier(parameter2) = 1) 
   AND 
   (parameter1 LIKE 'Customers_%' OR parameter1 LIKE 'Customer_%')

我在 EF 中导入了视图并进行了 Linq 查询.它什么也没返回，所以我提取了生成的 SQL 查询.在 SQL Management Studio 中测试查询时，出现以下错误:从字符串转换为 uniqueidentifier 时转换失败.

I imported the view in EF and made my Linq query. It returned nothing, so I extracted the generated SQL query. When testing the query in SQL Management Studio I got the following error: Conversion failed when converting from a character string to uniqueidentifier.

问题在于以下代码段(针对此问题进行了简化，但也给出了错误:

The problem lies in the following snippet (simplified for this question, but also gives an error:

SELECT *,
    (
        SELECT 
            [v_LastViewDateCustomer].[customerguid] AS [customerguid]
        FROM [dbo].[v_LastViewDateCustomer] AS [v_LastViewDateCustomer]
        WHERE c.GUID = [v_LastViewDateCustomer].[customerguid]
    )

FROM CM_CUSTOMER c

但是当我加入时，我得到了结果:

But when I do a join, I get my results:

SELECT *
FROM CM_CUSTOMER c
    LEFT JOIN
    [v_LastViewDateCustomer] v
on c.GUID = v.customerguid

我尝试制作 SQL 小提琴，但它正在该站点上运行.http://sqlfiddle.com/#!3/66d68/3

I tried to make a SQL fiddle, but it is working on that site. http://sqlfiddle.com/#!3/66d68/3

谁能指出我正确的方向?

Anyone who can point me in the right direction?

推荐答案

使用

TRY_CONVERT(UNIQUEIDENTIFIER, parameter2) AS customerguid

代替

 CONVERT(UNIQUEIDENTIFIER, parameter2) AS customerguid

视图被内联到查询中，并且 CONVERT 可以在 WHERE 之前运行.

Views are inlined into the query and the CONVERT can run before the WHERE.

有关其他讨论，请参阅 SQL Server 不应引发不合逻辑的错误

For some additional discussion see SQL Server should not raise illogical errors

相关文章