There is an entity that using composite key:
[<CLIMutable>]
type Entity =
{ Id1: int
Id2: int
Number: string }
I'd like to filter entities by list of ids tuple using EF Core
. Ideally the produced query should use many wheres like this:
SELECT * FROM Entities
WHERE
(Id1 = 1 AND Id2 = 1) OR
(Id1 = 2 AND Id2 = 2) // etc.
According to this EF issue code below won't work:
let getMany(context: DbCtx) (ids: (int * int) array) =
context
.Entities
.Where(fun ef ->
Linq.Enumerable.Any(ids, fun (id1, id2) ->
ef.Id1 = id1
&& ef.Id2 = id2))
.ToListAsync()
Based on answer from this thread I tried concatenate many where's but this produce many queries with single where
combined by UNION ALL
:
let getMany(context: DbCtx) (ids: (int * int) array) =
ids
|> Array.fold (fun (q: IQueryable<Entity>) (id1, id2) ->
let predicate = context.Entities.Where(fun ef -> ef.Id1 = id1 && ef.Id2 = id2)
if q = null then predicate else Queryable.Concat(q, predicate)
) null
|> fun q -> q.ToListAsync()
Expression is an option but I'd like to use Quatation that is more F#-friendly. However I'm not sure how to use it.
Ok, I've got a solution:
let getMany(context: DbCtx) (ids: (int * int) list) =
let predicate =
if ids = [] then
<@ fun (_: Entity) -> true @>
else
ids
|> List.map(fun (id1, id2) -> <@ fun ef -> ef.Id1 = id1 && ef.Id2 = id2 @>)
|> List.reduce(fun expr1 expr2 -> <@ fun ef -> (%expr1) ef || (%expr2) ef @>)
let q =
query {
for c in context.Entities do
where ((%predicate) c)
}
q.ToListAsync()
The key thing is to use query expression.