How do I use Dapper.Contrib with FluentMap to update an entity with an identity column?

Question

I have a class called Business, which has a property Id that is a identity field in MS SQL Server database. When I do an INSERT, it works properly. If I do the following:

_db.Update(business);

I get an error:

invalid column name 'id" the column in the DB is BusinessId

I have mapped it to Id as below:

public class BusinessMap : EntityMap<Business>
    {
        public BusinessMap()
        {
            Map(x => x.Id).ToColumn("BusinessId");                
        }
    }

Now keep in mind my INSERT works. I tried adding the Key attribute to the class

[Table("Business")]
    public class Business {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        .....
    }

My work around (which I hate, but is better than nothing):
I decided to just call a SP to do this, which sort of sucks because I was hoping dapper could handle this.

_db.Query("BusinessUpdate",
                param: new { businessId = business.Id, business.Name, business.LocatorUrl, business.Website }, commandType: CommandType.StoredProcedure);

I don't know why we are forced to conclude that every identity column in our database is named "ID", that is ludicrous in my opinion, and I don't think I need to justify my schema design, it should let me change it. Now anytime a field is added, I have to change the SP and the code to call the SP, I will use this until I found the real answer.

Answer 1

You are using Dapper.FluentMap. This tool works as mapper for Dapper; NOT for Dapper Contrib.
So while generating the query, Contrib do not know BusinessId column is mapped with Id property. It assumes that for Id property, there exist an Id column. The INSERT is working because your primary key is automatically being generated by RDBMS you are using.
To know more about mapping column/table names to properties/classes, please refer to this question.

If you are trying to update/change the ID using Dapper Contrib, then it is not supported. This is because ORMs like Dapper Contrib use ID for uniquely identifying the record while generating queries.
So when you call _db.Update(business); method, Dapper Contrib generate the query something like UPDATE <table name> SET <column list except ID, with new values> WHERE <ID column> = ?. As you can see, the ID is being used to uniquely identify the record to be updated while generating UPDATE query.
Unfortunately, I could not find reference for this. The GitHub help also does not explicitly mention it.

The alternative is to bypass the Dapper Contrib and use Dapper's Execute method directly by writing query by hand. I am not sure but Dapper.FluentMap also have CRUD query generator named Dommel; you may try one-to-one mapping that it support.

_{Although you are using Dapper Contrib, most part of this answer is also applicable to Dapper Extensions or some other query generators associated with Dapper.}

Answer 2

Ok, I got it to work. My first problem was there are several packages that add CRUD methods (Update). My code was calling Dapper.Contrib.Update and I wanted to call Update from DommelMapper.Update once I got my imports to use the correct Nuget Package.. I made progress.. Here is what worked for me

  public class BusinessMap :   DommelEntityMap<Business>
    {
        public BusinessMap()
        {
            ToTable("Business");
            Map(x => x.Id).ToColumn("BusinessId").IsKey().IsIdentity();
        }
    }


FluentMapper.Initialize(config =>
            {
                config.AddMap(new BusinessMap());
                config.ForDommel();
            });

public void UpdateBusiness(Business business)
        {
            using TransactionScope t = new TransactionScope();
            _db.Update(business);
            t.Complete();
        }

The issues is I was calling the wrong Update Extension (contrib) and not calling the Dommel Update Extension, which did not recognize my mappings

I've installed Dommel 2.3.2, Dapper.Contrib 2.0.78, Dapper.FluentMap.Dommel 2.0.0