Dapper.NET如何在内部使用.Count（）和SingleOrDefault（）？ [英] How Dapper.NET works internally with .Count() and SingleOrDefault()?

问题描述

我是Dapper的新手，尽管我了解ORM和DAL，并且较早时已使用NHibernate实现了DAL。

I am new to Dapper though I am aware about ORMs and DAL and have implemented DAL with NHibernate earlier.

示例查询：-

string sql = "SELECT * FROM MyTable";
public int GetCount()
{
    var result = Connection.Query<MyTablePoco>(sql).Count();
    return result;
}

Dapper会将此查询（内部）转换为 SELECT COUNT（*）来自MyTable 到底看着 .Count（）？

Will Dapper convert this query (internally) to SELECT COUNT(*) FROM MyTable looking at .Count() at the end?

同样，如果使用 SingleOrDefault（），它将转换为 SELECT TOP 1 * FROM MyTable 吗？

Similarly, will it convert to SELECT TOP 1 * FROM MyTable in case of SingleOrDefault()?

我来自NHibernate世界，它会据此生成查询。我不确定Dapper。在使用MS Access时，没有看到检查生成查询的方法。

I came from NHibernate world where it generates query accordingly. I am not sure about Dapper though. As I am working with MS Access, I do not see a way to check the query generated.

推荐答案

不，dapper不会调整您的查询。直接说明这一点的方法很简单：该方法返回 IEnumerable ... 与 IQueryable ... 吗？如果是第一个，则只能使用本地内存机制。

No, dapper will not adjust your query. The immediate way to tell this is simply: does the method return IEnumerable... vs IQueryable...? If it is the first, then it can only use local in-memory mechanisms.

具体来说，默认情况下是 ，查询实际上将返回一个完全填充的 List<> 。 LINQ的 Count（）方法可以识别出该内容，并且仅访问列表的 .Count 属性。因此，所有数据都是从数据库中获取的。

Specifically, by default, Query will actually return a fully populated List<>. LINQ's Count() method recognises that and just accesses the .Count property of the list. So all the data is fetched from the database.

如果您想向数据库询问计数，请向数据库询问计数。

If you want to ask the database for the count, ask the database for the count.

关于查看实际发送到数据库的机制：为此，我们使用mini-profiler。

As for mechanisms to view what is actually sent to the database: we use mini-profiler for this. It works great.

注意：当您只查询一行时： QueryFirstOrDefault （以及其他变量）期望）存在并在内部进行优化（包括对ADO.NET的提示，尽管并非所有提供程序都可以对这些事情采取行动）以尽可能高效地完成工作，但不会调整您的查询。在某些情况下，提供程序本身（而非精简程序）可以提供帮助，但最终可以：如果只希望第一行，请向第一行询问数据库（ TOP 或类似）。

Note: when you are querying exactly one row: QueryFirstOrDefault (and the other variants you would expect) exist and have optimizations internally (including hints to ADO.NET, although not all providers can act on those things) to do things as efficiently as possible, but it does not adjust your query. In some cases the provider itself (not dapper) can help, but ultimately: if you only want the first row, ask the database for the first row (TOP or similar).

这篇关于Dapper.NET如何在内部使用.Count（）和SingleOrDefault（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！