How to do an insert and update for an object with a navigation property using Dapper.Rainbow (or optionally using Dapper.Contrib)

Question

I started looking into Dapper just recently. I'm testing it out and was able to do basic CRUD and what I meant by basic is that working on a class with this structure:

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

Now I was looking for something that would make it easier to do inserts and updates and found Dapper.Rainbow. I checked it out and was able to use it to get and insert objects as described above. My problem is that when Product has a navigation property I can't do an insert on that field. So if I have this:

public class Product {
    public int Id {get;set;}
    public string Name {get;set;}
    public ProductCategory Category {get;set;}
}

I won't be able to do this:

// connection is a valid and opened connection            
 var db = TestDatabase.Init(connection , 300);
 var newId = db.Products.Insert(newProduct);

because of this reason:

The member Category of type ProductCategory  cannot be used as a parameter value

The issue can be resolved if I replace Category with type int (the same data type in the database). However if I do that, I won't be able to query a product with its category info, more than just the (category) Id.

So without resorting to raw Dapper, how can I do an insert and update using a class with a navigation property? I was hoping I can do the following and tell Dapper.Rainbow to ignore Category when inserting or updating.

public class Product {
    public int Id {get;set;}
    public string Name {get;set;}
    public ProductCategory Category {get;set;}
    public int CategoryId {get;set;} // this will be the same field name in the database
}

This scenario is possible with NHibernate where I can have a proxy object of Category and assign it to Product and save it and the mapping works perfectly. But I would love to use Dapper and that's why I am exploring and want to learn how things like this can be done.

Answer 1

Not With Dapper.Rainbow

This is not possible with Dapper.Rainbow in its current form but my pull request in github make this possible.

I am surprised that nobody suggests to use Dapper.Contrib. I know I asked if the functionality is in Rainbow. But I did not expect nobody will notice this statement (especially the text in bold):

Now I was looking for something that would make it easier to do inserts and updates and found Dapper.Rainbow. I checked it out and was able to use it to get and insert objects as described above. My problem is that when Product has a navigation property I can't do an insert on that field.

...and suggest an alternative, a solution that is already in the Dapper library. I guess I should have been clearer with my question and explicitly asked if a solution exists somewhere in the entire Dapper library that is in github. So after more digging in the library I found out that there is a support to my issue.

The Path to Dapper.Contrib

All is working well with my project and Rainbow until I needed more out of it. I got some tables that have a lot of fields in it. If I just feed Rainbow my object then it will do an update with all the fields, that is not all good. But that does not cause me to jump out of the boat quickly and go back to NH. So before I implemented my own change tracking, and I don't want to reinvent the wheel especially if someone has done a good job already, I googled around and found this SO thread. That thread confirmed my knowledge that Rainbow does not support change tracking but there is another beast that does and it's called Dapper.Contrib. And so I started experimenting with it.

And So We Meet Again

The member Category of type ProductCategory cannot be used as a parameter value

I got the same issue as I had with Rainbow. Contrib does not support navigation property!? I am starting to feel that I am wasting my time with Dapper, and the performance that it offers, which I am very much after for, will just be wishful thinking. Until...

The WriteAttribute, came to the rescue...

This class lives in the SqlMapperExtensions.cs file that is included in the Dapper.Contrib project. I did not find any documentation on this class nor does it has any comments that can make it be found easily and shout at me and say hey I'm the one you're looking for. I stumbled on this when I set aside Rainbow as I described above.

The usage of this class is the same as what I did with IgnorePropertyAttribute, it's an attribute that you can decorate your class' property with. You should decorate, with this attribute, any property that you do not want included in the sql that Dapper creates. So in my example, for me to tell Dapper to exclude the Category field I needed to do this:

public class Product {
    public int Id {get;set;}

    public string Name {get;set;}

    [Write(false)] // tell Dapper to exclude this field from the sql
    public ProductCategory Category {get;set;}

    public int CategoryId {get;set;}
}

I'm Almost There

Remember that the reason I go for Contrib is because of the change tracking functionality. This SO thread, the same link I gave above, states that for change tracking to kick in, you need to have an interface for your class and use it with Contrib. So for my example class I need to have:

public interface IProduct {
    int Id {get;set;}
    string Name {get;set;}
    ProductCategory Category {get;set;}
    int Category {get;set;}
}

// and implement it on my Product class
public class Product : IProduct {
    public int Id {get;set;}

    public string Name {get;set;}

    [Write(false)]
    public ProductCategory Category {get;set;}

    int Category {get;set;}
}

I thought that was it, almost! You might be asking why would I need to define Category in my interface if Dapper doesn't care about it at all. In fact that would only cause a problem, a problem that I would then solve.

In my particular scenario, there are times that I need to work on the Category field while maintaining the change tracking for the Product object. To maintain the tracking capability, the get call should be fed with an interface type like this:

var product = connection.Get<IProduct>(id);

and with that call I would not be able to access the Category field if I won't define it in my interface. But if I do define it in my interface I would then get the familiar error

The member {member} of type {type} cannot be used as a parameter value.

Really again? Make this stop please.

The Verdict

No need to worry as this one is easy to solve by decorating the interface member just as what we did for the class. So the final configuration to make everything work should be:

public interface IProduct {
    // I will not discuss here what this attribute does
    // as this is documented already in the github source.
    // Just take note that this is needed,
    // both here and in the implementing class.
    [Key]
    int Id {get;set;}

    string Name {get;set;}

    [Write(false)]
    ProductCategory Category {get;set;}

    int Category {get;set;}
}

// and implement it on my Product class
public class Product : IProduct {
    [Key]        
    public int Id {get;set;}

    public string Name {get;set;}

    [Write(false)]
    public ProductCategory Category {get;set;}

    int Category {get;set;}
}

You can use this approach if you prefer to work with Contrib that has the change tracking capability. If you want to work with Rainbow and are having problems with navigation property just as I had, then you can play with my pull request. It works the same way as the WriteAttribute only that it works with Rainbow.

If you are not a fan of decorating your classes with attributes then both the extension projects is not for you. I know that there is another extension project that will allow you do to some kind of fluent-type configuration, but that does not go with (not included as a core-part of) the Dapper library that is in github. My preference, which is to work with the core library only, lead me to investigate the entire library and see if things are already there or if it can be improved to meet my needs. And that's what I did and explained here, for both Rainbow and Contrib.

I hope that this contribution, the very simple class I added, the configuration tips I showed, and the scenario that leads me those, will help someone in the future who wishes to use Dapper, and that will have a similar setup that I have. Also, that this answer will educate devs more of what Dapper can and cannot do. This great tool called Dapper deserves a better wiki and I hope this answer/article here in SO helps even in a small way.

---

**And if what I wrote here is already written somewhere, that I have not found in the two-weeks time that I have been waiting for an answer, then I will be glad for anyone to link me to it. It's been two weeks now and the 29 people who looked into my question have not suggested any links or solution so I assumed the information I shared here is new to Dapper* :)

NOTE: I modified my question title so others can see this potential solution to their problem. The new title is based on the new knowledge I gained about Dapper.