CODEWITHSUNDEEP
dapper
webworm
webworm

Reputation: 11019

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?

Upvotes: 41

Views: 36033

Answers (11)

Leonardo Hernández
Leonardo Hernández

Reputation: 68

Since COUNT(1) or COUNT(*) returns a quantity, I prefer to use ExecuteScalar<int> and then check if the result is greater than zero.

var query = "SELECT COUNT(1) FROM tableName WHERE Id = @id";
bool exists = cnn.ExecuteScalar<int>(query, new { id = entity.Id }) > 0;

For any integer that results from the query, it will be checked if it is greater than zero, which results in true (exists).

Upvotes: 0

silkfire
silkfire

Reputation: 26033

Solution for Oracle:

SELECT CAST(
    (CASE 
        WHEN EXISTS (
            SELECT 1
            FROM "Table" T
            WHERE T."Column" = ...
        ) 
        THEN 1 
        ELSE 0 
    END) AS NUMBER(3)
) AS "Result"
FROM DUAL

Upvotes: 0

Kha Bui
Kha Bui

Reputation: 1

I am using this syntax, it works for me, for postgres.

select count(*)>0 from table_name where gid=@value;

For Oracle

SELECT CASE WHEN (B.C = 0) THEN 0 WHEN (B.C > 0) THEN 1 END boolean
  FROM (select count(*) as c from table_name where gid=@value) B
  WHERE B.C > 0;

Upvotes: 0

frhack
frhack

Reputation: 5112

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

Upvotes: 4

Александр Шмыков
Александр Шмыков

Reputation: 25

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 .

Upvotes: 0

mikesigs
mikesigs

Reputation: 11420

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

Upvotes: 0

Janeks Malinovskis
Janeks Malinovskis

Reputation: 570

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 });

Upvotes: 7

Liam
Liam

Reputation: 29750

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;

Upvotes: 1

Kevin Finck
Kevin Finck

Reputation: 317

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();

Upvotes: 9

Marc Gravell
Marc Gravell

Reputation: 1063844

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

should work...

Upvotes: 81

Void Ray
Void Ray

Reputation: 10219

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);
    }

Upvotes: 3

Related Questions