Problem accessing List of a query

2

I'm starting today with Entity-FrameWork but I came across a problem that I think is simple, but I could not find the answer anywhere, maybe because I was not really sure what to look for.

The code is as follows:

        using(var db = new CadastroContext())
        {
            var query = from c in db.Cadastros select c;
            foreach(Cadastro ca in query)
            {
                Debug.WriteLine(ca.CadastroID +" :: "+ca.Familiares.Count); //O erro sempre acontece quando tenta acessar a List
            }
        }

It usually ran until I directed the Context to a localdb

public class CadastroContext : DbContext
{
    /*
    public CadastroContext() : base()
    {

    }
    */
    public CadastroContext()
        : base(@"Data Source=(localdb)\v11.0;AttachDbFilename=D:\GitHub\SaneamentoBR\GerenciadorPesquisa\GerenciadorPesquisa\PesquisaSorriso.mdf;Initial Catalog=PesquisaSorriso;Integrated Security=True")
    {

    }

    public DbSet<Cadastro> Cadastros { get; set; }
    public DbSet<Familiar> Familiares { get; set; }
    public DbSet<Telefone> Telefones { get; set; }
}

Now it always generates an error, and the only record it can access the List is the last one. And when I add another element, only the last one is able to re-access the list repeating this cycle.

The generated stacktrace is this:

System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Exceção não tratada</Description><AppDomain>GerenciadorPesquisa.vshost.exe</AppDomain><Exception><ExceptionType>System.Data.Entity.Core.EntityCommandExecutionException, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>An error occurred while executing the command definition. See the inner exception for details.</Message><StackTrace>   em System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   em System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   em System.Data.Entity.Core.Objects.ObjectQuery'1.&amp;lt;&amp;gt;c__DisplayClass7.&amp;lt;GetResults&amp;gt;b__6()
   em System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func'1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   em System.Data.Entity.Core.Objects.ObjectQuery'1.&amp;lt;&amp;gt;c__DisplayClass7.&amp;lt;GetResults&amp;gt;b__5()
   em System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func'1 operation)
   em System.Data.Entity.Core.Objects.ObjectQuery'1.GetResults(Nullable'1 forMergeOption)
   em System.Data.Entity.Core.Objects.ObjectQuery'1.Execute(MergeOption mergeOption)
   em System.Data.Entity.Core.Objects.DataClasses.EntityCollection'1.Load(List'1 collection, MergeOption mergeOption)
   em System.Data.Entity.Core.Objects.DataClasses.EntityCollection'1.Load(MergeOption mergeOption)
   em System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.Load()
   em System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.DeferredLoad()
   em System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.LoadProperty[TItem](TItem propertyValue, String relationshipName, String targetRoleName, Boolean mustBeNull, Object wrapperObject)
   em System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.&amp;lt;&amp;gt;c__DisplayClass7'2.&amp;lt;GetInterceptorDelegate&amp;gt;b__1(TProxy proxy, TItem item)
   em System.Data.Entity.DynamicProxies.Cadastro_875C0EB92CA3613483BF1786024F3D0495A118E3EA8FBBD34C417E9919F975D3.get_Familiares()
   em GerenciadorPesquisa.Program.Main() na d:\GitHub\SaneamentoBR\GerenciadorPesquisa\GerenciadorPesquisa\Program.cs:linha 78
   em System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   em System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   em Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   em System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   em System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   em System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---&amp;gt; System.InvalidOperationException: Já existe um DataReader aberto associado a este Command que deve ser fechado primeiro.
   em System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
   em System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
   em System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   em System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource'1 completion, Int32 timeout, Task&amp;amp; task, Boolean asyncWrite)
   em System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   em System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   em System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   em System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   em System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.&amp;lt;Reader&amp;gt;b__c(DbCommand t, DbCommandInterceptionContext'1 c)
   em System.Data.Entity.Infrastructure.Interception.InternalDispatcher'1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func'3 operation, TInterceptionContext interceptionContext, Action'3 executing, Action'3 executed)
   em System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   em System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   em System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   em System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   --- Fim do rastreamento de pilha de exceções internas ---
   em System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
   em System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
   em System.Data.Entity.Core.Objects.ObjectQuery'1.&amp;lt;&amp;gt;c__DisplayClass7.&amp;lt;GetResults&amp;gt;b__6()
   em System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func'1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   em System.Data.Entity.Core.Objects.ObjectQuery'1.&amp;lt;&amp;gt;c__DisplayClass7.&amp;lt;GetResults&amp;gt;b__5()
   em System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func'1 operation)
   em System.Data.Entity.Core.Objects.ObjectQuery'1.GetResults(Nullable'1 forMergeOption)
   em System.Data.Entity.Core.Objects.ObjectQuery'1.Execute(MergeOption mergeOption)
   em System.Data.Entity.Core.Objects.DataClasses.EntityCollection'1.Load(List'1 collection, MergeOption mergeOption)
   em System.Data.Entity.Core.Objects.DataClasses.EntityCollection'1.Load(MergeOption mergeOption)
   em System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.Load()
   em System.Data.Entity.Core.Objects.DataClasses.RelatedEnd.DeferredLoad()
   em System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.LoadProperty[TItem](TItem propertyValue, String relationshipName, String targetRoleName, Boolean mustBeNull, Object wrapperObject)
   em System.Data.Entity.Core.Objects.Internal.LazyLoadBehavior.&amp;lt;&amp;gt;c__DisplayClass7'2.&amp;lt;GetInterceptorDelegate&amp;gt;b__1(TProxy proxy, TItem item)
   em System.Data.Entity.DynamicProxies.Cadastro_875C0EB92CA3613483BF1786024F3D0495A118E3EA8FBBD34C417E9919F975D3.get_Familiares()
   em GerenciadorPesquisa.Program.Main() na d:\GitHub\SaneamentoBR\GerenciadorPesquisa\GerenciadorPesquisa\Program.cs:linha 78
   em System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   em System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   em Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   em System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   em System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   em System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   em System.Threading.ThreadHelper.ThreadStart()</ExceptionString><InnerException><ExceptionType>System.InvalidOperationException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Já existe um DataReader aberto associado a este Command que deve ser fechado primeiro.</Message><StackTrace>   em System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
   em System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
   em System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   em System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource'1 completion, Int32 timeout, Task&amp;amp; task, Boolean asyncWrite)
   em System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   em System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   em System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   em System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   em System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.&amp;lt;Reader&amp;gt;b__c(DbCommand t, DbCommandInterceptionContext'1 c)
   em System.Data.Entity.Infrastructure.Interception.InternalDispatcher'1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func'3 operation, TInterceptionContext interceptionContext, Action'3 executing, Action'3 executed)
   em System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   em System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   em System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   em System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)</StackTrace><ExceptionString>System.InvalidOperationException: Já existe um DataReader aberto associado a este Command que deve ser fechado primeiro.
   em System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
   em System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
   em System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
   em System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource'1 completion, Int32 timeout, Task&amp;amp; task, Boolean asyncWrite)
   em System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   em System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   em System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   em System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   em System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.&amp;lt;Reader&amp;gt;b__c(DbCommand t, DbCommandInterceptionContext'1 c)
   em System.Data.Entity.Infrastructure.Interception.InternalDispatcher'1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func'3 operation, TInterceptionContext interceptionContext, Action'3 executing, Action'3 executed)
   em System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   em System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
   em System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   em System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)</ExceptionString></InnerException></Exception></TraceRecord>

I have tried to delete all the tables, but nothing solved.

@update I added a .ToList () at the end of the query and apparently solved it, but I still do not understand why, I'll leave the question open if someone can explain what happened, it can be useful ...

    using(var db = new CadastroContext())
    {
        var query = (from c in db.Cadastros select c).ToList();
        foreach(Cadastro ca in query)
        {
            Debug.WriteLine(ca.CadastroID +" :: "+ca.Familiares.Count); 
        }
    }
    
asked by anonymous 12.05.2016 / 23:17

1 answer

1

LINQ, by default, works with entities with the use of the concept LazyLoading (Only loads properties when necessary). In this case, it does not yet include relationship properties that will not be used unless you explicitly define it. To do this, you use objeto.Include(String path) .

In this case, your code would look like this:

using(var db = new CadastroContext())
{
    var query = from c in db.Cadastros.Include("Familiares") select c;

    foreach(Cadastro ca in query)
    {
        // Já não acontece erro algum
        Debug.WriteLine(ca.CadastroID +" :: "+ca.Familiares.Count);
    }
}

I hope I have helped \ o /

    
13.05.2016 / 00:00