System.Data.Entity.Core.EntityCommandExecutionException

Problem: A particular LINQ-to-SQL query selecting fields from a SQL Server view in a C# program, which ran fine in my local dev environment, produced 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 ...] 

Resolution: I had neglected to deploy to the staging environment (where the query was failing) a recently-created Entity Framework migration, which added a new output field to the SQL Server view being queried.  The query was failing because it was trying to select a field which didn’t actually exist on the target view.

Upon closer examination, when I reproduced the error and inspected the inner exception (which wasn’t being automatically written to the application’s error log), it was indeed of type SqlException and had the helpful message “Invalid column name ‘[my column name]’”.

So, things to try next time I see this exception:

• Examine the inner exception (as suggested by the outer exception’s error message).
• Make sure all Entity Framework migrations have been deployed to the target environment (if EF is in use).
• Check and see whether the query is trying to select a field that doesn’t exist.

SQL – Getting the record counts of each distinct value from a table column

SELECT column_name, COUNT(column_name) as RecordCount
FROM table_name
WHERE (whatever criteria)
GROUP BY column_name
ORDER BY column_name

Blogged to help me remember and memorize!