Efcore: RevEng: Use underlying types (not aliases) in the resulting model

[he-dev]

I'm using a database where there are a lot of user defined data-types. There are also like 60 tables or more so I used the dotnet ef dbcontext scaffold command to generate the models.

In the project file I included

   <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.1.0-preview1-final" />

and generated it with the following command:

$connectionString = "Data Source=(local);Initial catalog=TestDb;Persist Security Info=True;Integrated Security=True;"
$outputDir = "Entities\TestDb"
$context = "TestContext"

dotnet ef dbcontext scaffold "$connectionString" Microsoft.EntityFrameworkCore.SqlServer --output-dir "$outputDir" --force --context "$context" --schema dbo --use-database-names

the resulting context is

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<UserDefinedDataTypeBug>(entity =>
            {
                entity.Property(e => e.Id).ValueGeneratedNever();

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

When I now try to query this model with

            using (var context = new TestContext())
            {
                var results =
                    context
                        .UserDefinedDataTypeBug
                        .Where(x => x.Status == 1)
                        .ToList();
            }

it throws a SqlException that reads

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

with the stack-trace

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__17`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at UserDefinedDataTypeBug.Program.Main(String[] args) in C:\...\UserDefinedDataTypeBug\Program.cs:line 13

I've read (e.g. here) that this should work but it doesn't. I have to manually comment the HasColumnType line out in order for the query to work.

Steps to reproduce

I have created a small console application that reproduces this issue. You can find it in here in my repository.

Further technical details

EF Core version: (2.1.1)
Database Provider: (Microsoft.EntityFrameworkCore.SqlServer) SqlServer 2014
Operating system: Window 10 Pro
IDE: (Visual Studio 2017 15.7.4)

[he-dev]

You don't have to support UDT at all, just don't make them stand in the way ;-) an option to disable it completely and use the actual sql-type would be great.

[he-dev]

Workaround

A possible workaround is to remove the annotations after they have been added:

modelBuilder
    .Entity<MyEntity>()
    .Property(nameof(MyEntity.Status))
    .Metadata
    .RemoveAnnotation("Relational:ColumnType");

I also wrote a helper extension for removing all annotations from all generated entities. See my answer on SO for the full source code.

[divega]

@he-dev I am a little confused by your posts here and in StackOverflow.com.

We would need to understand if Enumeration is an actual UDT or just an alias for some built-in SQL Server type. The distinction is actually very important. You seem to imply that it is just an alias when you say we don't have to support UDTs, but the error seems to indicate that they are UDTs. If they are UDTs though, I cannot understand what your workaround does to make things work.

Enabling UDTs would require some work in EF Core provider for SQL Server. For example, when we generate parameters, we need to plumb through the UdtTypeName which was added to SqlClient for .NET Core in 2.1.

That said, assuming you are talking about UDTs, the primary reason you are seeing an exception form SqlClient is that the latter is not finding the assembly with the UDTs.

SQL Sever user-defined types are CLR types that need to be registered on the server and available on the client. When you (or in this case EF Core) calls ExecuteReader and tries to deserialize UDT instances, it needs to be able to find the assembly defining the UDT types. Currently the mechanism used is very simple and the assembly defining the UDTs has to be deployed with the program.

[divega]

Note for triage: While we wait from an answer from the customer, we should have issue for end-to-end support UDTs with the EF Core provider for SQL Server, especially now that SqlClient for .NET supports it.

@roji should be a good source of insights on this since he has already done equivalent work on Npgsql.

[roji]

@divega I'm not sure the PostgreSQL/Npgsql angle helps much, but here's some general info.

In PostgreSQL, UDTs are called composite types. They are a purely PostgreSQL construct, created via a CREATE TYPE DDL statement - no assembly or similar is deployed server-side. At the ADO.NET Npgsql supports mapping a class or struct via a single global at the start of the program, or per-connection (see these docs). However, at the EF Core level support isn't there yet, this is tracked by https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/22, and should be relatively straightforward after related/similar work has been done for enums and released in 2.1. It should definitely be released for 2.2

I'm not sure I see much need for cross-provider compatibility... Actually setting things up is going to be different from provider to provider - Npgsql will require a single global call which maps a PostgreSQL composite type to a .NET user type, and the provider will likely set DataTypeName when sending parameters (just like UdtTypeName). But hopefully I'll get around to working on composite support at some point and will raise any issues or potential helpful support from EF Core.

Let me know if you'd like to have any other details or see any opportunity for cross-provider behavior here.

[divega]

@roji I totally get that they are different. I was after a couple of possible similarities:

1. UDTs need CLR types that work at the ADO.NET layer. SqlClient’s mechanism happens to be very simple but also inflexible. Here is a call to create a true plug-in model:
   https://github.com/dotnet/corefx/issues/29139.

2. UDT’s CLR types need to extend the EF Core provider’s type mapper somehow. We have API for this but I think we maybe missing something consumable by app developers. Value converters provide patterns that can be used in model building but it would be more convinient if it happened before: by the time type discovery happens, ideally the type mapper responded that the UDTs are known scalar types, rather than having to go down the route of classifying them as entity candidates and backtracking later once the UDTs have been registered.

Perhaps there is an opportunity to avoid having redundant registration mechanisms. E.g. the ADO.NET layer could offer an API to respond what types are registered that the EF Core provider could consume. Although I am quite skeptical. I don’t think on SqlClient has any concept of registration of the CLR typees currently.

[roji]

I understand. Regarding the registration, as I wrote above at the ADO.NET level Npgsql requires you to explicitly register you CLR type, mapping out to a PostgreSQL composite. The EF Core provider would then gets all these mappings in the Type mapping source, automatically - this is exactly how enum support is currently implemented, and it seems to work well. So an ADO.NET registration is all that is needed to set everything up.

If SqlClient had no similar registration, then you indeed have the issue of making the EF Core provider aware of the mapping, likely by some sort of special API exposed by the provider (maybe in OnModelConfiguring?).

I'll think about this a bit more to see if there's anything else of interest.

[he-dev]

@divega I have provided a complete demo project with all SQL scripts and all code that is necessary to reproduce this behaviour. The UDT Enumeration is also defined there as

create type Enumeration from smallint null

I'm not sure what else I can do? The UDT is of course a SQL type so there is no assembly defining it - not that I know of - well, EF6 could handle it without any additional assemblies.

My workaround removes the Relational:ColumnType annotation from the generated code that causes EF-Core to crash which I also said earlier. I'm doing this automatically because searching and removing this annotation from each affected property in 10k lines of generated code would be a lot of work.

[divega]

@he-dev Thanks for the additional details. I must have missed the complete demo project. In fact looking at the entity types would have helped the most.

Anyway, that "create type" syntax is used to create a type alias. It is definitively not the UDT scenario I was concerned about.

Leaving this open to discuss it in the next triage.

[ajcvickers]

Problematic SQL is:

SELECT [x].[Id], [x].[Status]
      FROM [UserDefinedDataTypeBug] AS [x]
      WHERE [x].[Status] = CAST(1 AS Enumeration)

T-SQL docs say that alias types cannot be used in CAST:

data_type
The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.

[bricelam]

We're going to investigate removing the alias names in the resulting model. (RevEng would still handle alias, they just wouldn't make it into the HasColumnType() calls.)

[DanielSSilva]

I'm currently migrating from 2.0 to EF 3.1 and still bumping into this error. In my case, I have a UDT called fid which was created as follows:

CREATE TYPE [dbo].[fid] FROM BIGINT NOT NULL; -- foreign id

when doing something similar to

IEnumerable<long> someIdsToCompare 

await repository.AllAsQueryable().Where(entity => someIdsToCompare.Contains(entity.SomeId))

throws the same exception

Exception thrown: 'Microsoft.Data.SqlClient.SqlException' in System.Private.CoreLib.dll: 'Type fid is not a defined system type.'

How can I catch the generated SQL, in order to check if it's the same scenario?

[ajcvickers]

@DanielSSilva Can you please file a new issue and include the tables from your schema that are causing this issue so that we can investigate.

[DanielSSilva]

FYI: created #20773 . Let me know there if there if anything else is needed