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
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
.
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?