A particular LINQ-to-SQL query selecting fields from a SQL Server view in a C# program running against a SQL Server 2008 database, which runs fine in my local dev environment, produces an exception when run in the staging environment:
Exception Message: An error occurred while executing the command definition. See the inner exception for details.
Exception Trace: System.Data.Entity.Core.EntityCommandExecutionException
at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.b__5()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery`1..GetEnumerator>b__0()
at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at [my code ...]
What is causing this exception to occur?
This can be caused by a LINQ query that's trying to select a field that doesn't actually exist on the target database view or table.
One way this can happen (which was the problem in my case) is neglecting to deploy to the target environment a recently-created Entity Framework migration that adds the new field to the view being queried.
Another thing to look at is the inner exception of the thrown EntityCommandExecutionException (as suggested by the error message). In this case, the inner exception was of type SqlException and had the helpful message Invalid column name ‘[my column name]’.
So, things to look at when a EntityCommandExecutionException at EntityCommandDefinition.ExecuteStoreCommands gets thrown when running a LINQ-to-SQL query:
This can be caused by "Multiple Active Result Sets" missing in connection String.
Multiple Active Result Sets (MARS) is a feature that allows the execution of multiple batches on a single connection. In previous versions, only one batch could be executed at a time against a single connection. Executing multiple batches with MARS does not imply simultaneous execution of operations.
To Fix:
string connectionString = "Data Source=MSSQL1;" +
"Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +
"MultipleActiveResultSets=True";
Me helped the access to the Local property like. The exception :
foreach (var myTableObject in context.Table)
{
// Exception
}
foreach (var myTableObject in context.Table.Local)
{
// No exception
}