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  x.Client.Id != request.Editor.ClientId 
                        && x.ClientIdentifierType.Id == identifier.TypeId 
                        && 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 => ci.Client.Id != request.Editor.ClientId
                   && identifiers.Any(i => ci.ClientIdentifierType.Id == i.TypeId
                                      && 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 => i.TypeId).ToList();
    var identifiersValues = identifiers.Select(i => i.Value).ToList();

    var errors = 
        dbContext.ClientIdentifers
            .Where(ci => 
                   {
                       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);
                   });                       
}

StackExchange Code Review Q#83758, answer score: 2