Why DapperExtensions GetById not returning the object?

Question

I'm using DapperExtensions and the framework I'm using is .NET-Core.

I have a base repository with the following:

public abstract class TableRepository<T> : ITableRepository<T> where T : class
{
    public T GetById(int id)
    {
        using (SqlConnection sqlConnection = new SqlConnection(_dbConnection.ConnectionString))
        {
            return sqlConnection.Get<T>(id);
        }
    }

   ....
}

The ITableRepository contains multiple methods, but in this specific scenario we are interested in the GetById method:

public interface ITableRepository<T>
{
   T GetById(int id);
   ...
}

I have a ISkipRepository interface which inherits from ITableRepository and also defines the type:

public interface ISkipRepository : ITableRepository<Skip>
{
     Skip GetByName(string name);
     bool Exists(int id, string name);
}

I have an instance of ISkipRepository like so:

public class SkipRepository : TableRepository<Skip>, ISkipRepository
{
    public SkipRepository(IDbConnection dbConnection) : base(dbConnection) { }

    public Skip GetByName(string name)
    {
        string sql = @"SELECT * FROM [Skip] WHERE [Name] = @name";

        object parameters = new
        {
            name
        };

        return QuerySingle(sql, parameters, CommandType.Text);
    }

    public bool Exists(int id, string name)
    {
        var group = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
        group.Predicates.Add(Predicates.Field<Skip>(s => s.Id, Operator.Eq, id, true));
        group.Predicates.Add(Predicates.Field<Skip>(s => s.Name, Operator.Eq, name));
        return Exists(group);
    }
}

I've register this instance in my Startup:

services.AddTransient<ISkipRepository, SkipRepository>();

And calling the GetById on SkipRepository like so:

var skip = _skipRepository.GetById(skipId);

In my table I only have 3 records and as you can see I'm trying to grab a Skip by Id.

Here is my domain object:

public class Skip
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public decimal BasePrice { get; set; }
    public decimal PricePerDayAfter14Days { get; set; }
    public int Quantity { get; set; }
}

Here is a dapper mapper class for my Skip:

public sealed class SkipMapper : ClassMapper<Skip>
{
    public SkipMapper()
    {
        Schema("dbo");
        Table("Skip");
        Map(x => x.Id).Key(KeyType.Identity);
        AutoMap();
    }
}

For some reason, the GetById throws an exception indicating that it expects 1 element but has got 3 elements.

My database table has 3 records and so I started to dig deeper and run a SQL Profiler to what I found this query:

SELECT [y_1].[Id] AS [c_0], [y_1].[Name] AS [c_1], [y_1].[Description] AS [c_2], [y_1].[BasePrice] AS [c_3], [y_1].[PricePerDayAfter14Days] AS [c_4], [y_1].[Quantity] AS [c_5] FROM [Skip] [y_1]

As you can see its not added a WHERE clause and I'm struggling to understand why.

Does anyone know what I'm doing wrong?

Update:

I've removed all the layers and done this directly in my Controller:

    [HttpGet("{id}")]
    public IActionResult Index(int id)
    {
        using (SqlConnection conn = new SqlConnection("Server=localhost;Database=develop;Trusted_Connection=True;MultipleActiveResultSets=true"))
        {
            Data.Models.Skip skipModel = conn.Get<Data.Models.Skip>(1);
        }
        ...
    }

Still no luck.

Answer 1

You are mapping the primary key using [Key] attribute. This mapping does not work with Dapper Extensions; it is picking up the convention based mapping (the property named Id) currently. Just to be sure, please map explicitly like below:

public sealed class ProductMapper : ClassMapper<Product>
{
    public ProductMapper()
    {
        Schema("dbo");
        Table("Products");
        Map(x => x.Id).Key(KeyType.Guid);
        AutoMap();
    }
}

You also need to call SetMappingAssemblies at the startup of the project to apply these mappings.
Also, set the dialect at startup just after SetMappingAssemblies like below:

DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.SqlServerDialect();

Please refer to this answer to learn more about mappings in Dapper Extensions.

---

I copied your code below and executed on my system.
Dapper: 1.50.4.0.
DapperExtensions: 1.6.3.0.
Target .NET Framework: 4.6.1; NOT Core.

DapperExtensions.DapperExtensions.SetMappingAssemblies(new[] { Assembly.GetExecutingAssembly() });
DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.SqlServerDialect();

using(SqlConnection conn = new SqlConnection(connString))
{
    Skip skip = conn.Get<Skip>(1);
}

public class Skip
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public decimal BasePrice { get; set; }
    public decimal PricePerDayAfter14Days { get; set; }
    public int Quantity { get; set; }
}

public sealed class SkipMapper : ClassMapper<Skip>
{
    public SkipMapper()
    {
        Schema("dbo");
        Table("Skip");
        Map(x => x.Id).Key(KeyType.Identity);
        AutoMap();
    }
}

This generates following SQL query which is correct.

SELECT [dbo].[Skip].[Id], [dbo].[Skip].[Name], [dbo].[Skip].[Description], [dbo].[Skip].[BasePrice], [dbo].[Skip].[PricePerDayAfter14Days], [dbo].[Skip].[Quantity] FROM [dbo].[Skip] WHERE ([dbo].[Skip].[Id] = @Id_0)

I think there is something wrong in your layers or may be upper version is buggy.