0

I am trying to create model entities for use in LINQ in .NET Core 6.0 in VS 2022 since .edmx files are now deprecated. I am connecting to an existing SQL Server db at an external ISP using sql authentication. The db has compatibility level SQL Server 2000 (80). I am using Windows 11 and Visual Studio 2022 version 17.2.3.

The command I am using from Package Manager for the startup project (contains Program.cs) in this console app is:

PM> Scaffold-DbContext "Server=my SQL Server Fully qualified domain name;Database=myDataBase;User Id=myUsername;Password=myPassword;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables myschema.mytable

The error and detail I get is below and is essentially "Invalid column name 'is_sparse'." There is no is_sparse column in any table in this db. I get the same error if I don't put the schema or I don't use the -Tables switch or I use an incorrect table name in that switch. I am suspecting that I have a misconfiguration of some kind.

How do I fix this so I can get the entities and use them in LINQ queries?

Thanks in advance for any help on this.

Details:

Packages in the project (from parts of projectname/bin/Debug/net6.0/projectname.deps.json)

      "DataRetrieval": "1.0.0",
      "Microsoft.EntityFrameworkCore.Design": "6.0.6",
      "Microsoft.EntityFrameworkCore.Tools": "6.0.6",
      "Microsoft.Extensions.Configuration": "6.0.1",
      "Microsoft.Extensions.Configuration.Binder": "6.0.0",
      "Microsoft.Extensions.Configuration.Json": "6.0.0",
      "Microsoft.Extensions.Configuration.UserSecrets": "6.0.1",
      "Microsoft.Extensions.Hosting": "6.0.0",
      "Microsoft.Extensions.Options": "6.0.0",
      "Newtonsoft.Json": "13.0.1"
      "AutoMapper 11.0.1"
      
  "Microsoft.EntityFrameworkCore/6.0.6": {
    "dependencies": {
      "Microsoft.EntityFrameworkCore.Abstractions": "6.0.6",
      "Microsoft.EntityFrameworkCore.Analyzers": "6.0.6",
      "Microsoft.Extensions.Caching.Memory": "6.0.1",
      "Microsoft.Extensions.DependencyInjection": "6.0.0",
      "Microsoft.Extensions.Logging": "6.0.0",
      "System.Collections.Immutable": "6.0.0",
      "System.Diagnostics.DiagnosticSource": "6.0.0"

Error: Here's the error from Package Manager (PM):

PM> Scaffold-DbContext "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables myschema.mytable Build started... Build succeeded. To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263. Security Warning: The negotiated TLS 1.0 is an insecure protocol and is supported for backward compatibility only. The recommended protocol version is TLS 1.2 and later. Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'is_sparse'. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.GetColumns(DbConnection connection, IReadOnlyList1 tables, String tableFilter, String viewFilter, IReadOnlyDictionary2 typeAliases, String databaseCollation) at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.GetTables(DbConnection connection, DatabaseModel databaseModel, Func3 tableFilter, IReadOnlyDictionary2 typeAliases, String databaseCollation) at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options) at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options) at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions) at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable1 schemas, IEnumerable1 tables, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluralize) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable1 schemaFilters, IEnumerable1 tableFilters, String modelNamespace, String contextNamespace, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames, Boolean suppressOnConfiguring, Boolean noPluarlize) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_01.b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) ClientConnectionId:ba6b143c-3916-4582-a07f-5ea712131557 Error Number:207,State:1,Class:16 Invalid column name 'is_sparse'.

Steve

steve_user2685848
  • 1
  • 1

1 Answers1

0

Thanks @ErikEJ at https://github.com/dotnet/efcore/issues/28301! Only SQL Server 2012 and later is supported by scaffolding. I suspected db versioning was the issue.

I scripted just the tables I needed locally and put them in a local SQL2019 db. I then ran the scaffolding. I then changed my VS2022 solution to connect to the local db. I subsequently changed the connection to the remote SQL Server 2000 instead of the local db and that gave me working live data.

Alternatively, I could have used VS2019/.Net framework 4.8 to generate the db context and POCOs for the handful of tables I need. Then I could copy the context and POCO's .cs files to my .NET Core solution.

steve_user2685848
  • 1
  • 1