Dapper.SimpleCRUD Insert / Update / Get fails with message "Entity must have at least one [Key] property"

Question

I'm a new baby in Dapper. Trying to incorporate CRUD operations with Dapper and Dapper.SimpleCRUD lib. Here is the sample code...
My Data Model Looks like

Class Product
{
  public string prodId {get;set;}
  public string prodName {get;set;}
  public string Location {get;set;}
}

Dapper Implementation - Insert

public void Insert(Product item)
{
    using(var con = GetConnection())
    {
      con.Insert(item);
    }
}

Since the ProdId in the Db is an Identity Column it fails. How does it indicate ProdId is an Identity Column in DB?

Dapper Implementation - Get

public IEnumerable<Product> GetAll()
{
        IEnumerable<Product> item = null;
        using (var con = GetConnection())
        {
            item = con.GetList<Product>();
        }
        return item;
}

It gives an exception:

"Entity must have at least one [Key] property"!

Latitudinarian

Answer 1

This is happening since you are using a Dapper Extension, which has implemented the Insert CRUD extension method. Ideally this can be achieved using simple

con.Execute in the Dapper, but since you want to pass an object and create an insert query automatically by the extension, you need to help it understand, which is the Primary Key for the given product entity, following modification shall help:

[Key]
public string prodId {get;set;}

where Key attribute shall be either implemented in Dapper Extension or the Component Model.

Alternatively you may rename prodId to Id, which will automatically make it the key. Also check the following link, where you can create a separate mapper for the entity, thus defining the key, whatever works in your case

Statistics

I tried adding [Key] attribute but it is not able to locate from which lib it has to come from. Tried with Using Dapper; and using System.ComponentModel.DataAnnotations.Schema; should i import any lib to do so? – Latitudinarian

Need to check the DapperExtension implementation from Github, sure shot workaround would be either making the Key column as Id, which can be mapped to a different name db column using Dapper - Fluent Map - github.com/henkmollema/Dapper-FluentMap, or i prefer to use connection.Execute, which works flawless – Statistics

Did you check the link in the answer, where you can create explicit mapping for the Key, that would be simpler, choose one of the option to make it work – Statistics

Strangely github.com/ericdc1/Dapper.SimpleCRUD dodumaetation suggest the same Key attribute. Also check the options to the SimpleCRUD, Rainbow and Contrib - https://mcmap.net/q/259631/-dapper-rainbow-vs-dapper-contrib – Statistics

I did go with explicit mapper "Dapper Extensions" and it resolved the issue. Thanks for your note !! – Latitudinarian

Great, if my reply and follow up comments have helped in resolving the issue, then can you please mark this as an answer – Statistics

with the Dapper.SimpleCRUD you have to mark the field with the [key] attribute. But that is only if the db creates the key for you. if you want to manage the key then you need to add the [Required] attribute. the Dapper.SimpleCRUD ignores the property marked with the [key] attribute unless it has the [Required] attribute example:: [Table("Contacts")] public class Contacts : IContacts { [Required] [Key] public Guid objid { get; set; } ... – Dimple

I used ExplicitKey and including using Dapper.COntrib.Extensions then it worked. This is the easiest solution. I think since you used 'Key' attrr and used DataAnnotation using statement it didn't work. Need to use Contrib using statement – Octad

Answer 2

Connecting to SQL Server 2016, I had this error with both Dapper.Contrib & Dapper.SimpleCRUD when I forgot to attach the Primary Key to the Id column of the table.

Primary Key added to the table, project rebuilt & published to clear cache and all is good with both [Key] & [ExplicitKey] ( the latter in DapperContrib).

Wanda