使用LINQ短小精悍的参数化查询，自动生成类型 [英] Dapper parameterised queries with LINQ autogenerated types

问题描述

我用我的工作LINQ和小巧玲珑的组合。我与小巧玲珑的替换出于性能的考虑的地方我的LINQ代码。我有很多通过拖放到从SQL Server在Visual Studio数据库图表创建LINQ数据对象。

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.

在下面的例子中，我已经在内存中的LINQ对象我想将它传递给小巧玲珑作为查询的参数。例如：

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包含公共属性AnimalId和AnimalType的getter和setter方法。

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

不过在执行此代码，我得到了以下错误：

However on executing this code I get the following error:

类型：SMDApp.Models.Animal为
不短小精悍

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

下面的代码不工作：

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

这会更方便我用现有的对象，我使用超过尤其是在对象作为查询的参数的一个属性。谁能告诉我，为什么这个工程的一个匿名对象，但不是一个自动生成的LINQ的对象？

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.

编辑回应马克Gravell的答复第二次。

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

推荐答案

短版的这应该已经工作的;基于该误差：

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

类型：SMDApp.Models.CagedAnimal不受hardy中

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

我得出这样的结论要么你实际上是通过新{cagedAnimal} 而不是 cagedAnimal ，或的，你的 CagedAnimal 有一个属性（父，也许？ ）这本身就是一个 CagedAnimal ，以及短小精悍无法理解。目前的行为是一个参数添加的每公共财产的提供的参数对象 - 如果它不能找出如何发送任何属性的数据库，它抱怨。你会发现，一个简单的POCO只值成员工作正常。

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.

不过！需要注意的是它不曾经尝试解析您的SQL - 特别是，它不检查所提供的查询参数。 ，因此，使用POCO的做法将意味着你的查询增加不必要的性能

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

这篇关于使用LINQ短小精悍的参数化查询，自动生成类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！