What's the difference between 'QueryFirstOrDefault' and 'ExecuteScalar'?

Question

In Dapper, what is the difference between the usage of QueryFirstOrDefault<T> and ExecuteScalar<T>?

Both seem to return a single value, although QueryFirstOrDefault<T> will return a default value for the type, if none is found. Should you always prefer the use of one of the two, due to performance, or other reasons, or are they equivalent, disregarding the default value returned?

@user10728126 could you please link the resource stating that? — SpiritBob, Sep 10 '19 at 13:44
https://stackoverflow.com/questions/8050636/is-there-an-executescalar-in-dapper — Hans Passant, Sep 10 '19 at 13:59

Néstor Carreño Gimeno · Accepted Answer · 2019-09-27T11:33:56.447

6

The main difference is on their return:

ExecuteScalar<T>, as other users said, returns only the first cell of the result set.

The return type of ExecuteScalar<T> is generic <T>, and it automatically casts the result to the type you specified on <T>

QueryFirstOrDefault<T> executes a query and returns the data type you specified on <T>:

If the specified type is a basic type (string, int, etc...) it only returns the data from the first column.
If the specified type is an Object, then for each row of the result set an instance of that object is created. Be aware of the fact that a direct column-name===member-name mapping is assumed and no custom mapper can be used so if your object has different field names than the columns of the result set, no values will be fetched.

edited Sep 27 '19 at 11:33

answered Sep 10 '19 at 13:49

Néstor Carreño Gimeno

523
2
12

So for an `int` (or any other primitive type), `ExecuteScalar` and `QueryFirstOrDefault` are exactly equivalent, correct? – MarredCheese Nov 06 '20 at 02:47
1

Exactly, in that scenario they are equivalent! – Néstor Carreño Gimeno Nov 07 '20 at 22:50
About column names and mapping - if they are different than the names of the properties/fields on your object, using aliases in your sql will do the trick. – Ilian Vasilev Kulishev Oct 20 '22 at 06:32

pete the pagan-gerbil · Answer 2 · 2019-09-10T13:41:52.670

0

Scalar expects there to only be a single row returned from the SQL script; QueryFirstOrDefault doesn't care how many rows are returned.

I think (can't check this right now) that Scalar might throw an error if zero rows are returned (thanks @Tim for correcting me on multiple results).

I'd say the preference depends on what the script you've written is expected to do.

edited Sep 10 '19 at 13:41

answered Sep 10 '19 at 13:38

pete the pagan-gerbil

3,136
2
28
49

No error is generated if the result contains multiple rows. Internally, Dapper calls `IDbCommand.ExecuteScalar()`, which "Executes the query, and returns the first column of the first row in the resultset returned by the query. Extra columns or rows are ignored." Sources: [1](https://github.com/StackExchange/Dapper/blob/e47d819d4e3be933af97bde6b1b6f4f8c41fa534/Dapper/SqlMapper.cs) [2](https://learn.microsoft.com/en-us/dotnet/api/system.data.idbcommand.executescalar?view=netcore-3.1#System_Data_IDbCommand_ExecuteScalar) – MarredCheese Nov 06 '20 at 02:31

What's the difference between 'QueryFirstOrDefault' and 'ExecuteScalar'?

2 Answers2