Parameters with Dapper parameters with auto-generated LINQ types

Question

I use a combination of LINQ and Dapper in my work. I am replacing my LINQ code on Dapper with places for performance reasons. I have many LINQ data objects created by dragging and dropping into a Visual Studio SQL Server database diagram.

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

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

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

However, when I run this code, I get the following error:

Type: SMDApp.Models.Animal - this is not supported by dapper

The following code works:

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

It would be more convenient for me to use an existing object, especially when I use more than one property of the object as a parameter for the request. Can someone tell me why this works for an anonymous object, but not for automatically creating a LINQ object?

Edited in response to Ben Robinson's answer.

Edited a second time in response to Mark Gravell's answer.

Answer 1

The short version is already work; based on error:

Type: SMDApp.Models.CagedAnimal not supported by dapper

I suggest that either you actually pass new {cagedAnimal} instead of cagedAnimal , or your cagedAnimal has a property ( Parent , maybe?), Which itself is cagedAnimal , and which dapper can’t understand. The current behavior is that a parameter is added for every open property of the provided parameter object - and if it cannot figure out how to send any properties to the database, it complains.You should find that a simple POCO with only value members works fine.

However! Please note that he never tries to parse your SQL - in particular, he does not check the parameters in the submitted query. Thus, using the POCO approach means 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" } 

Answer 2

Mark has just made the transition to a fix for this problem , in particular:

• We perform a trivial check before submitting an attempt to translate the properties into parameters. For example, Dapper will not send any parameters to the server for this case: cnn.Query ("select 1", new {bla = 1}), the reason "bla" does not exist on the line. This check is skipped for stored procedures.

• The bug, which was pretty cryptic, is now fixed and significantly improved.

-

Dapper used to parse the underlying SQL statement, for example:

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

Contains one parameter called @AnimalId .

This is a tricky reason to be 100% correct, you need to handle EG boundary cases: @AnimalId to select '@AnimalId' -- @AnimalId \* @AnimalId *\ ? The regular expression is getting a little complicated, I did not think about every edge case. For example: Oracle prefixes its parameters with : which complicates the situation.

Since dapper knew nothing about the parameters in the string, he decided to send to each public property as a parameter. Some of your public properties cannot be mapped to DbParameters , so he complained.