Dapper .Net : table column and model property type mismatch

dapper

Question

Actually I have a query which returns result containing column(for ex.Address) of type varchar but the domain model for that table containing property of type object(for ex. Address Address).Because of which it trows error which says could not cast string to Comment.I cant figure out how to resolve this issue with dapper .net.

Code snippet:

IEnumerable<Account> resultList = conn.Query<Account>(@"
                    SELECT * 
                    FROM Account
                    WHERE shopId = @ShopId", 
new {  ShopId = shopId });

The Account object is for example.

public class Account {
  public int? Id {get;set;}
  public string Name {get;set;}
  public Address Address {get;set;}
  public string Country {get;set;}
  public int ShopId {get; set;}
}

As there is type mismatch between database table column(Address) and domain model property(Address) dapper throws exception.So is there is any way to map that properties though dapper..

Accepted Answer

Since there is a type mismatch between your POCO and your database, you'll need to provide a mapping between the two.

public class Account {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string DBAddress {get;set;}
  public Address Address 
  {
   // Propbably optimize here to only create it once.
   get { return new Address(this.DBAddress); } 
  }

  public string Country {get;set;}
  public int ShopId {get; set;}
}

Something like that - you match the db column to the property DBAddress (You need to provide an alias like SELECT Address as DBAddress instead of *) and provide a get method on your Address object which creates / reuses a type of Address with the contents of the db value.


Popular Answer

Another option is to use Dapper's Multi-Mapping feature.

public class TheAccount
{
    public int? Id { get; set; }
    public string Name { get; set; }
    public Address Address { get; set; }
    public string Country { get; set; }
    public int ShopId { get; set; }
}

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
}

public class Class1
{
    [Test]
    public void MultiMappingTest()
    {
        var conn =
            new SqlConnection(
                @"Data Source=.\SQLEXPRESS; Integrated Security=true; Initial Catalog=MyDb");
        conn.Open();

        const string sql = "select Id = 1, Name = 'John Doe', Country = 'USA', ShopId = 99, " +
                           " Street = '123 Elm Street', City = 'Gotham'";

        var result = conn.Query<TheAccount, Address, TheAccount>(sql, 
            (account, address) =>
                {
                    account.Address = address;
                    return account;
                }, splitOn: "Street").First();

        Assert.That(result.Address.Street, Is.Not.Null);
        Assert.That(result.Country, Is.Not.Null);
        Assert.That(result.Name, Is.Not.Null);
    }
}

The only issue I see with this is that you'll have to list all of the Account fields, followed by the Address fields in your select statement, to allow splitOn to work.



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