sqlite and sqlserver with dapper

dapper sql-server system.data.sqlite unit-testing

Question

I'm using dapper with SQL Server, but using Sqlite for some in memory tests for my unit tests.

I have a class as follows

public class Test
{
  public int ID {get;set;}
  public string Name 
}

If I query using dapper with a SQL Server connection, it works fine.

If I query using dapper with a SQLite connection, I get a type cast error. Basically I need to change the type of ID to be a long, rather than int.

I don't want to change my schema in SQL Server, so I'm wondering if there is a workaround to the scenario. I've tried creating and ID class with implicit casts to long/int, but this also fails, and I'm out of ideas!

Popular Answer

This revolves around the fact that Dapper uses the GetValue method on the IDataRecord interface. The concrete class in the System.Data.SqlLite namespace probably map values differently than that of System.Data.SqlClient.

As @FelicePollano pointed out try changing the structure of the SQLLite table and see if that causes the concrete class in System.Data.SqlLite to map it differently.

Another option is to roll your own mapping using the FastExpando object Dapper returns and force it to convert the value to a Int.

IEnumerable<dynamic> results = Conn.Query("Select ID from SomeTable");

var testValue = new Test
{
    ID = Convert.ToInt32(Results[0].ID.ToString())
};

Not really a big fan of it because it could require a lot of code changes, but it can work.

Please note: That example code can throw a exception if the ID column is not an integer. You can use TryParse, but for the sake of brevity I didn't include it.




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