Finding many duplicates based on multiple properties in one database hit

Question

I'm trying to perform a check on the database to see if the combination of two properties exists in the database check (pre-Unique constraint check for better UX). Doing the check with a single property is easy, no matter how many you're trying to check. I'm unable to find how to do it with multiple properties in an enumerable.

public class Foo
{
    public int Id { get; set; }
    public int Bar { get; set; }
    public int Bat { get; set; }
    public string Name { get; set; }
    //...
}

public class FooDupeCheckModel
{
    public int Bar { get; set; }
    public int Bat { get; set; }
}

public IEnumerable<FooDupeCheckModel> MatchExists(IEnumerable<FooDupeCheckModel> matches)
{
    return _db.Foos
              .Where(f => matches.Any(m => m.BarId == f.BarId &&
                                           m.BatId == f.BatId))
              .Select(f => new FooDupeCheckModel
              {
                  BarId = f.BarId,
                  BatId = f.BatId
              });
}

This unfortunately gives an exception because the complex property matches cannot be converted into a SQL script. Only primitive types can be included into a query.

I also tried converting matches to a multidimensional array before using it within the query, but indexes are not supported within a query. .First() is not allowed to be used their either.

public IEnumerable<FooDupeCheckModel> MatchExists(IEnumerable<FooDupeCheckModel> matches)
{
    var matchArray = matches.Select(m => new [] {m.BarId, m.BatId})
                            .ToArray();

    return _db.Foos
              .Where(f => matchArray.Any(m => m[0] == f.BarId &&
                                              m[1] == f.BatId))
              .Select(f => new FooDupeCheckModel
              {
                  BarId = f.BarId,
                  BatId = f.BatId
              });
}

This may be one of those situations that is such a niche case, or requires a SQL query that is too complex for Entity Framework that it is not possible. If it is possible, then this would be very helpful if someone else runs into the same issue.

I did get around this by looping through and calling the database for each element in matches, but if I could do this in one database call, that'd be quicker.

Answer 1

SQL Server doesn't support comparing tuples. However, you can compare two+ properties by OR-ing together comparisons:

SELECT *
FROM Foo f
WHERE
(
    (f.Bar = 1 AND f.Bat = 1)
    OR
    (f.Bar = 3 AND f.Bat = 2)
)

Unfortunately, there's no easy way to build up an IQueryable<T> involving an OR. You can, however, build it up using Expression tree builders:

var models = new FooDupeCheckModel[]
{
    new FooDupeCheckModel() { Bar = 1, Bat = 2 },
    new FooDupeCheckModel() { Bar = 1, Bat = 3 }
};
var comparison = getComparison(models);

IQueryable<Foo> foos = new Foo[]
{
    new Foo() { Bar = 1, Bat = 1 },
    new Foo() { Bar = 1, Bat = 2 },
    new Foo() { Bar = 1, Bat = 3 },
    new Foo() { Bar = 1, Bat = 4 }
}.AsQueryable();
var results = foos.Where(comparison).ToArray();

...

private static Expression<Func<Foo, bool>> getComparison(IEnumerable<FooDupeCheckModel> models)
{
    ParameterExpression pe = Expression.Parameter(typeof(Foo), "f");
    var ands = models.Select(m =>
    {
        // Compare Bars
        Expression pBarId = Expression.Property(pe, "Bar");
        Expression vBarId = Expression.Constant(m.Bar);
        Expression bar = Expression.Equal(pBarId, vBarId);

        // Compare Bats
        Expression pBatId = Expression.Property(pe, "Bat");
        Expression vBatId = Expression.Constant(m.Bat);
        Expression bat = Expression.Equal(pBatId, vBatId);

        Expression and = Expression.And(bar, bat);
        return and;
    }).ToArray();
    if (ands.Length == 0)
    {
        return Expression.Lambda<Func<Foo, bool>>(Expression.Constant(true), pe);
    }
    else
    {
        Expression ors = ands.First();
        foreach (Expression and in ands.Skip(1))
        {
            ors = Expression.OrElse(ors, and);
        }
        return Expression.Lambda<Func<Foo, bool>>(ors, pe);
    }
}

This works against in-memory data structures. Test it again SQL Server; it should generate the corresponding SQL.

Here is a version that supports an arbitrary number of properties with any names:

public class Foo
{
    public int Id { get; set; }
    public int Bar { get; set; }
    public int Bat { get; set; }
    public string Name { get; set; }
}

public class FooDupeCheckModel
{
    public int Bar { get; set; }
    public int Bat { get; set; }
}

static void Main(string[] args)
{
    var models = new FooDupeCheckModel[]
    {
        new FooDupeCheckModel() { Bar = 1, Bat = 2 },
        new FooDupeCheckModel() { Bar = 1, Bat = 3 }
    };
    var comparison = getComparison<Foo, FooDupeCheckModel>(
        models,
        compare((Foo f) => f.Bar, (FooDupeCheckModel f) => f.Bar),
        compare((Foo f) => f.Bat, (FooDupeCheckModel f) => f.Bat)
    );

    IQueryable<Foo> foos = new Foo[]
    {
        new Foo() { Bar = 1, Bat = 1 },
        new Foo() { Bar = 1, Bat = 2 },
        new Foo() { Bar = 1, Bat = 3 },
        new Foo() { Bar = 1, Bat = 4 }
    }.AsQueryable();
    var query = foos.Where(comparison);
    var results = query.ToArray();
}

private class PropertyComparison
{
    public PropertyInfo FromProperty { get; set; }

    public PropertyInfo ToProperty { get; set; }
}

private static PropertyComparison compare<TFrom, TFromValue, TTo, TToValue>(
    Expression<Func<TFrom, TFromValue>> fromAccessor, 
    Expression<Func<TTo, TToValue>> toAccessor)
{
    MemberExpression fromMemberAccessor = (MemberExpression)fromAccessor.Body;
    PropertyInfo fromProperty = (PropertyInfo)fromMemberAccessor.Member;
    MemberExpression toMemberAccessor = (MemberExpression)toAccessor.Body;
    PropertyInfo toProperty = (PropertyInfo)toMemberAccessor.Member;
    return new PropertyComparison() { FromProperty = fromProperty, ToProperty = toProperty };
}

private static Expression<Func<TFrom, bool>> getComparison<TFrom, TTo>(
    IEnumerable<TTo> models, 
    params PropertyComparison[] comparisons)
{
    ParameterExpression pe = Expression.Parameter(typeof(TFrom), "f");
    if (!models.Any() || !comparisons.Any())
    {
        return Expression.Lambda<Func<TFrom, bool>>(Expression.Constant(true), pe);
    }
    var ands = models.Select(m =>
    {
        var equals = comparisons.Select(p =>
        {
            PropertyInfo fromProperty = p.FromProperty;
            PropertyInfo toProperty = p.ToProperty;
            object value = toProperty.GetValue(m);

            Expression fromValue = Expression.Property(pe, fromProperty);
            Expression toValue = Expression.Constant(value);
            Expression equal = Expression.Equal(fromValue, toValue);
            return equal;
        }).ToArray();
        var and = equals.First();
        foreach (var equal in equals.Skip(1))
        {
            and = Expression.AndAlso(and, equal);
        }
        return and;
    }).ToArray();
    Expression ors = ands.First();
    foreach (Expression and in ands.Skip(1))
    {
        ors = Expression.OrElse(ors, and);
    }
    return Expression.Lambda<Func<TFrom, bool>>(ors, pe);
}

Answer 2

Since Foo is a type that belongs to the model, you could project your matches to IEnumerable<Foo>, mapping only the two properties of interest, then issue the query. That should make it work.

public IEnumerable<FooDupeCheckModel> MatchExists(IEnumerable<FooDupeCheckModel> matches)
{
    //Convert small set to check for dups to objects recognized by the EF
    var fooMatches = matches.Select(m => new Foo() { BarId = m.BardId, BatId = m.BatId });

    //This should now work
    return _db.Foos
              .Where(f => fooMatches.Any(m => m.BarId == f.BarId &&
                                           m.BatId == f.BatId))
              .Select(f => new FooDupeCheckModel
              {
                  BarId = f.BarId,
                  BatId = f.BatId
              });
}