Best way to check if a record/row/entity exists in a table?

Reply

southwo8 Posted: 10-14-2009 9:31 PM

rated by 0 users

Hi,

I'd like to know the most efficient way to check if a record exists by searching on a single column.  I don't need the whole entity, I just want to know if a row exists in the table that matches my criteria.  Here's the code I'm trying right now, based on a database table called "Product":

1    public bool ProductExists(string nameToFind)
2    {
3        ProductQuery q = new ProductQuery();
4        q.es.Top = 1;
5        q.Where(q.Name == nameToFind);
6        
7        return q.Load();
8    }

Code:
1    public bool ProductExists(string nameToFind)
2    {
3        ProductQuery q = new ProductQuery();
4        q.es.Top = 1;
5        q.Where(q.Name == nameToFind);
6    
7        Product product = new Product();
8        return product.Load(q); 
9    }

Then it works correctly, returning "True" and "False" when it should based on the data in the database table.  Is this second method the best way to accomplish this with ES?  I should also probably add a "q.Select(q.Id)" line in there too, since I don't need all the columns, just need a T/F if the row exists.

Based on my code above, it also seems that the "query.Load()" method does not return T/F correctly when it's not attached to an actual entity?

Reply

Mike.Griffin replied on 10-15-2009 5:17 AM

rated by 0 users

This is probably the fastest way ...

namespace BusinessObjects
{
    public partial class Employees : esEmployees
    {
        static public bool Exists(int employeeID)
        {
            EmployeesQuery query = new EmployeesQuery();
            query.es.CountAll = true;
            query.Where(query.EmployeeID == employeeID);
            int count = (int)query.ExecuteScalar();

            return count == 1 ? true : false;
        }
    }
}

Notice that the method is static and therefore doesn't require that you even instantiate an Employees object. Also, it uses ExecuteScalar which is faster than loading data because it returns only a single value (and doesn't create a DataTable and all that stuff). The SQL generated is as follows:

SELECT COUNT(*) AS ''Count'' 
FROM [Employees] 
WHERE [EmployeeID] = @EmployeeID1

Finally, you would call it in your code like this ...

if (Employees.Exists(1))
{
    // Then it exists ...
}

Reply

southwo8 replied on 10-15-2009 7:06 AM

rated by 0 users

Great, thanks!

Is there a possibility this will make it into a future release of ES?  Seems like a useful thing.  The API could allow the "Where" clause part to be passed in.  Something maybe like this:

1    // check existence by PK...
2    
3    if(Employees.Exists(1))
4    {
5        // it exists
6    }
7    
8    
9    // or, allow the column to be passed in somehow
10   
11   if(Employees.Exists("Name == John"))
12   {
13       // it exists
14   }