290326 (6) [Avatar] Offline
#1
Perhaps I'm missing something, but I'm getting an error in section 1.9.2, Reading data from the database. Is the L command supposed to work this early in the process?

The only changes I've made to the original Chapter 1 code are
1. Updated connection string to point to an existing development SQL Server and database. I'm confident that the connection string is valid because when I launch the console app the first time it tells me that the database exists.
2. Commented out the db.Database.EnsureDeleted line in Commands.cs because I don't have access to create new databases from scratch on this server. I do have dbo rights to the database that I specified in my connection string, though.

This is the error I get if I try to run l:
System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Invalid object name 'Books'.
Source=Core .Net SqlClient Data Provider
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
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(Boolean buffer)
at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
at MyFirstEfCoreApp.Commands.ListAllWithLogs() in L:\code\EfCoreInAction\EfCoreInAction-Chapter01\MyFirstEfCoreApp\Commands.cs:line 75
at MyFirstEfCoreApp.Program.Main(String[] args) in L:\code\EfCoreInAction\EfCoreInAction-Chapter01\MyFirstEfCoreApp\Program.cs:line 30



And this is the error I get if I try to run r:
System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Invalid object name 'Books'.
Source=Core .Net SqlClient Data Provider
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
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(Boolean buffer)
at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
at MyFirstEfCoreApp.Commands.ListAll() in L:\code\EfCoreInAction\EfCoreInAction-Chapter01\MyFirstEfCoreApp\Commands.cs:line 22
at MyFirstEfCoreApp.Program.Main(String[] args) in L:\code\EfCoreInAction\EfCoreInAction-Chapter01\MyFirstEfCoreApp\Program.cs:line 24



So I skipped ahead to run add-migration and update-database, and those commands worked. However, when I went back to 1.9.2 and ran the L command, it returned nothing and I still had to run R to seed the database. After that, I was able to use the L command to retrieve the list of titles shown in the book.

Jon P Smith (32) [Avatar] Offline
#2
Hi,

Looking at the stack trace and your comment at the end of your post it looks like you created an empty SQL database and then tried to access it from the demo code, which won't work. The line that says "Message=Invalid object name 'Books'." suggests EF Core was looking for a table called books and couldn't find it, which would be the case if the database existed but hadn't had its schema setup. You said at the end that you used add-migration and then update-database on the database and then the database worked, which reinforces my view that the database was there, but didn't have the tables defining in it.

If you were running from Visual Studio 2017 then installing VS2017 with its database feature would have installed a SQL Server on your machine. That SQL Server allows you to create/delete databases locally. The example code is designed to be runnable "out-of-the-box" and relies on the db.Database.EnsureCreated command to a) create a database if one isn't there, and b) add the tables and other constraints to the database.

I hope that helps explain what was happening.
290326 (6) [Avatar] Offline
#3
Thanks for the reply. My misunderstanding was in thinking that EnsureCreated would also ensure that the tables had been created. I now see that if an empty database exists, EnsureCreated immediately assumes that everything is fine. Since my office has a policy against installing SQL on the desktop, I had to my DBAs to grant me CREATE DATABASE access on my dev SQL instance. And then apparently EnsureCreated also leverages msdb.dbo.sp_send_dbmail so I needed EXEC rights to that as well, but once those were applied it ran beautifully.

Again, thanks!