Dapper micro ORM, database agnostic and MySql Guid type

c# dapper mysql orm sqlite

Question

I am experimenting Dapper on a pet project. I am using SQLite to run all the tests and MySql for "production". However I am not sure how to best use Dapper to handle database agnostic situation.

The particular problem I am having is with MySql which doesn't support Guid type for primary key therefore I am using varchar(40) as the type (SQLite supports unique identifier which is a guid). So here comes the problem if I have a generic repository as below I would get into trouble when trying to select from MySql database. Because the type of Id property is Guid and Dapper will throw "Error parsing column 10" because varchar type doesn't match guid type.

If I change the Id property from guid to int then the raw sql inside the GetById would be even trickier which I am not sure how to even write. It will be something like, 1. start transaction, 2. insert, 3. select last inserted id and return it. So am I going to go with if database type is mysql, then use last_insert_id, or if it's sqlite then use last_insert_rowid? Because the raw sql syntax would be quite different from database to database...

public IEnumerable<T> GetById(Guid id) //convention: Id is always of type Guid.
{
  return UnitOfWork.DbConnection.Query<T>(
      string.Format(
        "select * from {0} where Id = @Id", typeof (T).Name), new {Id = id});
}

Other examples would be to limit number of rows returned (especially for paging) and so on. So how am I going to write database agnostic raw sql queries with dapper? And maybe in my situation Dapper isn't suitable? Perhaps I should use the same old NHibernate here. Any suggestions? Am I doing it wrong? Thanks!

Popular Answer

Try to use CHAR(36) as the datatype for the primary key in MySQL, this get transalted to Guid by the MySQl Connector - I am using MySQL connector version 6.3.4. Works with Dapper as well.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why