Query for existence of an object with LINQ (to Entities)

Question

(I've put "...to Entities" in brackets since I don't know if this matters at all. I guess this is a quite general LINQ related question.)

I want to check with LINQ (to Entities) if an object exists in the database. At the moment I am doing the following:

using (MyEntitiesContext aCtx = new MyEntitiesContext())
{
    var aQuery = from c
                 in aCtx.Client
                 where c.ClientID==1
                 select c;

    Client aClient = aQuery.FirstOrDefault();

    bool Exists = (aClient!=null);
    ...
}

But (if I am not wrong) this loads the full Client object from the database (if the Client exists). I am actually only interested whether it exists or not without loading the object.

SQL has the SELECT COUNT(*)... construct. Is there something similar I can do with LINQ?

Thank you for advice!

Answer 1

You could try...

using (MyEntitiesContext aCtx = new MyEntitiesContext())
{
     var aQuery = from c
         in aCtx.Client
         where c.ClientID==1
         select c;

     int total = aQuery.Count();

     bool Exists = (total > 0);
     ...
}

Untested...

Answer 2

One option is to use the Any method of IQueryable. It will return a boolean value indicating whether or not an object was found matching the specified condition.

using (MyEntitiesContext aCtx = new MyEntitiesContext())
{
    bool exists = (from c
                   in aCtx.Client
                   where c.ClientID==1
                   select c).Any();
}

This method will also stop running as soon as it evaluates to true.

Answer 3

You can use the Count() operator or the Any Operator on your query to check if it would return a result:

using (MyEntitiesContext aCtx = new MyEntitiesContext())
{
    var aQuery = from c
                 in aCtx.Client
                 where c.ClientID==1
                 select c;

    int count = aQuery.Count();

    bool Exists = (count > 0);

    // or
    Exists = aQuery.Any();

    ...
}

Answer 4

I would then use Any() to determine existence. Regardless of whether you create a dummy instance like the following, the compiler will create a SQL statement using the Exists function and thus, it will not matter what is in the Select statement.

var query= from c
            in context.Client
            where c.ClientID == 1
            select new { Dummy = "foo" };

var exists = query.Any();