ISNULL vs. COALESCE

SQL Server has two coalescence functions, ISNULL and COALESCE. Ostensibly, the difference is quite trivial, 2 parameters vs. 2 or more. However, there's a subtle difference in behavior that has very specific consequences for code generated for LINQ to SQL and Entity Framework entities.

It pertains to the following excerpt from Books Online's description of COALESCE:

ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

I admit that it took me a while to understand what it meant. It refers to the nullability property of the resulting value's type. What that means for LINQ to SQL and Entity Framework entities is that the choice of ISNULL vs. COALESCE comes down to what data types and validation metadata you want generated for those entities' properties.

Demonstration

I'll start with a simple table that has two nullable columns.

CREATE TABLE [dbo].[Things]

(

   [Int32] [int] NULL,

   [String] [varchar] (10) NULL

);

I selected the [int] and [varchar] types because the associated .NET types are a value type and a reference type, respectively, so we can see the results of ISNULL and COALESCE on each.

Now, I'll create a view on this table, using both ISNULL and COALESCE on both columns.

CREATE VIEW [dbo].[ViewOfThings]

AS

SELECT

   ISNULL([Int32], 0) AS [RequiredInt32],

   COALESCE([Int32], 0) AS [OptionalInt32],

   ISNULL([String], '') AS [RequiredString],

   COALESCE([String], '') AS [OptionalString]

FROM [dbo].[Things];

And finally, when I add the view to a LINQ to SQL diagram, it generates the following properties (abbreviated for clarity):

[Column(Storage="_RequiredInt32", DbType="Int NOT NULL")]

public int RequiredInt32

{

   // ...

}

[Column(Storage="_OptionalInt32", DbType="Int")]

public System.Nullable<int> OptionalInt32

{

   // ...

}

[Column(Storage="_RequiredString", DbType="VarChar(10) NOT NULL", CanBeNull=false)]

public string RequiredString

{

   // ...

}

[Column(Storage="_OptionalString", DbType="VarChar(10)")]

public string OptionalString

{

   // ...

}

RequiredInt32 is an int while OptionalInt32 is a Nullable<int> (int?). RequiredString has CanBeNull=false specified on the Column attribute, while OptionalString does not. Selecting the right coalescence function ensures that the generated code has the correct types and validation metadata.

The same applies for result sets from stored procedures and table-valued functions.

Generic Parsing, Part 4: Smarter Parsing

This is Part 4 of a series on generic parsing. Here are parts 1, 2, and 3.

It's most efficient to call Parse and TryParse statically, but that requires knowledge of the type at compile time. The power of Parse<T> and TryParse<T> lies in the ability to call them with a type parameter. That is, in our own generic methods (including those that inherit their type parameters from the declaring class), we can receive any type parameter and, as long as that type implements Parse or TryParse, parse a string as that type.

However, Parse<T> and TryParse<T> require that parsers be declared by the type being parsed and not in a base class so that the fully derived type can be returned. As a result, they cannot directly parse nullable value types or enums. For example, the following won't compile:

int? value = null;

 

int.TryParse("1", out value);   // Type mismatch on output parameter

 

int?.TryParse("1", out value);  // No such method implemented on int?

Unfortunately, Parse<T> and TryParse<T> not only inherit this drawback, but defer revealing this fact until run time. The following will compile, but it'll throw an exception:

int? value;

"1".TryParse(out value);        // Throws an exception

Instead, a temporary variable is necessary, like so:

int? value = null;

int tempValue;

if ("1".TryParse(out tempValue))

   value = tempValue;

You could implement your own wrapper around this pattern for each type and call SetTryParseMethod. However, this could become tedious and yields a lot of seemingly unncessary code. Even if you implemented a single generic method to do this, you would still have to call SetTryParseMethod for each nullable value type you need to support.

Since I believe in not deviating from the expected behavior of a well-established pattern, especially when I'm trying to promote that pattern, I chose to let Parse<T> and TryParse<T> continue to inherit the aforementioned drawback. Instead, I wrote another set of methods, SmartParse<T>, SmartTryParse<T>, SmartParseDefault<T>, and their non-generic counterparts.

Handling Nullable Value Types

Since Nullable<T> is a wrapper around T, nullable value types can't implement parsers.

If the type is a nullable value type, SmartTryParse gets the underlying type and passes it to TryParse. Otherwise, it passes the original type to TryParse.

Type underlyingType = Nullable.GetUnderlyingType(type);

if (underlyingType != null)

   type = underlyingType;

 

return TryParse(s, type, out result);

Now, we can do what we originally set out to do:

int? value = null;

"1".SmartTryParse(out value);

This works without any special handling on our part by virtue of how the .NET Framework boxes and unboxes nullable value types and the fact that the value type is unwrapped in the non-generic versions of these functions, where the value is passed and returned as object.

Handling Enums

The parsing functionality for enums is provided by their common base class, System.Enum, because enums can't declare any members other than public constant fields. Even ILASM prevents methods from being added to enums. Therefore, enums can't implement parsers.

Again, defining parsers for each enum type would be tedious. That's why SmartParse<T> and SmartTryParse<T> handle enums as a special case. After unwrapping the type from Nullable<T> (if necessary) and discovering that the type is an enum, they follow the appropriate pattern for the method being called. Parsing an output format from the query string and falling back on a default if an invalid value is specified is a one-liner:

OutputFormat format = Request.QueryString["format"].SmartParseDefault(OutputFormat.Xml);

Wrapping It Up

For the strictest adherance to the Parse/TryParse implementations, use Parse<T>, TryParse<T>, and ParseDefault<T>. But for a more liberal interpretation of nullable types and enums (one that will actually work!), use SmartParse<T>, SmartTryParse<T>, and SmartParseDefault<T>. The whole suite can be found in the attached project.

That does it for this series on generic parsing. I hope it's been helpful.

Attachment: GenericParsing.zip

Generic Parsing, Part 3: ParseDefault<T>

This is Part 3 of a series on generic parsing. Here are parts 1 and 2.

One of the more common things to do with TryParse is to assign a default value if parsing fails. Since TryParse is required to assign a value to the output parameter, it first assigns a value of its own choosing, usually default(T), which may not always be the desired default value. Testing for default(T) doesn't advise failure because it could be a legitimately parsed value. Often, we find ourselves writing the following:

int i;

if (!int.TryParse(s, out i))

   i = defaultValue;

Having to repeatedly write these 3 lines can become cumbersome so sometimes we end up writing a few wrapper methods for the types we need. Of course, writing a similar wrapper around TryParse<T> is pretty easy. This is what I've done with ParseDefault<T> and ParseDefault.

Like Parse<T> and TryParse<T>, ParseDefault<T> has a non-generic counterpart that does the heavy lifting. Since ParseDefault is also public, it needs to verify that the default value is compatible with the type being parsed, so it uses the CheckDefault method to do that.

CheckDefault handles things differently depending on the type being parsed. For reference types, it makes sure that the default value is null or is type-compatible with the type being parsed. For nullable value types, it makes sure that the default is null or is of the type being parsed (nullable or not). For non-nullable value types, it makes sure that the types are the same.

Once we know the default is acceptable, we can rewrite the earlier code to use the non-generic TryParse:

public static object ParseDefault(this string s, Type type, object defaultValue)

{

   if (type == null)

      throw new ArgumentNullException("type");

 

   CheckDefault(type, defaultValue);

 

   object result = null;

 

   if (!TryParse(s, type, out result))

      return defaultValue;

 

   return result;

}

 

public static T ParseDefault<T>(this string s, T defaultValue)

{

   return (T)ParseDefault(s, typeof(T), defaultValue);

}

In combination with methods to get values from configuration, a query string, or whatever else, this simplifies a lot of TryParse usages scenarios.

Be sure to check out part 4.

Attachment: GenericParsing.zip

Generic Parsing, Part 2: Parse<T>

This is part 2 of a series on generic parsing. Here is part 1.

Having tackled the task of developing a generic TryParse method, it makes sense to also implement a generic Parse method, since TryParse rarely exists alone.

The infrastructure is mostly the same. The key difference, besides the method names, is that HasParseSignature and FindParseMethod check for a different signature:

static T Parse(string s);

GetParseMethod and SetParseMethod are essentially the same as their TryParse counterparts.

Not having to deal with an output parameter, the Parse methods are a little easier to implement:

public static object Parse(this string s, Type type)

{

   MethodInfo method = GetParseMethod(type);

   if (method == null)

      throw new Exception(...);

 

   return method.Invoke(null, new object[] { s });

}

 

public static T Parse<T>(this string s)

{

   return (T)Parse(s, typeof(T));

}

Be sure to check out parts 3 and 4.

Attachment: GenericParsing.zip

Generic Parsing, Part 1: TryParse<T>

One of the benefits of generics is that it doesn't have to matter what type you're working with as long as it meets the criteria of the operation.

So what if you're parsing input in a generic method and you need to be able to do a TryParse-like operation on any type for which the method is called? With a type converter, a very small number of types is supported, mostly scalar types. Using a little reflection, the code in this article makes it possible to work with any type for which a TryParse method is available.

The first time I went down this road, I fell into it somewhat by accident. Fortunately, I realized that I didn't need it and was able to abandon the mess. The second time, I was aware that I was about to start down that road again and it was clear that it was necessary. The result is a flexible method with a granular infrastructure.

Note: I use the term "parser" to refer to a TryParse method for a specific type, thus avoiding confusion with the TryParse methods being developed here.

Prerequisites

I want to start by stating the design goals of this development effort:

1. To support any type as long as a parser with the required signature is available.
2. To defer to the type whenever possible.
3. To allow the consumer to provide parsing functionality for types they can't modify.
4. To prevent overriding type-provided functionality (if different behavior is used, it should be apparent at the call site).
5. To minimize reflection.

There are actually two methods being developed here; one is a type-safe, generic method, TryParse<T>, and the other is the non-generic version, TryParse, which can be useful in its own right when code is dealing with a Type instance instead of a generic type parameter.

The first thing to do is formalize the required method signature I mentioned in the design goals:

static bool TryParse(string s, out T result);

The method must be static, it must return a bool, it must be named "TryParse", it must take a string as its first parameter, and it must take a reference to the type it parses as its second parameter. What can't be readily concluded from this signature (because it's shown here as T) is that it must not have any unresolved generic parameters. In CLR terms, it must be a closed constructed method. We'll need to test all these aspects before we can use a parser.

HasTryParseSignature encapsulates that logic. One thing to point out is that the parser only has to be public in a certain situation. I'll explain why later.

Discovery

In order to prevent multiple lookups of the same method, we need to keep a dictionary that maps Type to MethodInfo. If no parser is found, we still store null to prevent duplicate searches, regardless of success or failure.

GetTryParseMethod returns a parser from the cache, if available. Otherwise, it attempts to find a parser and cache it, then return the result. Finding an appropriate parser implemented by a type is fairly simple:

BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Static;

Type[] parameterTypes = new Type[] { typeof(string), type.MakeByRefType() };

 

MethodInfo method = type.GetMethod("TryParse", bindingFlags, null, parameterTypes, null);

The key is the call to type.MakeByTypeRef(); just using type won't work.

FindTryParseMethod does the above reflection and returns the result or null. The only thing that the above code doesn't do is guarantee that the method's return type is bool so FindTryParseMethod tests that separately.

The above code uses BindingFlags.Public to find a public TryParse method on the type. This is because we need to be sure that the type's author intended it to be used by consumers. But when providing a consumer-implemented parser, visibility doesn't matter.

SetTryParseMethod allows the consumer to provide a parser for a type over which the consumer has no control. For example, Guid doesn't expose a parser, but it wouldn't be difficult to provide one (the attached code automatically provides it).

SetTryParseMethod ensures that a type-provided parser is unavailable (see design goal #4), confirms that the consumer-provided parser has the correct signature, and then associates it with the type it parses in the cache. There are two versions available, one that takes a MethodInfo instance, and one that takes an instance of the following delegate type:

public delegate bool TryParseDelegate<T>(string s, out T result);

Putting it all together

Finally, we need to be able to call a TryParse method on any type. Earlier I mentioned that two TryParse methods are being implemented here. We'll start with the non-generic version because it's the workhorse of the two.

public static bool TryParse(this string s, Type type, out object result)

{

   result = null;

 

   MethodInfo method = GetTryParseMethod(type);

   if (method == null)

      throw new Exception(...);

 

   object[] parameters = new object[] { s, null };

   bool success = (bool)method.Invoke(null, parameters);

   if (success)

      result = parameters[1];

   return success;

}

This allows us to use any type, specified as a parameter. The result is an object, but the value being assigned upon success is of the specified type (by virtue of the fact that we validated the method signature and associated it with the type).

Now, implementing the generic version is rather trivial:

public static bool TryParse<T>(this string s, out T result)

{

   result = default(T);

   object tempResult;

   bool success = TryParse(s, typeof(T), out tempResult);

   if (success)

      result = (T)tempResult;

   return success;

}

In either case, we don't check s because a type may have a special meaning for a null string. It's best to let the TryParse method determine what to do with it.

Final notes

An exception is thrown by TryParse to indicate that no parser is available for a type. This kind of exception will most likely be detected during development and testing. If you wanted to return false instead, you could do it but you would run the risk of failures slipping under the radar just because someone forgot to implement a parser.

Finally, I implemented these TryParse methods as extension methods on string because:

1. Having to qualify the TryParse methods with the class name gets cumbersome.
2. Any string could potentially be parsed as some other data type, so it makes sense to see extension methods in Intellisense.

Be sure to check out parts 2, 3, and 4.

Attachment: GenericParsing.zip

Introduction

Greetings, reader! My name is Benn and I'm a software developer with Kiefer Consulting.

This blog will focus on two subjects I find particularly interesting: C# and .NET Reflection, and T-SQL and the system catalog views. Not to limit myself too much, I'll cover anything that I find useful in my work as it comes up.

I hope that you find something here that is useful, inspires you, saves you time, makes your code a little easier to write/maintain, or any combination of the above. Enjoy!