Geek Answers Handbook

Check if record exists with ORM Dapper

What is the easiest way to check if a record exists with ORM Dapper?

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

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

must work...

+35
source

I think this may have a slightly lower overhead since there are no function calls 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();
+4
source

You have a bool return request:

  [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); }
+2
source 
 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 });
+2
source

Another option that will work with duplicate records, i.e. do not request table identifier

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

If you need to perform this kind of query on a non-unique field, you can use HAVING to process values ​​greater than 1.

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

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

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

ValueTuple<long> is a value type. Query<object> maps to the reference type and calls the box.

0
source

More articles:


All Articles