entity-framework-coref#quotations

EF Core Quotations - filter composite key by list of ids


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.


Solution

  • 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.