I have a requirement to perform a LINQ to LS query, but the where requirements are not fixed. But I am trying deperately not to have t create many LINQ to LS queries to solve the problem. This probably all sound very confusing, so I shall try to clarify. Below is my code in a method.
There is a method which should return a BindingList based on the properties. Several of these properties are Booleans. If any of these boolean values are false, which is the norm, they will exclude records with a certain status. If they are true, they will select those statuses also.
The second peice of code is a predicate builder which is as hijacked form a very useful article on the net http://www.albahari.com/nutshell/predicatebuilder.aspx. This gives me the ability to do what I need.
public static BindingList<HotelBookingSearch> FindReservations(String searchText, DateTime startDate, DateTime endDate, Int32? excludeBookingId, bool includedCancelled, bool includedClosed, bool includedMaintenance)
{
try
{
using (var uow = ContextsData.DataContext.CreateUnitOfWork())
{
var predicate = PredicateBuilder.True<HotelBooking>();
if(excludeBookingId.HasValue)
predicate = predicate.And(p => p.Id != excludeBookingId.Value);
if (!includedCancelled)
predicate = predicate.And(p => p.Status != (int)Enums.RoomBookingStatus.Cancelled);
if (!includedClosed)
predicate = predicate.And(p => p.Status != (int)Enums.RoomBookingStatus.Closed);
if (!includedMaintenance)
predicate = predicate.And(p => p.Status != (int)Enums.RoomBookingStatus.Maintenance);
predicate = predicate.And(p => p.CheckInDate >= startDate);
predicate = predicate.And(p => p.CheckInDate <= endDate);
predicate = predicate.And(p => p.GuestName.Contains(searchText));
var qry = from b in uow.HotelBookings.Where(predicate)
select new HotelBookingSearch
{
CheckInDate = b.CheckInDate,
CheckOutDate = b.CheckOutDate,
GuestName = b.GuestName,
Id = b.Id,
RateCodeId = b.RateCodeId.HasValue ? b.RateCodeId.Value : 0,
RoomId = b.RoomId,
RoomType = b.RoomType.Description,
Reference = b.Reference,
Status = Messages.GetMessage(b.Status)
};
var list = qry.ToList();
return new BindingList<HotelBookingSearch>(list);
}
}
catch (Exception error)
{
ExceptionHandling.LogProblem(error);
}
return new BindingList<HotelBookingSearch>();
}
public static class PredicateBuilder
{
public static Expression<Func<T, bool>> True<T>() { return f => true; }
public static Expression<Func<T, bool>> False<T>() { return f => false; }
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters);
return Expression.Lambda<Func<T, bool>>
(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters);
return Expression.Lambda<Func<T, bool>>
(Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
}
}
The problem I experience is when I run this code, I receive the following error.
System.NotSupportedException: This expression type is not supported in LightSpeed 3.0
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ConvertToQueryExpression(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteriaCore(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression)
at Mindscape.LightSpeed.Linq.Sqo.Where.ExtractWhereCriteria(MethodCallExpression expression, LinqQueryPlanExpression plan, GroupResultsPlan groupPlan)
at Mindscape.LightSpeed.Linq.Sqo.Where.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)
at Mindscape.LightSpeed.Linq.Sqo.Select.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp)
at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Build(Expression translation, LinqQueryProvider provider)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQuery`1.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at ResortManager.BL.HotelTools.FindReservations(String searchText, DateTime startDate, DateTime endDate, Nullable`1 excludeBookingId, Boolean includedCancelled, Boolean includedClosed, Boolean includedMaintenance) in D:\_My Projects\Resort Manager\Solutions\ResortManager\ResortManager.BL\HotelTools.cs:line 467
But the problem is, I am not sure which bit is not supported. So my questions are, can any tell me which bit is not supported so I can change it. Or can someone suggest a cleaner way to achieve the same task.
Thanks in advance for any and all advise.
