public class EmployerRestaurant
{
public int Id { get; set; }
public int EmployerId { get; set; }
public int RestaurantId { get; set; }
public bool IsPrimary { get; set; }
}
// configuration
builder.HasIndex(i => new {i.EmployerId, i.RestaurantId }).IsUnique();
Is it possible to perform a search on this table providing an array of unique indexes?
Something like
_context.EmployerRestaurant.Find({1,2},{2,2});
CodePudding user response:
Find is just a shorthand for DbSet.SingleOrDefault(), so you can just use it by providing a key as parameter.
The simplest way to get what you want is to filter the DbSet using a where statement:
_context.EmployerRestaurant.Where(e => (e.EmployerId == 1 && e.RestaurantId == 2) || e.EmployerId == 2 && e.RestaurantId == 2);
EF Core will translate the expression and the database will do the rest (using the index you provided).
As I suppose, if you don't know what ids to get at compile time, you can provide to Where()
a dynamically generated expression of type Expression<Func<EmployerRestaurant, bool>>
to get the same result:
List<(int empId, int RestId)>? selectedEmps = new List<(int empId, int RestId)>()
{
(1,2), (2,2)
};
//init an empty expression that compute to false because it's an Or operation
Expression<Func<EmployerRestaurant, bool>> predicateExpr = (e) => false;
foreach ((int empId, int RestId) in selectedEmps)
{
//concat the real condition for each item in the array to the "dummy" expression.
//The resulting expression will contains all your tuples linked in a big Or expression
Expression<Func<EmployerRestaurant, bool>> itemExpression = (e) => e.EmployerId == empId && e.RestaurantId == RestId;
InvocationExpression tmpExpr = Expression.Invoke(itemExpression, predicateExpr.Parameters.Cast<Expression>());
predicateExpr = Expression.Lambda<Func<EmployerRestaurant, bool>>(Expression.OrElse(predicateExpr.Body, tmpExpr), predicateExpr.Parameters);
}
//provide your generated predicate to EF Core and get the result
List<EmployerRestaurant>? emps = db.EmployerRestaurant.Where(predicateExpr).ToList();
To easily compose your query expression you can also use Joseph Albahari's PredicateBuilder.
Using the PredicateBuilder the foreach code will be like:
Expression<Func<EmployerRestaurant, bool>>? predicateExpr = PredicateBuilder.False<EmployerRestaurant>();
foreach ((int empId, int RestId) in selectedEmps)
{
predicateExpr = predicateExpr.Or((e) => e.EmployerId == empId && e.RestaurantId == RestId);
}
CodePudding user response:
Have not tested it, but tuples may help ...
List<(int employerId, int restaurantId)> _idsToSearchFor = new()
{
(1, 2)
(2, 2)
};
List<EmployerRestaurant> matches = _context.EmployerRestaurant
.Where(er => _idsToSearchFor.Contains((er.EmployerId, er.RestaurantId))
.ToList();