Efcore: Entity Framework Core 2.0 Scaffold-DbContext returns Build Error

[LakshmiPrasanna-Human]

In visual Studio 2017, create a .NET core class library project with Target Framework as 2.0 (No project.json).

Have included below packages by Package Manager console.

Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.SqlServer.Design
Microsoft.EntityFrameworkCore.Tools
Microsoft.VisualStudio.Web.CodeGeneration.Design
While running below Scaffold-DbContext command from Package Manager console of same project , getting Build Failed error.

Steps to reproduce

Scaffold-DbContext "Data Source=HTGHTFG135611L;Initial Catalog=HHH;Persist Security Info=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DBModels -force -v

Also tried with giving username and password

Further technical details

EF Core version: 2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer)
Operating system: Windows 8 32 bit
IDE: Visual Studio 2017 community edition

[bricelam]

If you manually build the project (e.g. using Ctrl+Shift+B) you'll see the specific errors.

[smitpatel]

Microsoft.EntityFrameworkCore.SqlServer.Design

This package is no longer in 2.0 release. You need to remove reference to it.

[LakshmiPrasanna-Human]

1. I have removed "Microsoft.EntityFrameworkCore.SqlServer.Design"
2. Build the project , its not showing any build errors. Build is successful.
3. When running Scaffold-DBContext from powershell command , it throwing the same error.
   PM> Scaffold-DbContext "Data Source=HTGHTFG135611L;Initial Catalog=HHH;Persist Security Info=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DBModels -force -v
   Using project 'HHH.DataModel'.
   Using startup project 'HHH.DataModel'.
   Build started...
   Build failed.

[pmrozek]

I have the same problem with my project (class library).

Further technical details
.NET Core version: 2.0
Database: MS SQL Server 2016 Developer [Version 13.0.4001.0]
Operating system: Windows 10 64 bit Enterprise [Version 1703 / 15063.540]
IDE: Visual Studio Professional 2017 [Version 15.3.2]

My project settings:
< Project Sdk="Microsoft.NET.Sdk">

< PropertyGroup Label="Globals">
< SccProjectName>SAK< /SccProjectName>
< SccProvider>SAK< /SccProvider>
< SccAuxPath>SAK< /SccAuxPath>
< SccLocalPath>SAK< /SccLocalPath>
< /PropertyGroup>

< PropertyGroup>
< TargetFramework>netcoreapp2.0< /TargetFramework>
< /PropertyGroup>

< ItemGroup>
< PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.0.0" />
< PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.0" />
< PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.0.0" />
< /ItemGroup>

< ItemGroup>
< Service Include="{508349b6-6b84-4df5-91f0-309beebad82d}" />
< /ItemGroup>

< /Project>

NuGet Package Manager Console:
PM> Scaffold-DbContext "Server=(local);Database=CrossPlatform;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -force
Build failed.

[bricelam]

We may need someone on dotnet/project-system to help investigate. Our tool just calls the following. No additional information is available from the EnvDTE interfaces.

$DTE.Solution.SolutionBuild.Build($true)
if ($DTE.Solution.SolutionBuild.LastBuildInfo)
{
    throw 'Build failed.'
}

[bricelam]

@LakshmiPrasanna-Human or @pmrozek Can your share your solution so we can investigate?

[pmrozek]

Sure, please find the attached file (project + database).

CrossPlatform.zip

[LakshmiPrasanna-Human]

Hi, The issue got resolved in my case. However i still see a bug in this. Here are the details

Current Solution: My current solution has two projects. One of the project(ProjectA) has build errors. In ProjectB i am running the Scaffold command from Package Manager Console. In Package Manager Console , ProjectB is clearly selected. In this scenario its throwing error "Build Error" and code files are not auto generated.

Work Around: I have unloaded ProjectA and re run the command. Now code files are auto generated.
However ProjectA and ProjectB are not related to each other. Also in Package Manager Console, i have selected ProjectB, why should it look for ProjectA build errors.

[bricelam]

I have selected ProjectB, why should it look for ProjectA build errors.

There were bugs in certain project types when we tried to only build the selected project, so we had to resort to building the entire solution.

There's a TODO in the code for finding a reliable way to build just the selected project.

[syndicatedshannon]

This dependency on building the entire solution to build the data models is quite an obstacle, since the data models are at the lowest layers of my solution dependencies. Add this to the list of non-starters in EF Core for me.

[syndicatedshannon]

LakshmiPrasanna-Human 👍 : we are using your workaround to unload projects, to manually operate in a DB-first approach. Sadly it makes it nearly impossible to create any automated development tooling for this.

[ErikEJ]

@syndicatedshannon you can use SQLite toolbox to generate the model, does not require anything to be compiled

[syndicatedshannon]

@ErikEJ : Thank you for that. I wasn't aware of that tool.

I know this is a tangent, but I see it mentions generation directly from DACPAC. Creating EF Core models directly from my SQL Server Data Project (which is currently the authority for my schema 'truth') would simplify things greatly for me. Currently the only reason I generate from a database instance is as a round-about step to get code from my schema.

I also see that SQLite Toolbox depends on https://marketplace.visualstudio.com/items?itemName=SimonHughes.EntityFrameworkReversePOCOGenerator , which has "Support EF Core" on its TODO list.

Do you know offhand if the relationship between SQLite Toolbox and this dependency invalidates it for this idea?

I've recently asked this similar question at StackOverflow also.

[ErikEJ]

@syndicatedshannon Well, that particular feature only works for EF6 yes.

It is quite simple to deploy your dacpac to a "blank" localdb database (either via gui or using a command line script) , and then run reverse engineer from that, using the SQLite Toolbox.

For the command line script to do that, you can use sqlcmd.exe + sqlpackage.exe to create the database and deploy the dacpac file.

Are you looking for a way to create the reverse engineer code from a standalone command line tool? (Not using dotnet ef or PMC in VS)

[syndicatedshannon]

@ErikEJ : Yes, the "blank" localdb workflow is exactly how I ended up here commenting about the inability to generate code on a project that wouldn't build, above :) . I was currently doing it manually and resolving workflow bugs, and was just about to script it out, so thank you for the tool references (sqlcmd.exe + sqlpackage.exe).

Are you looking for a way to create the reverse engineer code from a standalone command line tool?

Well, in a perfect world, it would run on changes, ideally processing deltas only, as part of MSBuild. This would update my data models when my DB Schema changes (e.g. via Git submodule version bump), ensuring that the code always matches the DB Schema it is associated with.

However, even if it rebuilds all files on change, the desire to run as part of an MSBuild change-detect is why my ideal scenario doesn't have a stopover in a DB instance.

I could easily be missing some generally-accepted solution to this problem.

Hope that clarifies.

[syndicatedshannon]

Maybe it's simplest to say that my desire is to fail build if my (version-controlled) EF Models don't match my (version-controlled) DB Schema, in a DB-First EF Core environment.

[syndicatedshannon]

Thinking more about what/why you were asking, I probably didn't really clarify my goal.

My current near-term plan is to create an MSBuild Task that runs whenever any file in my DB Schema project is newer than a timestamp file stored in my EF Core Model project.

An example of a C# class that implements ITask doing this:

• conditionally run based on file timestamps (MSBuild does this internally)
• use SQL SMO/DMO to create a new instance from DACPAC (sadly DMO is prone to version issues)
• initiate SQLite Toolbox reverse engineer by a process I haven't researched yet
• optionally remove the DB instance
• update EF Model project regeneration timestamp

[ErikEJ]

Re step 2: DacFX should be used for that, not SMO

Re step 3: Sounds like you are asking for a command line tool!

[bricelam]

@pmrozek I am not able to reproduce the issue on the solution you provided. Does unload projects work around the issue for you?

[bricelam]

Notes for triage: We should create a new issue about only building the startup project and not the entire solution.

[syndicatedshannon]

This issue occurs in my current project. I cannot share my code, but I don't think it should be necessary.
It seems clear from the code snippets above (https://github.com/aspnet/EntityFrameworkCore/issues/9515#issuecomment-324375818) that if the entire solution does not build, the scaffold will fail.

That means the solution can easily become 'wedged', where if the developer deletes or modifies the database, discovers a portion of the changes are incorrect, and tries to modify it again, they will be unable to resolve the issue without reverting their work.

Additionally, because the scaffold may result in renames or removals, any automated processes/tooling would need to clean models first, also resulting in a solution that won't build, and therefore a paradoxical tooling requirement.

Unloading all other projects is a viable workaround for manual builds, provided the project where scaffolding is going to be placed will build without the scaffolding already existing. This creates a stipulation (probably unintended) where even when using this workaround, no handwritten code may coexist in a scaffold-output project.

[ajcvickers]

We have created a new issue to cover building only the startup project rather than the solution--see #9716.

There's a lot of other discussion in this thread not directly related to this. Some of it is covered by the work being done for #831 (Update model from database). Feel free to comment with thoughts on that issue. If there are other specific EF things that you feel are not being tracked anywhere, then please file new issues to track them so that we can manage them better.

[satvik1986]

how to generate domain class from existing database using scaffold in entityframework core?.

[satvik1986]

Scaffold-DbContext "server=servername;database=testDb;user=test;password=newpass;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -Context "testContext"
-- > using package manager console

# Getting error

System.Data.SqlClient.SqlException (0x80131904): Failed to generate SSPI context.
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.EntityFrameworkCore.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(DbConnection connection, IEnumerable1 tables, IEnumerable1 schemas) at Microsoft.EntityFrameworkCore.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(String connectionString, IEnumerable1 tables, IEnumerable1 schemas) at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(String connectionString, IEnumerable1 tables, IEnumerable1 schemas, Boolean useDatabaseNames) at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ModelScaffolder.Generate(String connectionString, IEnumerable1 tables, IEnumerable1 schemas, String projectPath, String outputPath, String rootNamespace, String contextName, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames) at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable1 schemas, IEnumerable1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable1 schemaFilters, IEnumerable1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_1.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_01.b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:b527a95f-e41b-499d-ba39-c359653928b0
Failed to generate SSPI context.

[ErikEJ]

@satvik1986 Please do not add to existing issues, also, this is a SQL logon issue, not an EF Core issue

[ErikEJ]

@faisal5170 pls raise a new issue with a reproduction - can you connect without having EF Core involved?

[faisal5170]

Hello sir @ErikEJ ,
I have one question, If we change in context class which genertaed by "Scaffold-DbContext" this command in .net core and we change static connection-string to dynamic connection-string logic and then we want to update table so we fire again same command at that time whole dbcontext class will be change (as new) any suggestion to just update entity class with old changes ?

[ErikEJ]

@faisal5170 the classes are partial, so create a MyContext.partial.cs file and make your changes there.