Dapper : How to load virtual properties?

Question

Dapper : How to load virtual properties?

c# dapper sql

Question

I want to retrieve an Advert

public class Advert
{
    public int ID { get; set; }
    public DateTime DateCreation { get; set; }
    public String Title { get; set; }
    public String Description { get; set; }
    public virtual Member Creator { get; set; }
}

(with the Creator property loaded)

public class Member
{
    public int ID { get; set; }
    public String Name { get; set; }
    public String Email { get; set; }
    public String Password { get; set; }
}

with the following function :

public Advert GetById(int id)
{
    String sql = "select * from Advert inner join Member on Advert.Creator = Member.ID where Advert.ID = @aid";
    return unitOfWork.Connection.Query<Advert,Member,Advert>(sql, (advert, member) => { advert.Creator = member; return advert; } , new { aid=id}).Single();
}

But I have the following error :

InvalidCastException: unable to cast object of type 'System.Int32' to 'Models.Member'.

What could be the cause of the error?

ptiflo

View more on Stack Overflow

Nkosi · Accepted Answer · 6/24/2018 3:08:22 PM

Based on the query it looks like the Advert.Creator column, which appears to be of type int, is conflicting with the Member Creator property on the model.

Change the name of the property to something else to avoid the name conflict

public class Advert {
    public int ID { get; set; }
    public DateTime DateCreation { get; set; }
    public String Title { get; set; }
    public String Description { get; set; }
    public virtual Member Owner { get; set; }
}

And update the query accordingly

public Advert GetById(int id) {
    var connection = unitOfWork.Connection;
    String sql = 
    @"select * from Advert AS A
    inner join Member AS M on A.Creator = M.ID 
    where A.ID = @aid";
    var data connection.Query<Advert, Member, Advert>(
        sql, 
        (advert, member) => { advert.Owner = member; return advert; }, 
        new { aid = id }
    );
    return data.Single();
}

Ideally the Advert.Creator column in the table should have been name CreatorId. This would have allowed the original code to work as desired.

Dapper : How to load virtual properties?

Question

Fastest Dapper Plus Extensions

Accepted Answer

Performance

Expression Evaluator