Efcore: Reverse Engineer: Scaffolding fails on Azure Sql Server v11 and SQL 2008 because sequences are not supported

[mhenderson442]

Steps to reproduce
Scaffold-DbContext -Provider "Microsoft.EntityFrameworkCore.SqlServer" -Context "DbMercurySchool" -OutputDir "DataModel" -Connection "Server=[Sql Azure Database Server];Data Source=[Sql Azure Database];Initial Catalog=MercurySchool;Persist Security Info=False;User ID=[User ID ];Password=[My Password];Pooling=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

The issue: SQL error thrown

Error Number:208,State:1,Class:16 One or more errors occurred. (Invalid object name 'sys.sequences'.)

See attached file.

scaffold_error.txt

EF Core

• version: 1.0.0-rc2-final
• Operating system: Win 10
• Visual Studio version: VS 2015 Community

Other details about my project setup:

So far database has one table. The schema for the table is academy not dbo.

[smitpatel]

Ah Sequence issue. We are trying to get metadata about sequences but not all version of Sql Azure supports sequences so database error.
@mhenderson442 - Can you also tell us details about the server like version of Sql Azure?

[mhenderson442]

The server hosting my database describes the following features:

• Server Version: V2
• Extended Transact SQL
• API Version 12

[ErikEJ]

Do a SELECT @@version

[ErikEJ]

@lajones - this check is not correct for Azure SQL database:

 private bool SupportsSequences => _serverVersion?.Major >= 11;

 private bool SupportsSequences => _serverVersion?.Major >= 11 || (IsAzureSqlDatabase && _serverVersion?.Major >= 12;

Probably need IsAzureSqlDatabase (SERVERPROPERTY('EngineEdition') = 5) in the class as well

 DECLARE @sqlVersion numeric(4,2)
 SELECT @sqlVersion = LEFT(CAST(SERVERPROPERTY('productversion') as VARCHAR), 4)
 IF (SERVERPROPERTY('EngineEdition') = 5 AND @sqlVersion < 12) OR (@sqlVersion < 11)
 -- No sys.sequences   

[mhenderson442]

SELECT @@VERSION outputs the following:

Microsoft SQL Azure (RTM) - 11.0.9231.349
May 20 2016 14:01:37
Copyright (c) Microsoft Corporation

SERVERPROPERTY('EngineEdition') = 5

SERVERPROPERTY('productversion') = 11.0

Off in the weeds:
Interesting that what I have does not support sequences. It was my understanding that SQL Azure supports sequences (reference). I don't have a need for them, but thought there was feature parity with 2012.

[ErikEJ]

@mhenderson442 Azure SQL database has version 11 and 12, it is possible to update from 11 to 12: https://azure.microsoft.com/da-dk/documentation/articles/sql-database-upgrade-server-portal/

[mhenderson442]

@ErikEJ Just saw that I can upgrade and am doing so.

[ErikEJ]

@mhenderson442 Great idea, that should unblock you!

[mhenderson442]

Upgrading to to version 12 unblocked me and I successfully executed Scaffold-DbContext.

@smitpatel @ErikEJ Should this issue be closed, or leave it open till the version 11 scenario is addressed?

[ErikEJ]

As long as Azure supports v11 databases, I vote for a bug fix

[smitpatel]

As as @ErikEJ said, since there are Azure database v11 out there, EF should not fail like that and have better check for server version. Leave this issue open please.

[mhenderson442]

Agreed. I will leave open.

[markti]

@mhenderson442 how did you get the scaffolding output log? all I get is "build failed."

[markti]

Guys I get this issue and have verified my Azure SQL DB is version 12. Anybody know how to turn on verbose logging for Scaffolding-DbContext?

[mhenderson442]

@markti I don't recall as it was more than a year ago I wrestled with this issue.

[smitpatel]

@markti - Use -Verbose flag in PMC.

[lajones]

@markti - note: we had a bug where the -Verbose flag wasn't actually outputting the error messages (see #7315). The fix for that is checked in now.

[smitpatel]

https://github.com/aspnet/EntityFrameworkCore/blob/eb8f7ede8d070d98583058bb7fe25b84bbf8101d/src/EFCore.SqlServer/Scaffolding/Internal/SqlServerDatabaseModelFactory.cs#L158-L161

We already do sequences conditionally, Does this still fail?

[ErikEJ]

Yes, this is still an issue, if you wish to continue to support Azure SQL Database v11, which has been retired: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-faq - othewise close

See my comment above: https://github.com/aspnet/EntityFrameworkCore/issues/5553#issuecomment-222294947

[smitpatel]

Thanks @ErikEJ - I missed that condition was incorrect. Probably should just fix this if we plan to support.

[symbiogenesis]

Azure Data Warehouse uses a different version numbering scheme. The latest version at this date is 10.0.9999.0.

Maybe it should check the compatibility level, rather than the version number. The compatibility level is 130, which is probably fine.

Edit: looks like Azure Data Warehouse doesn't currently support sequences. Thus, the compatibility level would not actually detect this. Ideally, a real feature detection would be attempted, such as seeing if a query on sys.sequences returns anything.

Here's my attempt

https://github.com/symbiogenesis/EntityFrameworkCore/commit/b8d814740adbc2afe47355b1bb4246abead521a3

[ajcvickers]

Triage: we should try to not need to check for whether a feature is available, or if we do we should find the most appropriate way to use version number, compatibility level, or whatever else to determine what is supported.