CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
user2524113
user2524113

Reputation: 11

Building an IQueryable dynamically

I have the following IQueryable:

        IQueryable<Class1> queryable =
            (from c1 in DbContext.Set<Class1>()
             from c2 in DbContext.Set<Class2>()
             from c3 in DbContext.Set<Class3>()
             where c1.Id == c2.Class1Id
             && c2.Id == c3.Class2Id
             && c3.ValueAsString == val
             select c1);

In the case above, val is a string. However, Class3 has several other members:

    public string ValueAsString { get;private set; }
    public int? ValueAsInteger { get; set; }
    public DateTime? ValueAsDate { get; set; }
    public decimal? ValueAsDecimal { get; set; }
    public bool? ValueAsBoolean { get; set; }

I need to modify the IQueryable depending on the type of 'val' which could be one of the 5 types above. Is it possible to construct the IQueryable as:

        IQueryable<Class1> queryable =
            (from c1 in DbContext.Set<Class1>()
             from c2 in DbContext.Set<Class2>()
             from c3 in DbContext.Set<Class3>()
             where c1.Id == c2.Class1Id
             && c2.Id == c3.Class2Id                 
             select c1);

And then depending on the type of 'val' add the final where before execution? For example, if val is a decimal, append 

c3.ValueAsDecimal == val

Upvotes: 1

Views: 2805

Answers (2)

Maarten
Maarten

Reputation: 22945

That is actually pretty easy, you can just call .Where(...) when a condition passes. You just have to pre-select all values c1, c2 and c3, and after you've added the .Where(...) calls you can select the c1 value from the result. Like this:

var q = (
    from c1 in dbContext.Set<Class1>()
    from c2 in dbContext.Set<Class2>()
    from c3 in dbContext.Set<Class3>()
    where c1.Id == c2.Class1Id
    && c2.Id == c3.Class2Id
    select new { c1, c2, c3 }
);
object var = ...; // Some value
if (var is decimal)
{
    q = q.Where(x => x.c3.ValueAsDecimal == (decimal)var);
}
else if (var is DateTime)
{
    q = q.Where(x => x.c3.ValueAsDate == (DateTime)var);
}
// TODO: Add other types of 'var' 

var queryable = q.Select(x => x.c1);

Upvotes: 1

Red
Red

Reputation: 2776

Looks like for your task it would be more handy to use expressions instead of sql-like linq. As I can see your classes are connected with each other with Primary Keys, and if using properties, this query can look approximately like this:

Expression<Func<Class3, bool>> filterExpression = GetFilterExpression(val); //returns expression bases on val type

var queryable = DbContext.Set<Class1>()
                             .Include(cl1=>cl1.Class2.Class3) //or .Include(cl1=>cl1.Class2.Select(cl2=>cl2.Class3)) depending on your object relationships
                             .Where(filterExpression);

Includes here are used if you need Class2 and Class3 instances loaded for your root entities of type Class1. If you don't need them, you can skip .Include() constructions.

Example of GetFilterExpression:

public Expression<Func<Class1, bool>> GetFilterExpression(string value) 
{ 
    return cl1 => cl1.Class2.Class3.ValueAsString == value;
}

Upvotes: 1

Related Questions