LINQ Query to check if a single combo exists out of a list of combos

Question

Using EF Core 3.x

I am trying to check if a database table, Ducks, already contains any records with a matching combo of values, from a list of combo values, stored in a class: Duck.

What I am expecting to be returned is the Duck.Ids of the records that already exist / have the matching values I'm passing in.

Right now I'm only able to check if a single combo exists at a time using this query

The combo here being 1, "NV", and "Mallard". The result is true

var duckExists = Ducks
    .Any(duck =>
        duck.PondId == 1 &&
        duck.StateId == "NV" &&
        duck.Type == "Mallard");

Now I want to check if multiple Ducks already exists.
I have a list, ducksToCheck, that I want to use when querying the database to see if any of them already exist:

var ducksToCheck = new List<Duck>() {
    // Does exist
    new Duck{
        PondId = 1,
        State = "NV",
        Breed = "Mallard"
    },
    // Does exist
    new Duck{
        PondId = 2,
        State = "AZ",
        Breed = "Crested"
    },
    // Does not exist
    new Duck{
        PondId = 1,
        State = "AZ",
        Breed = "Crested"
    },
    // Does not exist
    new Duck{
        PondId = 2,
        State = "NV",
        Breed = "Mallard"
    }
};

My latest attempt was to put each unique combo value into their own list and check against those lists in the query.
This doesn't work because so long as a Duck had a matching value in each list it is seen as existing when it really doesn't.
Such as the 3rd Duck: (1, "AZ", "Crested"). Since the resulting SQL is duck.PondId IN(pondIds) && duck.State IN(states) && duck.Breed IN(breeds) the first two ducks have a mix of these values and it can mix and match them to say it does exist.

var pondIds = ducksToCheck.Select(x => x.PondId).Distinct().ToList();
var states = ducksToCheck.Select(x => x.State).Distinct().ToList();
var breeds = ducksToCheck.Select(x => x.Breed).Distinct().ToList();

var existingDuckIds = Ducks
    .Where(duck => pondIds
        .Any(pondId => pondId == duck.pondId)
            && states.Any(state => state == duck.State)
            && breeds.Any(breed => breed == duck.Breed)
    )
    .Select(duck => duck.Id);

Is it possible to query if a single combo exists in the Ducks table, out of a list of combos, without sending a query for each individual combo / Duck in ducksToCheck?

wahid-moh · Answer 1 · 2020-05-31T00:13:17.107

0

/!\ Work only for evaluated data

On your Model object you can implement IEquatable interface, adding your equal script on Equals method, like :

public bool Equals([AllowNull] Duck other)
{
    return other.Id == ID; // add other conditions
}

And call it as :

var result = Ducks.Any(x => ducksToCheck.Contains(x));

If you don't like make any change on your db model you can add a new checking method :

private bool Check(Duck x, List<Duck> filterDucks)
{
    foreach (var filterDuck in filterDucks)
    {
        if (filterBook.Title == x.Title)
            return true;
        else
            continue;
    }
    return false;
}

And call it as :

var result = Ducks.Where(x => Check(x ,ducksToCheck));

Good luck,

edited May 31 '20 at 00:13

answered May 23 '20 at 02:08

wahid-moh

46
4

Unfortunately neither of these worked for me. The `Equals` method creates a query where it tries to check if `[ducks].[Id] IN (CAST(0 AS bigint), CAST(0 AS bigint), CAST(0 AS bigint), CAST(0 AS bigint), CAST(0 AS bigint), CAST(0 AS bigint))`. I believe it's doing a `CAST` for each object in my list. Not positive. The `Check` method is rejected by EF as it can't translate it into a query and would have to force it into an In Memory query. I have also tried to implement `IEqualityComparer` on my `Duck` model and EF is unable to translate that as well. – Ryan Taite May 26 '20 at 16:02
ah yes, we can use these solutions only for evaluted data, it will be comlicated to evaluate the query on the server and make the filter on client ! – wahid-moh May 31 '20 at 00:15

LINQ Query to check if a single combo exists out of a list of combos

1 Answers1