public override IQueryable <FilmEntity> GetExampleQuery(LinqExpressionsDbContext context)
{
var expression = GetExpression(context);
// Store the filter as a dynamic query.
return(context.FilmEntities.Where(expression));
}
protected override Expression <Func <FilmTimeEntity, bool> > GetExpression(LinqExpressionsDbContext context)
{
var beginTime = DateTimeOffset.Parse("2019-08-03 12:00");
var endTime = DateTimeOffset.Parse("2019-08-04 11:00");
// We want to build the expression for the "Where" clause
// Example of Query we will eventually run.
// context.FilmTimeEntities.Where(ft => ft.FilmId == 3 && (ft.StartTime < beginTime || ft.StartTime >= endTime));
// Time to build up the clause in the ANY field
var ftParameter = Expression.Parameter(typeof(FilmTimeEntity), "ft"); // ft =>
var ftIdProperty = Expression.Property(ftParameter, "FilmId"); // ft.FilmId
var ftIdClause = Expression.Equal(ftIdProperty, Expression.Constant(3)); // ft.FilmId == 3
// Begin the OrElse statement
var ftStartTimeProperty = Expression.Property(ftParameter, "StartTime"); // ft.StartTime
var ftStartTimeFirstClause = Expression.LessThan(ftStartTimeProperty, Expression.Constant(beginTime)); // ft.StartTime < beginTime
var ftStartTimeSecondClause = Expression.GreaterThanOrEqual(ftStartTimeProperty, Expression.Constant(endTime)); // ft.StartTime >= endTime
// Or statement
var ftOrElseClause = Expression.OrElse(ftStartTimeFirstClause, ftStartTimeSecondClause); // (ft.StartTime < beginTime || ft.StartTime >= endTime)
// AndAlso statement
var ftAndClause = Expression.AndAlso(ftIdClause, ftOrElseClause); // ft.FilmId == 3 && (ft.StartTime < beginTime || ft.StartTime >= endTime)
// Lambda Expression
return(Expression.Lambda <Func <FilmTimeEntity, bool> >(ftAndClause, ftParameter));
}
protected override Expression <Func <FilmEntity, bool> > GetExpression(LinqExpressionsDbContext context)
{
var beginTime = DateTimeOffset.Parse("2019-08-03 12:30");
var endTime = DateTimeOffset.Parse("2019-08-03 16:30");
// We want to build the expression for the "Where" clause
// Example of Query we will eventually run.
// context.FilmEntities.Include("FilmTimes").Where(f.FilmTimes.Any(ft => ft.StartTime >= beginTime && ft.StartTime < endTime));
var fParameter = Expression.Parameter(typeof(FilmEntity), "f"); // f =>
// Get Film Times between beginTime and endTime
var fFilmTimeProperty = Expression.Property(fParameter, "FilmTimes"); // f.FilmTimes
// Time to build up the clause in the ANY field
var ftParameter = Expression.Parameter(typeof(FilmTimeEntity), "ft"); // ft =>
var ftStartTimeProperty = Expression.Property(ftParameter, "StartTime"); // ft.StartTime
var ftBeginTimeClause = Expression.GreaterThanOrEqual(ftStartTimeProperty, Expression.Constant(beginTime)); // ft.StartTime >= startTime
var ftEndTimeClause = Expression.LessThan(ftStartTimeProperty, Expression.Constant(endTime)); // ft.StartTime < endTime
// Builds up the AND expression
var ftFullQueryExpression = Expression.AndAlso(ftBeginTimeClause, ftEndTimeClause); // ft => ft.StartTime >= beginTime && ft.StartTime < endTime
// Make the Lambda Expression for Film Time
var ftLambda = Expression.Lambda <Func <FilmTimeEntity, bool> >(ftFullQueryExpression, ftParameter);
// Join in an ANY statement
var anyMethod = typeof(Enumerable).GetMethods().FirstOrDefault(method => method.Name == "Any" && method.GetParameters().Count() == 2); // Use reflection to find the ANY method (We use Enumerable for collections)
var anyFilmTimeMethod = anyMethod.MakeGenericMethod(typeof(FilmTimeEntity)); // Any is a generic method, so create a method specific to FilmTimeEntity
var anyCall = Expression.Call(anyFilmTimeMethod, fFilmTimeProperty, ftLambda); // FilmTimes.Any(ft => ft.StartTime >= beginTime && ft.StartTime < endTime)
// Return the lamba expression for film so we can put in the where clause.
return(Expression.Lambda <Func <FilmEntity, bool> >(anyCall, fParameter));
}
protected override Expression <Func <FilmEntity, bool> > GetExpression(LinqExpressionsDbContext context)
{
var startTime = DateTimeOffset.Parse("2019-08-04");
// We want to build the expression for the "Where" clause
// Example of Query we will eventually run.
// _context.FilmEntities.Where(f => _context.FilmTimeEntities.Any(ft => ft.FilmId == f.Id && ft.StartTime >= startTime));
var fParameter = Expression.Parameter(typeof(FilmEntity), "f"); // f =>
// Create the FilmTime Entities Query
var ftQueryExpression = context.Set <FilmTimeEntity>().AsQueryable().Expression; // _context.FilmTimeEntities
// Build up the Any part of the query from Film Times
var ftParameter = Expression.Parameter(typeof(FilmTimeEntity), "ft"); // ft =>
var ftFilmIdProperty = Expression.Property(ftParameter, "FilmId"); // ft.FilmId
var ftFilmIdEquals = Expression.Equal(ftFilmIdProperty, Expression.Property(fParameter, "Id")); // ft.FilmId == f.Id
var ftStartTimeProperty = Expression.Property(ftParameter, "StartTime"); // ft.StartTime
var ftStartTimeEquals = Expression.GreaterThanOrEqual(ftStartTimeProperty, Expression.Constant(startTime)); // ft.StartTime >= startTime
// Builds up the AND expression
var ftFullQueryExpression = Expression.AndAlso(ftFilmIdEquals, ftStartTimeEquals); // ft => ft.FilmId == f.Id && ft.StartTime >= startTime
// Make the Lambda Expression for Film Time
var ftLambda = Expression.Lambda <Func <FilmTimeEntity, bool> >(ftFullQueryExpression, ftParameter);
// Join in an ANY statement
var anyMethod = typeof(Queryable).GetMethods().FirstOrDefault(method => method.Name == "Any" && method.GetParameters().Count() == 2); // Use reflection to find the ANY method
var anyFilmTimeMethod = anyMethod.MakeGenericMethod(typeof(FilmTimeEntity)); // Any is a generic method, so create a method specific to FilmTimeEntity
var anyCall = Expression.Call(anyFilmTimeMethod, ftQueryExpression, ftLambda); // (f => _context.FilmTimeEntities.Any(ft => ft.FilmId == f.Id && ft.StartTime >= startTime)
return(Expression.Lambda <Func <FilmEntity, bool> >(anyCall, fParameter));
}
public abstract IQueryable <TEntity> GetExampleQuery(LinqExpressionsDbContext context);
protected abstract Expression <Func <TEntity, bool> > GetExpression(LinqExpressionsDbContext context);
public HomeController(LinqExpressionsDbContext context)
{
_context = context;
}
