Dapper parameterised queries with LINQ autogenerated types

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 Rob开发者_开发知识库inson's reply.

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

---

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" }

---

Marc just committed a change to fix for this issue in particular:

1. We perform a trivial validation before sending attempting to translate properties to params. For example Dapper will not not send any params to the server for this case: cnn.Query("select 1", new {bla = 1}) cause "bla" does not exist in the string. This validation is skipped for stored procs.

2. The error, which was rather cryptic, is now fixed and much improved.

--

Dapper used to perform no parsing of the underlying SQL statement, so for example:

@"select * 
from animal
where animalid = @AnimalId"

Contains a single param called @AnimalId.

It gets complicated cause, to be 100% correct you need to handle edge cases EG: @AnimalId into the string select '@AnimalId' -- @AnimalId \* @AnimalId *\? The regex does get a bit tricky, I have not thought through every edge case. For example: Oracle prefixes its params with a : which complicates things more.

Since dapper knew nothing about the params in the string, it decided to send in every public property as a parameter. Some of your public properties can not be mapped to DbParameters so it complained.