CODEWITHSUNDEEP
c#t-sqlentity-framework-coresql-server-2014
t3chb0t
t3chb0t

Reputation: 18734

Columns with data-type prefix cause "Type X is not a defined system type" error

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.

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

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

enter image description here

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

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

public short Status { get; set; }

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.

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?

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

Upvotes: 0

Views: 392

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239804

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
)

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

1E.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.

Upvotes: 1

Related Questions