Using Linq syntax, how can I remove the select n+1 issue in this method? I have the following code in a validation module. It works the way I want it to, but it has an obvious select n+1 problem. I would like to refactor this code to make a single call to the db to perform this validation.

Is there a way I can do it using linq or do I need to perform more major surgery? I am trying to avoid sprocs at this point, but am willing to listen to arguments for it.

private void EnsureIdentifiersAreNotInUse(AddOrEditClientCommand request)
        {
            var identifiers = request.Editor.Identifiers;

            for (var i = 0; i &lt; identifiers.Count; i++)
            {
                var identifier = identifiers[i];
                var exists = dbContext.ClientIdentifers
                    .Any(x =&gt; x.Client.Id != request.Editor.ClientId 
                        &amp;&amp; x.ClientIdentifierType.Id == identifier.TypeId 
                        &amp;&amp; x.Value.Equals(identifier.Value, StringComparison.InvariantCultureIgnoreCase));
                if(exists)
                    request.ModelState.AddModelError("Identifiers[{0}].Value".FormatWith(i), "System Id ({0}) is already in use".FormatWith(identifier.Value));
            }
        }
I think this might work for you

void EnsureIdentifiersAreNotInUse(AddOrEditClientCommand request)
{
    var identifiers = request.Editor.Identifiers;

    var errors = 
        dbContext.ClientIdentifers
            .Where(ci =&gt; ci.Client.Id != request.Editor.ClientId
                   &amp;&amp; identifiers.Any(i =&gt; ci.ClientIdentifierType.Id == i.TypeId
                                      &amp;&amp; ci.Value.Equals(i.Value, StringComparison.InvariantCultureIgnoreCase)));
}


UPD
Ok, your identifier type is not presented at db, so the query cannot be converted to a db query.

Let's try this:

void EnsureIdentifiersAreNotInUse(AddOrEditClientCommand request)
{
    var identifiers = request.Editor.Identifiers;
    var identifiersIds = identifiers.Select(i =&gt; i.TypeId).ToList();
    var identifiersValues = identifiers.Select(i =&gt; i.Value).ToList();

    var errors = 
        dbContext.ClientIdentifers
            .Where(ci =&gt; 
                   {
                       if (ci.Client.Id == request.Editor.ClientId)
                       {
                           return false;
                       }
                       var indexOfId = identifiersIds.IndexOf(ci.ClientIdentifierType.Id);
                       if (indexOfId == -1)
                       {
                           return false;
                       }
                       return indexOfId == identifiersValues.IndexOf(ci.Value);
                   });                       
}