Why does SQL SELECT via connection.Query returns an IENumerable and SQL SELECT via MSSMS returns an-CodePudding

Background information

I have a SQL Server table of events called Events which include 2 columns, id that contains a unique number per event, and dt which holds the date & time at which the event occurred.

I've created a list by reading table Events and adding new events via an API.

I update the database by going through all events and adding them if the unique id is not already in the database. The loop time is longer than is allowed and therefore, I would like to loop while the highest id in the new list is higher than the highest id in the database.

Desired result in SSMS

To get the latest event via Microsoft SQL Server Management Studio I successfully queried one of the following:

SELECT MAX(id) 
FROM Events

SELECT TOP (1) [id] 
FROM Events

In my Console App I added the commented out lines to replace the for-loop that iterates the entire dataset:

But when I try this way, I do not get an integer but an IEnumerable (please ignore the cam_obj_type and Dt-Utc as I have excluded these from the example to keep it simple).

My question is: why doesn't the SQL query in my C# console app return the same result as the query in SSMS when using a SELECT statement, but does when using an INSERT INTO statement?

The class Actions is as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AtriumConsoleApp
{

}

CodePudding user response：

You are using the Dapper method Query<T> which, by definition, returns a collection of T objects mapped from your query. If you have a query that just returns a single integer, use the QuerySingle<int> method:

var latestID = connection.QuerySingle<int>("SELECT MAX(id) FROM Events");

Or you can also just use the core ExecuteScalar method:

using (command = new SqlCommand("SELECT MAX(id) FROM Events", conn)) 
{
    var latestID = command.ExecuteScalar();
}

CodePudding user response：

Aren't you using Dapper library? If so, Query<T> method returns an IEnumerable<T>. You can add a .FirstOrDefault() after the final parenthesis like this:

var yourResult = connection.Query<Actions>("..").FirstOrDefault();

Still, the result is a IEnumerable of Actions, not an integer. Use .ExecuteScalar method as pointed above, which returns an object (the first columnf of the first row).