CODEWITHSUNDEEP
c#sql-serverdapper
George
George

Reputation: 57

Using Dapper one-to-many (spliton) with generics

I am trying to map objects in a one to many relationship using Dapper. Assume a database with a Departments and a People table. People table has a DepartmentId column to create the relationship between People and Departments. Each person can only be in one department.

I have the following models in code:

public class PersonModel
{
    public int Id { get; set; }
    public string FullName { get; set; }
    public DepartmentModel Department { get; set; }
}

public class DepartmentModel
{
    public int Id { get; set; }
    public string Name { get; set; }
}

I want to use Dapper to fetch People and store them in object that contains a Departments property. Which is possible with SplitOn as follows:

public Task<IEnumerable<PersonModel>> GetPeople<PersonModel>(string connectionString)
{
     using var connection = new SqlConnection(connectionString);

     var query = "Select People.Id, People.FullName, Department.Name FROM People LEFT JOIN Departments ON People.DepartmentId = Departments.Id";

    return await connection.QueryAsync<PersonModel, DepartmentModel, PersonModel>(query, (person, department) => {
        person.Department = department;
        return person;
    }, 
    splitOn: "Id" );
}

Question: so far so good. What I want is to turn this into a re-usable method using generics and I am stuck because I cannot do the assignment person.Department = department with generics, as .Department is a property that I don't know of. And I cannot simply use department. I also tried model.typeof(TSubModel)(see below).

Is there any way to achieve this?

Code that fails is:

public Task<IEnumerable<TModel>> GetPeople<TModel, TSubModel>(string connectionString, string query)
{
     using var connection = new SqlConnection(connectionString);

     return await connection.QueryAsync<TModel, TSubModel, TModel>(query, (model, subModel) => {
        model.subModel= subModel; // <-- obviously model.subModel fails and so does model.typeof(TSubModel). 
        return model;
    }, 
    splitOn: "Id" );
}

PS: there is a similar question that the OP answered but doesn't use SplitOn here (SO question)

Upvotes: 0

Views: 89

Answers (2)

Charlieface
Charlieface

Reputation: 72194

Unless there was some way you could work out which property to set via reflection then this is not really possible.

You could pass through an Action<TModel, TSubModel> lambda from the outside.

But you have no way of passing query parameters or any of the other parameters that QueryAsync takes, so you should really add that in.

public Task<IEnumerable<TModel>> GetPeople<TModel, TSubModel>(string connectionString, string query, DynamicParameters? parameters, Action<TModel, TSubModel> splitOnAction)
{
    using var connection = new SqlConnection(connectionString);
    return await connection.QueryAsync<TModel, TSubModel, TModel>(query, (model, subModel) => {
        splitOnAction(model, subModel);
        return model;
    },
    parameters,
    splitOn: "Id" );
}

and

var results = await GetPeople<PersonModel, DepartmentModel>(connString, query, null,
  (person, dep) => person.Deparmtent = dep
);

But at this point I'm not sure what you gain over using Dapper directly.

I suggest you reconsider trying to do this "generically" as Dapper already provides that for you.

Upvotes: 2

iSR5
iSR5

Reputation: 3498

If you are going to use generics, then you can use reflection to get the TModel properties, then get the TSubModel property out of it, and set its value using reflection.

Here is untested example to demonstrate the concept :

public async Task<IEnumerable<TModel>> GetPeople<TModel, TSubModel>(string connectionString, string query)
    where TModel : class
    where TSubModel : class
{
    var properties = typeof(TModel).GetProperties(); 

    var childProperty  =  properties.FirstOrDefault(s=> s.PropertyType == typeof(TSubModel));
    
    if(childProperty == null) return Enumberable.Empty<TModel>();
    
    using var connection = new SqlConnection(connectionString);
    
    return await connection.QueryAsync<TModel, TSubModel, TModel>(query, (model, subModel) => 
    {
        childProperty.SetValue(model, subModel);
        return model;
    }, 
    splitOn: "Id");
}

Upvotes: 1

Related Questions