具有数据类型前缀的列会导致“类型X不是定义的系统类型".错误 [英] Columns with data-type prefix cause "Type X is not a defined system type" error

问题描述

我正在使用第三方数据库.通常，我用LINQPad编写的查询，后来转换为Entity Framework Core(2.1)，可以正常工作.但是，这次不是.

I'm working with a 3rd party database. Usually queries that I write in LINQPad and later translate to Entity Framework Core (2.1) work without any issues. However, not this time.

当我尝试使用列 Status 时，我得到以下 SqlException :

When I try to use the column Status I'm getting the following SqlException:

Class         16  byte
LineNumber    1   int
Message       "Type Enumeration is not a defined system type."    string
Number        243 int
Procedure     ""  string
Server        "..."   string
Source        ".Net SqlClient Data Provider"  string
State         2   byte

所以我打开SQL Server Management Studio，发现此列(及其他一些列)使用 strage 数据类型:

so I opend the SQL Server Management Studio and found that this column (and few others) use strage data-types:

带有 strage 的意思是，它们像在这里一样以 Enumeration 或 CurrencyCode:varchar(3)等开头.

with strage I mean they are prefixed like here with Enumeration or CurrencyCode:varchar(3) etc.

我使用EF-Core脚手架创建了实体，并将该属性定义为:

I used EF-Core scaffolding to create the entities and the property is defined as:

public short Status { get; set; }

看起来不错，但EF-Core引擎显然识别出 Enumeration 前缀，并在 OnModelCreating 方法中生成了这一行:

which looks fine but the EF-Core engine apparently recognized the Enumeration prefix and generated this line in the OnModelCreating method:

entity.Property(e => e.Status).HasColumnType("Enumeration");

我想这是导致错误的原因.

and I guess this is causing the error.

我以前从未见过这种情况，我想知道为什么EF-Core不能查询这样的列，而LINQPand不是，这些奇怪的数据类型是什么?

I've never seen this before and I'm wondering why is EF-Core failing to query such columns whereas LINQPand isn't and what are those strange data-types?

EF-Core是否有 magic 或 secret 选项可以解决此问题?

Is there any magic or secret option for EF-Core to fix this issue?

推荐答案

为什么之所以这样显示，是因为您使用的是用户定义的数据类型.该脚本重现了该问题:

Why it's showing up like this is because you're using a User-Defined Data Type. This script reproduces the issue:

create type Enumeration from smallint null
go
create table T (
    ID int not null,
    E Enumeration not null
)

如何我无法说出这种情况.我认识的大多数人(我很欣赏这只是意见)避免使用SQL Server中的用户定义类型(其他用户定义的 table 类型)，因为它们开始时只有一半的工作. ¹

How you've ended up in this situation I couldn't say. Most people I know (I appreciate this is just opinion) shun the User-defined types in SQL Server (other that User-defined table types) because they only half work to start with.¹

¹ 例如对于用户定义的表类型，一个有用的功能是可以自动应用特定的约束-该数字只能在1-100范围内.您不能使用UDT系统执行此操作.您可以分别创建 RULE 并将其绑定到列，但是现在您可以在多个位置分发此定义，并且 规则已被弃用.

¹E.g. a useful feature for user-defined table types would be if you could apply specific constraints automatically - a number that can only fall in the range 1-100 say. You cannot do this with the UDT system. You can separately create RULEs and bind those to columns but you're now distributing this definition in multiple places and rules are deprecated anyway.

这篇关于具有数据类型前缀的列会导致“类型X不是定义的系统类型".错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！