samedi 27 juin 2020

LINQ Generic IQueryable

Let's say I have a DBContext with 3 DBSets:

public class DatabaseContext : DbContext
{
    public DbSet<A> As { get; set; }
    public DbSet<B> Bs { get; set; }
    public DbSet<C> Cs { get; set; }
}

class A
{
    public string Text { get; set; }
    public string Code { get; set; }
}

class B
{
    public string Name { get; set; }
    public string Code { get; set; }
}

class C
{
    public string Book { get; set; }
    public string Code { get; set; }
}

I want to write a generic method that:

  1. Generically takes any of the three DbSet as the first argument
  2. Takes a string value as the second argument
  3. Without casting to IEnumerable, returns the first record in the provided DbSet where the Code field matches the provided string

So far I have this method:

public static T GetCode<T>(IQueryable<T> set, string code) where T : class
{
    var Prop = typeof(T).GetProperty("Code");
    return set.Where(x => (string)Prop.GetValue(x) == code).FirstOrDefault();
}

When I try to call it using this line:

var _A = GetCode(TheDB.As, "123");
var _B = GetCode(TheDB.Bs, "123");
var _C = GetCode(TheDB.Cs, "123");

I get this error:

InvalidOperationException:

The LINQ expression 'DbSet<A>.Where(m => (string)__Prop_0.GetValue(m) == __code_1)' could not be translated.

Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

How can I write the WHERE clause on the DbSet that is able to translate properly for IQueryable? My method works if I cast the IQueryable to an IEnumerable, but I don't want to do that, since the set may potentially be very large and I want the database (not my application) to do the record searching.





Aucun commentaire:

Enregistrer un commentaire