Inserting an IEnumerable<T> collection with Dapper errors out with "class is not supported by Dapper."

Question

Yep, there are questions here and here about how to insert records with dapper-dot-net. However, the answers, while informative, didn't seem to point me in the right direction. Here is the situation: moving data from SqlServer to MySql. Reading the records into an IEnumerable<WTUser> is easy, but I am just not getting something on the insert. First, the 'moving records code':

//  moving data Dim session As New Session(DataProvider.MSSql, "server", _                            "database")  Dim resources As List(Of WTUser) = session.QueryReader(Of WTUser)("select * from tbl_resource")   session = New Session(DataProvider.MySql, "server", "database", _                       "user", "p@$$w0rd")  //    *edit* - corrected parameter notation with '@' Dim strInsert = "INSERT INTO tbl_resource (ResourceName, ResourceRate, ResourceTypeID, ActiveYN) " & _                 "VALUES (@ResourceName, @ResourceRate, @ResourceType, @ActiveYN)"  Dim recordCount = session.WriteData(Of WTUser)(strInsert, resources)  //  session Methods     Public Function QueryReader(Of TEntity As {Class, New})(ByVal Command As String) _                                                             As IEnumerable(Of TEntity)         Dim list As IEnumerable(Of TEntity)          Dim cnn As IDbConnection = dataAgent.NewConnection         list = cnn.Query(Of TEntity)(Command, Nothing, Nothing, True, 0, CommandType.Text).ToList()          Return list     End Function      Public Function WriteData(Of TEntity As {Class, New})(ByVal Command As String, ByVal Entities As IEnumerable(Of TEntity)) _                                                           As Integer         Dim cnn As IDbConnection = dataAgent.NewConnection          //    *edit* if I do this I get the correct properties, but no data inserted         //Return cnn.Execute(Command, New TEntity(), Nothing, 15, CommandType.Text)          //    original Return statement         Return cnn.Execute(Command, Entities, Nothing, 15, CommandType.Text)     End Function 

cnn.Query and cnn.Execute call the dapper extension methods. Now, the WTUser class (note: the column name changed from 'WindowsName' in SqlServer to 'ResourceName' in MySql, thus the two properties pointing to the same field):

Public Class WTUser     //    edited for brevity - assume the following all have public get/set methods     Public ActiveYN As String     Public ResourceID As Integer     Public ResourceRate As Integer     Public ResourceType As Integer     Public WindowsName As String     Public ResourceName As String  End Class 

I am receiving an exception from dapper: "WTUser is not supported by Dapper." This method in DataMapper (dapper):

    private static Action<IDbCommand, object> CreateParamInfoGenerator(Type OwnerType)     {         string dmName = string.Format("ParamInfo{0}", Guid.NewGuid());         Type[] objTypes = new[] { typeof(IDbCommand), typeof(object) };          var dm = new DynamicMethod(dmName, null, objTypes, OwnerType, true); // << - here         //    emit stuff          //    dm is instanced, now ...         foreach (var prop in OwnerType.GetProperties().OrderBy(p => p.Name)) 

At this point OwnerType =

System.Collections.Generic.List`1[[CRMBackEnd.WTUser, CRMBE, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

It seems like OwnerType should be CRMBackEnd.WTUser ... not List<CRMBackEnd.WTUser> ... ??? because what is happening is that the collection properties are being iterated: Count, Capacity, etc. What am I missing?

Update

If I modified session.WriteData as:

Public Function WriteData(Of TEntity As {Class, New})(ByVal Command As String, _                                                       ByVal Entities As IEnumerable(Of TEntity)) _                                                       As Integer     Dim cnn As IDbConnection = dataAgent.NewConnection     Dim records As Integer      For Each entity As TEntity In Entities         records += cnn.Execute(Command, entity, Nothing, 15, CommandType.Text)     Next      Return records End Function 

records are inserted nicely ... but I didn't think this would be necessary given examples like:

connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",     new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }   ).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"   

... from dapper-dot-net

Answer 1

I just added a test for this:

class Student {     public string Name {get; set;}     public int Age { get; set; } }  public void TestExecuteMultipleCommandStrongType() {     connection.Execute("create table #t(Name nvarchar(max), Age int)");     int tally = connection.Execute(@"insert #t (Name,Age) values(@Name, @Age)", new List<Student>      {         new Student{Age = 1, Name = "sam"},         new Student{Age = 2, Name = "bob"}     });     int sum = connection.Query<int>("select sum(Age) from #t drop table #t").First();     tally.IsEqualTo(2);     sum.IsEqualTo(3); } 

It works as advertised. I made a few amendments to the way multi-exec works (so its a tad faster and supports object[]).

My guess is you were having issues cause you were missing a getter property on all you fields on WTUser. All params must have reader properties, we do not support pulling this from fields, it would require a complex parsing step to stay efficient.

---

An additional point that caused an issue is passing dapper a param with unsupported mapping.

For example, the following class is not supported as a param:

class Test {    public int Id { get; set; }    public User User {get; set;} }  cnn.Query("select * from Tests where Id = @Id", new Test{Id = 1}); // used to go boom  

The issue is that dapper did not parse the SQL, it assumed all the props are settable as params but was unable to resolve the SQL type for User.

Latest rev resolves this