DbConnection.Get<T>([id]) does no longer work with simple data types

[PlumBum91]

Hi there, currently the example from the intro

using (SqlConnection cn = new SqlConnection(_connectionString)) { cn.Open(); int personId = 1; Person person = cn.Get<Person>(personId); cn.Close(); }

is not working anymore. This is because the the method call GetIdPredicate is no longer in use. This results in an an exception within InternalGet. The method makes use of the Lambda SingleOrDefault which failes because the queried results are more or less ALL rows the database table contains.

It would be nice if this could be fixed :-)

[valfrid-ly]

Could you please send me the stack trace of the error?

[PlumBum91]

Sorry for the delay. Here is the complete stack trace:

System.InvalidOperationException: Sequence contains more than one element at System.Linq.ThrowHelper.ThrowMoreThanOneElementException() at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable'1 source) at DapperExtensions.DapperImplementor.InternalGet[T](IDbConnection connection, Object id, IDbTransaction transaction, Nullable'1 commandTimeout, IList'1 colsToSelect, IList'1 includedProperties) in /home/user/Entwicklung/Dapper-Extensions-master/DapperExtensions/DapperImplementor.cs:line 806 at System.Dynamic.UpdateDelegates.UpdateAndExecute7[T0,T1,T2,T3,T4,T5,T6,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4, T5 arg5, T6 arg6) at DapperExtensions.DapperImplementor.Get[T](IDbConnection connection, Object id, IDbTransaction transaction, Nullable'1 commandTimeout, IList'1 includedProperties) in /home/user/Entwicklung/Dapper-Extensions-master/DapperExtensions/DapperImplementor.cs:line 61 at System.Dynamic.UpdateDelegates.UpdateAndExecute5[T0,T1,T2,T3,T4,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4) at DapperExtensions.DapperExtensions.Get[T](IDbConnection connection, Object id, IDbTransaction transaction, Nullable'1 commandTimeout) in /home/user/Entwicklung/Dapper-Extensions-master/DapperExtensions/DapperExtensions.cs:line 140 at Custom.Tests.UnitTest1.TestMethod1() in /home/user/Entwicklung/Dapper-Extensions-master/Custom/Custom.Tests/UnitTest1.cs:line 23

[origamirobot]

I'm running into this too. Is there any work around for this?

[PlumBum91]

@origamirobot

I have built myself a DbConnection Extension class as follows:

public static class DbConnectionExtensions {

        /// <summary>
        /// Executes a query for the specified id, returning the data typed as per T
        /// </summary>
        public static T GetId<T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int? commandTimeout = null) where T : class {
            var classMapper = DapperExtensions.DapperExtensions.GetMap<T>();
            var idPredicate = GetNewIdPredicate(classMapper, id);
            return DapperExtensions.DapperExtensions.Get<T>(connection, idPredicate, transaction, commandTimeout);
        }

        public static Task<T> GetIdAsync<T>(this IDbConnection connection,
                                            dynamic id,
                                            IDbTransaction transaction = null,
                                            int? commandTimeout = null,
                                            bool buffered = false) where T : class {
            return Task.FromResult<T>(GetId<T>(connection, id, transaction, commandTimeout));
        }

        private static IPredicate GetNewIdPredicate(IClassMapper classMap, object id) {
            var isSimpleType = ReflectionHelper.IsSimpleType(id.GetType());
            var keys = classMap.Properties.Where(p => p.KeyType != KeyType.NotAKey);
            IDictionary<string, Func<object>> paramValues = null;
            var predicates = new List<IPredicate>();

            foreach (var key in keys) {
                var value = id;
                if (!isSimpleType) {
                    value = paramValues[key.Name];
                }

                var predicateType = typeof(FieldPredicate<>).MakeGenericType(classMap.EntityType);

                var fieldPredicate = Activator.CreateInstance(predicateType) as IFieldPredicate;
                fieldPredicate.Not = false;
                fieldPredicate.Operator = Operator.Eq;
                fieldPredicate.PropertyName = key.Name;
                fieldPredicate.Value = value;
                predicates.Add(fieldPredicate);
            }

            return ReturnPredicate(predicates);
        }

        private static IPredicate ReturnPredicate(IList<IPredicate> predicates) {
            return predicates.Count == 1
                                   ? predicates[0]
                                   : new PredicateGroup {
                                       Operator = GroupOperator.And,
                                       Predicates = predicates
                                   };
        }
    }

... but unfortunately I ran into another issue that I haven't filed yet. The other issue was that DapperExtensions are no longer thread safe since the changes for .NET5 / .NET Core. Let's say you want to use it in an Web Application where multiple requests run simultaneously there will be some problems with the internal SQL query generation. But I had no time to do some further research on that.

[valfrid-ly]

Why do you say they are not thread safe? Did you see something I'm missing?

[bgiromini]

Just another footnote I too had production code running fine for 7 years but after upgrading to the the newest release this method is broken. Will try to determine a work around.

[valfrid-ly]

From what I could notice you're relying on DefaultDialect. Depending on the database it will work but can also not work as it was not set. Usually it'll work for SqlServer and Sqlite, but it's not guaranteed.

The ideal code for this should be:

using (SqlConnection cn = new SqlConnection(_connectionString)) 
{ 
  DapperExtensions.SqlDialect = new SqlServerDialect();
  cn.Open(); 
  int personId = 1; 
  Person person = cn.Get<Person>(personId); 
  cn.Close(); 
}

[vcinquini]

It doesn't work for me too. Same error, same story. I'm using Northwind sample database

 // this works
Orders results = _context.Connection.QuerySingle<Orders>("SELECT * from Orders where OrderId = @OrdersID", new { id });
// this doesn't work
Orders results2 = _context.Connection.Get<Orders>(id); //, transaction: _context?.Transaction);