Get method on mapped class returns a Linq error - Sequence contains more than one element

[kitmax]

I have a Generic GetItem method

public static T GetItem(string server, int Id) where T : class { using (var conn = GlobalConnection.GetServerDBConnection(server, db)) { return conn.Get(Id); } }

Since updating to the latest version, 1.7, I am getting the following Linq error

Sequence contains more than one element - at System.Linq.ThrowHelper.ThrowMoreThanOneElementException() at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable1 source) at DapperExtensions.DapperImplementor.InternalGet[T](IDbConnection connection, Object id, IDbTransaction transaction, Nullable1 commandTimeout, IList1 colsToSelect, IList1 includedProperties) 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, Nullable1 commandTimeout, IList1 includedProperties) 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) at Cardinal.Global.DataAccess.GenericDb.GetItem[T](String server, Int32 Id)

This uses a mapped class but has been working ok on previous versions

public class ApplicationMapping : ClassMapper { public ApplicationMapping() { Table("gbl_Application"); Map(f => f.ApplicationId).Column("id").Key(KeyType.Identity); Map(f => f.Name).Column("name"); Map(f => f.Description).Column("description"); Map(f => f.Prefix).Column("_prefix"); } }

[valfrid-ly]

Could you send an example how to reproduce the error?

[kitmax]

@valfrid-ly valfrid-ly I created a sql table similar to a legacy table I have where I have to use the Mapping class (using the Person class in the examples)

This reproduces the same issue I am having in my own production envrionment. Insert works but causes a System.ArgumentException: Object of type 'System.Int64' cannot be converted to type 'System.Int32' when returning the Id to an int GetItem causes System.InvalidOperationException: Sequence contains more than one element GetList works as expected. Update works as expected Delete works as expected

CREATE TABLE dbo.Person (
  id int IDENTITY NOT NULL,
  first_name varchar(50) DEFAULT '' NOT NULL,
  last_name varchar(50) DEFAULT '' NOT NULL,
  active bit DEFAULT 1 NOT NULL,
  date_created datetime DEFAULT '01/01/1900' NOT NULL,
  PRIMARY KEY CLUSTERED (id)
)
GO

namespace Test
{
    using DapperExtensions;
    using DapperExtensions.Mapper;
    using DapperExtensions.Predicate;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;

    public class Person
    {
        public Person()
        {
            DateCreated = DateTime.Now.Date;
        }

        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public bool Active { get; set; }
        public DateTime DateCreated { get; set; }
    }

    public class PersonMapping : ClassMapper<Person>
    {
        public PersonMapping()
        {
            Table("tbl_person");
            Map(f => f.Id).Column("id").Key(KeyType.Identity);
            Map(f => f.FirstName).Column("first_name");
            Map(f => f.LastName).Column("last_name");
            Map(f => f.Active).Column("active");
            Map(f => f.DateCreated).Column("date_created");
        }
    }

    [TestClass]
    public class PersonTest
    {
        private static string _connectionString = "my connection";

        [TestMethod]
        public void Insert()
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                Person person = new Person { FirstName = "Foo", LastName = "Bar" };
                int id = cn.Insert(person);
                cn.Close();
            }
        }

        [TestMethod]
        public void GetItemById()
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                int personId = 1;
                Person person = cn.Get<Person>(personId);
                cn.Close();
            }
        }

        [TestMethod]
        public void GetList()
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                cn.Open();
                var predicate = Predicates.Field<Person>(f => f.Active, Operator.Eq, true);
                IEnumerable<Person> list = cn.GetList<Person>(predicate);
                cn.Close();
            }
        }

        [TestMethod]
        public void Update()
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                cn.Open();

                // Can't use Get
                var predicate = Predicates.Field<Person>(f => f.Active, Operator.Eq, true);
                var list = cn.GetList<Person>(predicate).ToList();

                var person = list[0];
                person.LastName = "Baz";
                cn.Update(person);
                cn.Close();
            }
        }

        [TestMethod]
        public void Delete()
        {
            using (SqlConnection cn = new SqlConnection(_connectionString))
            {
                cn.Open();

                // Can't use Get
                var predicate = Predicates.Field<Person>(f => f.Active, Operator.Eq, true);
                var list = cn.GetList<Person>(predicate).ToList();

                var person = list[1];
                cn.Delete(person);
                cn.Close();
            }
        }
    }
}

[valfrid-ly]

Thanks, I'm going to check it out

[barry-tormey-imprivata]

Ran into this myself and it looks like the generated SQL in 1.7.0 is incorrect. I'm not sure exactly what is causing the problem, but the issue is apparent.

In 1.6.3 Command:

SELECT "first_name" as "firstname", "last_name" as "lastname", "active" as "active", "date_created" as "datecreated", "id" as "id" FROM "Person" WHERE ("id" = @Id_0)

Parameters:

Id_0=1

In 1.7.0 Command:

SELECT "y_1"."first_name" as "c_0", "y_1"."last_name" as "c_1", "y_1"."active" as "c_2", "y_1"."date_created" as "c_3", "y_1"."id" as "c_4" FROM "Person" "y_1" WHERE (1=1)

Parameters are empty

Obviously in 1.7.0, the WHERE (1=1) is causing the SELECT statement to return all of the rows in the table. It seems as if the primary key is being ignored. I have confirmed that the Id property in the MemberMap does have a KeyType of KeyType.Identity.

[valfrid-ly]

Did you take a look into this test??? UsingPredicate_ReturnsMatching

It's exactly the same scenario and the test does not fail.

I couldn't see any difference between tests.

If there is not additional information, please, check again with next version because all tests are running ok

[barry-tormey-imprivata]

I can confirm this is still happening in 1.7.0, the above SQL is what is generated. Potentially the only thing to note here that is different than the test is that it is using the Postgres dialect. I'm not familar with the codebase enough to know if that has impact on the SQL generation. I'll attempt to create a minimal replication in a public repo this week.

[kitmax]

I tried again, this time with a normal table that matches the class, so NOT using the ClassMapper. Insert worked with an int64 error still, GetItem worked as it should therefore the error seems to be related to the ClassMapper.

[valfrid-ly]

@barry-tormey-imprivata , unit tests for PostgreSQL are being created. It's something we are missing to validate things that are particular to this database.

@kitmax , I'll try to use the code you sent again and try to find the issue

[barry-tormey-imprivata]

Created a simple set of integration tests that validate the issue using Docker.NET to invoke calls against a PostgreSQL database. I created tests using a custom class mapper and the default (AutoClassMapper<T>), both seem to produce the same error on Get and GetAsync.

https://github.com/barry-tormey-imprivata/dapper-extensions-postgres-test

[andrenpt]

Ran into this myself and it looks like the generated SQL in 1.7.0 is incorrect. I'm not sure exactly what is causing the problem, but the issue is apparent.

In 1.6.3 Command:

SELECT "first_name" as "firstname", "last_name" as "lastname", "active" as "active", "date_created" as "datecreated", "id" as "id" FROM "Person" WHERE ("id" = @Id_0)

Parameters:

Id_0=1

In 1.7.0 Command:

SELECT "y_1"."first_name" as "c_0", "y_1"."last_name" as "c_1", "y_1"."active" as "c_2", "y_1"."date_created" as "c_3", "y_1"."id" as "c_4" FROM "Person" "y_1" WHERE (1=1)

Parameters are empty

Obviously in 1.7.0, the WHERE (1=1) is causing the SELECT statement to return all of the rows in the table. It seems as if the primary key is being ignored. I have confirmed that the Id property in the MemberMap does have a KeyType of KeyType.Identity.

I am experiencing the exact same problem.

Changed to 1.6.3 and it works as expected. (SQL DB)

[PeterZhuJiaZhi]

there is bug in 1.7.0 i view the source code in ILSpy, it's maybe cause by the param keyPredicate,(default is false)

protected virtual void GetMapAndPredicate<T>(object predicateValue, out IClassMapper classMapper, out IPredicate wherePredicate, bool keyPredicate = false) where T : class
    `{`
        classMapper = SqlGenerator.Configuration.GetMap<T>();
        wherePredicate = (keyPredicate ? GetKeyPredicate(classMapper, predicateValue) : GetPredicate(classMapper, predicateValue));
    }

[PeterZhuJiaZhi]

about the con.Upate method, in previous version, i can use like follow con.Update<T>(entity) but in 1.7.0, it's must be like follow con.Update<T>(entity, null, null, true) it's ungly

protected bool InternalUpdate<T>(IDbConnection connection, T entity, IClassMapper classMap, IPredicate predicate, IDbTransaction transaction, IList<IProjection> cols, int? commandTimeout, bool ignoreAllKeyProperties = false) where T : class { Dictionary<string, object> parameters = new Dictionary<string, object>(); string text = SqlGenerator.Update(classMap, predicate, parameters, ignoreAllKeyProperties, cols); DynamicParameters dynamicParameters = GetDynamicParameters(classMap, entity, useColumnAlias: true); dynamicParameters.AddDynamicParams(GetDynamicParameters(parameters)); LastExecutedCommand = text; return connection.Execute(text, dynamicParameters, transaction, commandTimeout, CommandType.Text) > 0; } the param ignoreAllKeyProperties is better defalut for true

[KF200518]

Ran into this myself and it looks like the generated SQL in 1.7.0 is incorrect. I'm not sure exactly what is causing the problem, but the issue is apparent.

In 1.6.3 Command:

SELECT "first_name" as "firstname", "last_name" as "lastname", "active" as "active", "date_created" as "datecreated", "id" as "id" FROM "Person" WHERE ("id" = @Id_0)

Parameters:

Id_0=1

In 1.7.0 Command:

SELECT "y_1"."first_name" as "c_0", "y_1"."last_name" as "c_1", "y_1"."active" as "c_2", "y_1"."date_created" as "c_3", "y_1"."id" as "c_4" FROM "Person" "y_1" WHERE (1=1)

Parameters are empty

Obviously in 1.7.0, the WHERE (1=1) is causing the SELECT statement to return all of the rows in the table. It seems as if the primary key is being ignored. I have confirmed that the Id property in the MemberMap does have a KeyType of KeyType.Identity.

+1 Also experiencing the same exact problem. The Where (1=1) is returning the entire table and causing this exception. Reverted to V1.6.3 and it works as expected.

[thefat32]

There is something wrong with GetKeyPredicate

As @PeterZhuJiaZhi mentioned keyPredicate parameter in GetMapAndPredicate method is always false, but forcing keyPredicate to true does not fix the problem, it crashes when trying to get the value inside GetKeyPredicate

If you don't want to downgrade, passing a predicate to GetAsync method works. Example:

        var predicate = Predicates.Field<Device>(f => f.DeviceId, Operator.Eq, request.DeviceId);
        var device = await _connection.GetAsync<Device>(predicate);

[sparkwong3]

The issue seems still exists in v1.7.0;

@valfrid-ly, kindly ask when the issue can be resolved? Thanks.

[valfrid-ly]

I'm still working on many things. I still have no date, but I'm trying to release a 1.7.1 with many bug fixes