35

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?

webworm
  • 10,587
  • 33
  • 120
  • 217

9 Answers9

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

should work...

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 2
    @webworm note it gets tricky if you have more than 1 record with the key, but ... you shouldn't have that :) – Marc Gravell Aug 18 '16 at 16:41
  • 7
    Just use `COUNT(DISTINCT 1)` to ensure you get an answer of 1 (true) even if you have multiple records with the same id/key. Admittedly that should be an edge case. – Caltor Apr 19 '17 at 12:21
  • 1
    I'm guessing this works because `1` is parsed as a bool(`true`) by Dapper? So if you have 2 it will fail? Hence the comment? – Liam Sep 05 '17 at 13:07
  • 1
    @Liam I would expect that 2 would evaluate to `true`. The reason for the comment is simply that 2 is probably an unexpected value, so ... it hides a problem. I'd prefer `` personally – Marc Gravell Sep 05 '17 at 14:41
  • or `SELECT TOP 1 COUNT(*) FROM X WHERE ...` – dovid Jul 21 '22 at 10:19
9

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();
Kevin Finck
  • 317
  • 3
  • 6
6
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 });
Janeks Malinovskis
  • 511
  • 1
  • 5
  • 8
3

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);
    }
Void Ray
  • 9,849
  • 4
  • 33
  • 53
  • 4
    Nope, disagree. 'Count' processes the entire table since the query optimizer can't rule out multiple matches for your where clause.. 'Exists' returns as soon as the first one is found. The difference can be significant when you have a lot of rows in your table, especially if this query is repeated often. For an example see this blogpost I found - http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx – Arno Peters Aug 24 '17 at 15:15
  • 2
    @Volkirith In general, that's true, but there is an exception. If the `WHERE` clause filters by a column with a unique index, the optimizer **can** conclude that there will be 0 or 1 rows and perform a seek instead of a table scan. I guess this is the case here as the column is named "id". – Alejandro Sep 05 '17 at 13:25
2
conn.QuerySingleOrDefault<bool>("select top 1 1 from table where id=@id", new { id});
frhack
  • 4,862
  • 2
  • 28
  • 25
1

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;
Liam
  • 27,717
  • 28
  • 128
  • 190
0

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
mikesigs
  • 10,491
  • 3
  • 33
  • 40
0

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 .

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Александр Шмыков
  • 27
  • 6
0

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

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;
Kha Bui
  • 1
  • 2