Efficiently check if record exists in database using Entity framework LINQ

Question

I need to check if customer by code already exists in the database using entity framework. Ideally I would write plain sql query like this:

select id from dbo.Customer where RecActive = 1 and Code = 'xxx';

If query result is empty, it means customer by code 'xxx' does not exist yet. In entity framework there are multiple ways to write this, but I'm looking the one nearest above. Note: Code field has unique index on it

  using (var context = new CustomerContext())
  {
    // not very efficient as it reads all columns
    return context.Customers.Where(c => c.RecActive && c.Code == customerCode).SingleOrDefault() != null ? true : false;

    return context.Customers.Where(c => c.RecActive && c.Code == customerCode).Count() > 0 ? true : false;

    // translates to this query:
    // exec sp_executesql N'SELECT [Limit1].[Id] AS [Id]
    // FROM ( SELECT TOP (2) 
    //        [Extent1].[Id] AS [Id]
    //        FROM [dbo].[Customer] AS [Extent1]
    //        WHERE ([Extent1].[RecActive] = 1) AND (([Extent1].[Code] = 
    //          @p__linq__0) OR (([Extent1].[Code] IS NULL) AND 
    //          (@p__linq__0 IS NULL)))
    //        )  AS [Limit1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'xxx'
    int a = context.Customers.Where(c => c.RecActive && c.Code == customerCode).Select(c => c.Id).SingleOrDefault();             
    return a > 0 ? true : false;

    return context.Customers.Where(c => c.RecActive && c.Code == customerCode).Any();
  }

Perhaps any other good (performance alternative)? Note: I have to use entity framework linq and not raw queries (which I would really like) as linq is used throughout whole project consistently.

return context.Customers.Where(c => c.RecActive && c.Code == customerCode).Any(); — gilliduck
– gilliduck, Commented Jan 29, 2018 at 14:50
Why do you need something like that? Are you trying to write an UPSERT method? If Code is the primary key EF will perform the check itself before inserting or updating. If you want to check for existence for some other reason use Any(). If you want the ID use .Where()...).Select(c=>c.id) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jan 29, 2018 at 14:53
Any() will likely use select null in its query and rather than select id. It's a very minor difference, but the question is round about in both asking that it matches that minor inefficiency of the original query and also asking that it be the most performant. — Jon Hanna
– Jon Hanna, Commented Jan 29, 2018 at 15:48

Sergey Kalinichenko · Accepted Answer · 2018-01-29 15:41:46Z

9

Since your code ignores the actual ID, and returns only a true/false indication of its presence, you can do it with an single call of Any:

return context.Customers.Any(c => c.RecActive && c.Code == customerCode);

answered Jan 29, 2018 at 15:41

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Panagiotis Kanavos · Accepted Answer · 2018-01-29 15:00:07Z

If you want to check for existence only, use .Any(). If you want to retrieve the ID(s) that match the criteria, use Select, eg :

 context.Customers
        .Where(c => c.RecActive && c.Code == customerCode)
        .Select(c => c.id);

If multiple IDs are a valid result, your method should return an array of strings/integers, whatever the type of id is. You can return an array of IDs with .ToArray();

 return context.Customers
               .Where(c => c.RecActive && c.Code == customerCode)
               .Select(c => c.id)
               .ToArray();

If you don't expect multiple IDs, you have to decide what to do if you get multiples results:

FirstOrDefault() will return the first ID without throwing if there are multiple results.
SingleOrDefault() will throw if there are multiple results preventing the use of a possibly invalid ID.

For example:

return context.Customers.Where(c => c.RecActive && c.Code == customerCode)
               .Select(c => c.id)
               .SingleOrDefault();

janos laszlo · Accepted Answer · 2024-03-04 11:15:37Z

For checking the existence of multiple entities with a composite PK I came up with the following:

// Returns the subset of entities that don't exist in the db.
private IReadOnlyList<TEntity> GetNewEntities<TEntity>(
    IEnumerable<TEntity> entities,
    Func<TEntity, Expression<Func<TEntity, bool>>> getPredicate) where TEntity : class
{
    var newEntities = new List<TEntity>();
    foreach (var entitiesBatch in entities.Batch(100))
    {
        var entitiesExistence = GetEntitiesExistence(entitiesBatch, getPredicate);
        newEntities.AddRange(entitiesBatch
            .Where((_, i) => !entitiesExistence[i]));
    }

    return newEntities;
}

private IReadOnlyList<bool> GetEntitiesExistence<T>(IEnumerable<T> entities, Func<T, Expression<Func<T, bool>>> getPredicate) where T : class
{
    IQueryable<bool> query = null;
    var set = this.dataEntities.Set<T>();
    foreach (var entity in entities)
    {
        var tempQuery = set.Where(getPredicate(entity))
            .Select(x => true)
            .DefaultIfEmpty(false);
        query = query == null ? tempQuery : query.Concat(tempQuery);
    }
    return query.ToArray();
}

getPredicate specifies the equality between 2 entities, it could be:

e1 => e2 =>
    e1.Key1 == e2.Key1 &&
    e1.Key2 == e2.Key2

Experiment with batch size for performance. Usage example:

var newUsers = GetNewEntities(users,
    u1 => u2 =>
        u1.FirstName == u2.FirstName &&
        u1.LastName == u2.LastName);

Collectives™ on Stack Overflow

Efficiently check if record exists in database using Entity framework LINQ

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related