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:
DbSet
as the first argumentIEnumerable
, returns the first record in the provided DbSet
where the Code
field matches the provided stringSo 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.
The easiest (and type safe) approach would be just to introduce an interface like IHaveCode
and limit the generic parameter to it:
interface IHaveCode
{
string Code { get; set; }
}
class A: IHaveCode
{
public string Text { get; set; }
public string Code { get; set; }
}
class B: IHaveCode
{
public string Name { get; set; }
public string Code { get; set; }
}
class C: IHaveCode
{
public string Book { get; set; }
public string Code { get; set; }
}
public static T GetCode<T>(IQueryable<T> set, string code) where T : IHaveCode
{
return set.Where(x => x.Code == code).FirstOrDefault();
}
If for some reason it does not suit you then you will need to construct an expression tree yourself.