Tuesday, June 09, 2015

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.

Monday, June 01, 2015

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!

Monday, April 06, 2015

Getting the fractional portion of a number in C#

Quick technique for “truncating” the integer portion of a decimal value in C#, leaving only the fractional portion:

    static decimal GetFractionalPortion(decimal d)
    {
        return d % 1; 
    }

Calling that method with the value 12.34 will return 0.34.

This works because the modulo operator (%) returns the remainder after dividing the first operand by the second, and any value divided by 1.0 returns the fractional portion of the value.

Note that if a negative value is used, the return value (the fractional portion) will also be negative.

Thursday, March 19, 2015

C#: Default value for int? and other nullable types

If you’re a C# developer, you’re probably aware of the nullable versions of primitive types such as int. Just declare the variable with a question mark added to the type, like so:

int? i;

And presto, you have an int variable to which you can assign null!  Very handy for working with nullable number fields in databases, among other uses.

Before today, the above was pretty much the extent of my knowledge of C# nullable types.  As far as I was concerned, they were just “magic” versions of the corresponding primitives.

I ran into a situation today, though, that led me to investigate more deeply.  I had a class with a nullable type field for which no default value was explicitly assigned, as in this simplified example:

    Class MyClass
    {
         public int? I { get; set; }
    }

I needed to determine whether the field had been assigned a value other than the default value.  That led me to ask the question: What is the default value for an unassigned nullable int? instance variable in C#?  Is it null, like for reference types?  Or is it 0, like for int?

Surprisingly, searching Google didn’t immediately lead to a clear answer; nor did there seem to be a question on StackOverflow asking for this particular information.  The MSDN article Nullable Types (which was the first Google hit for terms C# nullable default value) didn’t have a clear answer.

It was the work of just a couple of minutes to write and run a bit of code to test the behavior using LINQPad.  The answer: The default value of a C# int? is null.

So int? defaults to null.  But why?

I was curious: Why is the default value null, not 0?  So I did some investigating, and ended up learning the following:

  • int? is syntactic sugar for the type Nullable<T> (where T is int), a struct.  (Per that Nullable Types MSDN article.)
  • The Nullable<T> type has a bool HasValue member, which when false, makes the Nullable<T> instance "act like" a null value.  In particular, the Nullable<T>.Equals method  is overridden to return true when a Nullable<T> with HasValue==false is compared with an actual null value.
  • From the C# Language Specification 11.3.4, a struct instance's initial default value is all of that struct's value type fields set to their default value, and all of that struct's reference type fields set to null. 
  • The default value of a bool variable in C# is false (reference). 

Therefore, the HasValue property of a default Nullable<T> instance is false; which in turn makes that Nullable<T> instance itself act like null.

Since there was no readily-available information on this topic in StackOverflow, I went ahead and posted it there as a new question and answer.

A null int? just simulates an actual null value

An interesting aspect of an int? merely “simulating” a null value, rather than actually being one, is that you can call methods on a null-value int? without a NullReferenceException occurring!  For example, this code using a null String throws a NullReferenceException:

    String s = null;
    s.GetHashCode(); // Throws NullReferenceException

The same code, though, used with a null Nullable<T>, does not throw an exception:

    int? n = null;
    n.GetHashCode(); // Returns 0

Friday, December 05, 2014

Quick email tips!

Here are a couple brief email tips that are top of mind:

Set Recipients Last!  When writing a long email message, leave the To (Recipients) field BLANK until last, so that if you inadvertently hit Send before the message is actually finished and ready to go, it won't go anywhere!

Attach Attachments First!  As soon as you type the words “see attachment” or “please find attached” into the message body, stop typing, and add the actual attachment to the email!  If you assume you’ll remember to add the attachment after you’ve finished typing up the message body, sometimes you won’t remember!