Existence check and count

Aug 14, 2012 at 6:07 AM
Edited Aug 14, 2012 at 6:09 AM

What is the best way to perform existence check and counting items with IRepository methods? So far for existence check  I couldn't come with something better than 

bool Exists(IRepository<Entity> entityRepo, int id)
{
  try {
    entityRepo.First(entity => entity.ID == id);
    return true;
  }
  catch {
    return false;
  }

But obviously this is ugly at the very least.

Please understand me right: I tried Find().Count() and Find().Any(), but they seem to generate full-fledge SELECT queries and run Count() and Any() on their results, not the optimal SELECT COUNT() and SELECT TOP 1.

Coordinator
Aug 23, 2012 at 9:00 PM
Edited Aug 23, 2012 at 9:03 PM

Hello pechkin,

sorry for the long delay, I'm just back from vacation (without any decent Internet connection)...

Now on your problem, what you can do to approximate a Select Count is : 

int res = entityRepo.AsQueryable().Count(w => w.Id == 100);

which will be translated in

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
	COUNT(1) AS [A1]
	FROM [dbo].[WorkBenches] AS [Extent1]
	WHERE 100 = [Extent1].[Id]
)  AS [GroupBy1] 

Please note that in most cases, the Find().Any() construct is also a good solution

bool bResult = entityRepo.Find(w => w.Id == 100).Any();

resulting in :

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[CreatedBy] AS [CreatedBy], 
[Extent1].[Created] AS [Created], 
[Extent1].[UpdatedBy] AS [UpdatedBy], 
[Extent1].[Updated] AS [Updated], 
[Extent1].[DeletedBy] AS [DeletedBy], 
[Extent1].[Deleted] AS [Deleted]
FROM [dbo].[WorkBenches] AS [Extent1]
WHERE (100 = [Extent1].[Id])

returning at most 1 item if the Id column is the primary key. Then Any() works on the collection (one item max) in memory.

Hope this helps.