Check if record exists with Dapper ORM

问题:

What is the simplest way to check if record exists using the Dapper ORM?

Do I really need to define POCO objects for a query where I only want to check if a record exists?

回答1:

int id = ...
var exists = conn.ExecuteScalar<bool>("select count(1) from Table where Id=@id", new {id});

should work...

回答2:

I think this may have a tad less overhead as there's no function call or data type conversions:

int id = ...
var exists = connection.Query<object>(
    "SELECT 1 WHERE EXISTS (SELECT 1 FROM MyTable WHERE ID = @id)", new { id })
    .Any();

回答3:

const string sql = "SELECT CAST(CASE WHEN EXISTS (SELECT 1 FROM MyTable WHERE Id = @Id) THEN 1 ELSE 0 END as BIT)";
bool exists = db.ExecuteScalar<bool>(sql, new { Id = 123 });

回答4:

You can have your query to return a bool:

    [Test]
    public void TestExists()
    {
        var sql = @"with data as
                    (
                        select 1 as 'Id'
                    )
                    select CASE WHEN EXISTS (SELECT Id FROM data WHERE Id = 1)
                           THEN 1 
                           ELSE 0
                      END AS result 
                    from data ";

        var result = _connection.Query<bool>(sql).FirstOrDefault();

        Assert.That(result, Is.True);
    }

回答5:

Another option that will run with duplicate records, i.e. not querying the id of the table

bool exists = connection.ExecuteScalar<int>(
    "select count(1) from Table where notanId=@value", new { value = val})
     > 0;

回答6:

If you need to do this sort of query against a non-unique field you can use HAVING to handle counts greater than 1.

SELECT 1
FROM Table
WHERE Col=@val
HAVING COUNT(1) > 0

回答7:

imho SELECT TOP(1) is better than SELECT COUNT(1)

    bool exists = connection.Query<ValueTuple<long>>(
        "SELECT top(1) Id FROM MYTABLE WHERE MYTABLE.Id=@Id",
        new {Id}).Any());

The ValueTuple<long> is value type . Query<object> map to reference type and causes boxing .

回答8:

conn.QuerySingleOrDefault<bool>("select top 1 1 from table where id=@id", new { id});