How do I get a nullable int[] with dapper?

c# dapper sql-server-2008

Question

If I want to get a bunch of rows containing a single column of int, using Dapper, and it's possible for this result set to be empty. What's the best way to use Dapper to query this data?

For Example, if I have the following method that returns what I want:

public void int[] GetInts()
{

    conn.Query<int?>("select 123 where 1=1")
        .Where(x=> x.HasValue)
        .Select(x => x.Value)
        .ToArray();
}

If I change the line to this:

conn.Query<int>("select 123 where 1=0").ToArray();

I get a casting error when there are no results.

The Stack Trace is below and the exception is just an object reference not set to instance of an object when casting (T)next:

at Dapper.SqlMapper.<QueryInternal>d__13`1.MoveNext() in .\SqlMapper.cs:line 611
 at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)   
  at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)    
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in .\SqlMapper.cs:line 539

Accepted Answer

My problem turned out to be that I was doing a LEFT JOIN in code, but while I was trying to reproduce the error I was using an INNER JOIN, so, I couldn't reproduce the same behavior. With the LEFT JOIN, a single row with the value of NULL was returned which is why I was getting a casting error between NULL and int.


Popular Answer

Well, the Where and Select don't make any sense if it's an int. Pretty sure you want to remove them.

conn.Query<int>("select 123 where 1=0")
    .ToArray();

Do you have any problems when you do that?




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why