Map SQL columns in SqlMapper.Query<T1,T2,TReturn> to class properties

Question

First I do not have much experience with dapper.net and maybe this problem has simple solution.

I would like use multi mapping features.

I simplified my problem.

I have 2 tables.

First table

CARS

Columns:

• CARD_ID (PK)
• CAR_LOCATION
• CAR_STATUS

Second table

BRANDS

Columns:

• ID (PK)
• CARD_ID (FK)
• BRAND_NAME
• BRAND_LOGO

I need execute this query:

SQL_CMD:

SELECT * FROM CARS
LEFT JOIN BRANDS
ON CARS.CARD_ID = BRANDS.CARD.ID;

In .NET application I map these tables on 2 POCO classes.

public class Car
{
  public int CarId {get;set}
  public string CarLocation {get;set;}
  public string CarStatus {get;set;} 
  public Brand Brand {get;set;}
}

public class Brand
{
  public int Id {get;set}
  public int CardId {get;set;}
  public string BrandName {get;set;}
  public string BrandLogo {get;set;}
}

When I query sql cmd above:

        var data = connection.Query<Car, Brand, Car>(SQL_CMD, (car, brand) =>
        {
            car.Brand = brand;
            return car;

        }, commandTimeout: 50000, splitOn:"ID")

I get empty result.

IMHO problem is in mapping SQL columns on class properties because if I changed prop Card.CarId to Car.CAR_ID this properties is filled.

Is there any way how to map "SQL columns" to class properties in SqlMapper.Query<T1,T2,TReturn> to class properties?

I know that exist ClassMapper but I didn’t find way how to use in this scenario.

Thanks

EDITED:

I tried add mapping classes, but not work

public class CarMapper : ClassMapper<Car>
{
   Table("CARS");
   Map(c=>c.CarId).Column("CAR_ID").KeyType(KeyType.Assigned);
   Map(c=>c.CarLocation).Column("CAR_LOCATION");
   Map(c=>c.CarStatus).Column("CAR_STATUS");
}

public class BrandMapper : ClassMapper<Brand>
{
   Table("BRANDS");
   Map(c=>c.Id).Column("ID").KeyType(KeyType.Assigned);
   Map(c=>c.CarId).Column("CAR_ID");
   Map(c=>c.BrandName).Column("BRAND_NAME");
   Map(c=>c.BrandLogo).Column("BRAND_LOGO");
}

add mapping assemblies

DapperExtensions.DapperExtensions.SetMappingAssemblies(new List<Assembly>
{
    Assembly.GetAssembly(typeof(CarMapper)), 
    Assembly.GetAssembly(typeof(BrandMapper)),                                                         
});

and after this set up execute query

    var data = connection.Query<Car, Brand, Car>(SQL_CMD, (car, brand) =>
    {
        car.Brand = brand;
        return car;

    }, commandTimeout: 50000, splitOn:"ID")

As I told not work not work properties of objects are empty but if I tried simple insert, update or delete it works.

I am confuse now where can be root of problem.

Answer 1

Dapper Extensions provides 4 extensions: Get, Insert, Update and Delete, so your second example wont work. Query extension is a part of core Dapper. As far as I can tell, Dapper Extensions does not support multi-mapping.

The only option I can think of is to modify your select statement and rename the attributes, for example:

SELECT CAR_ID as CarId, CAR_LOCATION as CarLocation, CAR_STATUS as CarStatus, etc.
FROM CARS
LEFT JOIN BRANDS
ON CARS.CARD_ID = BRANDS.CARD.ID

Then multi-mapping should work.

Also, you don't need to specify splitOn if you are splitting by ID. Dapper splits on Id fields automatically.

Dapper assumes your Id columns are named "Id" or "id", if your primary key is different or you would like to split the wide row at point other than "Id", use the optional 'splitOn' parameter.