Dapper parameterised queries with LINQ autogenerated types

Question

I'm using a combination of LINQ and Dapper in my work. I'm replacing my LINQ code with Dapper in places for performance reasons. I have a lot of LINQ data objects created by dragging and dropping into the Visual Studio database diagram from SQL Server.

In the following instance I already have a LINQ object in memory and I'd like to pass it to Dapper as the parameters for a query. For example:

Animal animal = con.Query<Animal>(" select * " +
        " from animal " +
        " where animalid = @AnimalId " +
        " and animaltype = @AnimalType ",
        cagedAnimal).SingleOrDefault();

cagedAnimal contains a public properties AnimalId and AnimalType with getters and setters.

However on executing this code I get the following error:

The type : SMDApp.Models.Animal is not supported by dapper

The following code does work:

Animal animal = con.Query<Animal>(" select * " +
            " from animal " +
            " where animalid = @AnimalId " +
            " and animaltype = @AnimalType ",
            new 
            { 
            AnimalId = cagedAnimal.AnimalId, 
            AnimalType = cagedAnimal.AnimalType 
            }
            ).SingleOrDefault();

It'd be more convenient for me to use an existing object particularly where I'm using more than one property of the object as a parameter for the query. Can anybody tell my why this works for an anonymous object but not an auto generated LINQ object?

Edited in response to Ben Robinson's reply.

Edited a second time in response to Marc Gravell's reply.

Answer 1

The short version is that should already work; based on the error:

The type : SMDApp.Models.CagedAnimal is not supported by dapper

I conclude that either you are actually passing new {cagedAnimal} instead of cagedAnimal, or, your CagedAnimal has a property (Parent, perhaps?) that is itself a CagedAnimal, and which dapper can't understand. The current behaviour is that a parameter is added for every public property of the provided parameter object - and if it can't figure out how to send any of the properties to the database, it complains. You should find that a simple POCO with just value members works fine.

However! Note that it does not ever attempt to parse your SQL - in particular, it does not check for parameters in the query provided. As such, using the POCO approach will mean that you are adding unnecessary properties to the query.

We use dapper extensively, and we just use the approach:

 new { obj.Foo, obj.Bar, id, key = "something else" }