score:3
You could try something like that
public static IQueryable<TElement> IsDateBetween<TElement>(this IQueryable<TElement> queryable,
Expression<Func<TElement, DateTime>> fromDate,
Expression<Func<TElement, DateTime>> toDate,
DateTime date)
{
var p = fromDate.Parameters.Single();
Expression member = p;
Expression fromExpression = Expression.Property(member, (fromDate.Body as MemberExpression).Member.Name);
Expression toExpression = Expression.Property(member, (toDate.Body as MemberExpression).Member.Name);
var after = Expression.LessThanOrEqual(fromExpression,
Expression.Constant(date, typeof(DateTime)));
var before = Expression.GreaterThanOrEqual(
toExpression, Expression.Constant(date, typeof(DateTime)));
Expression body = Expression.And(after, before);
var predicate = Expression.Lambda<Func<TElement, bool>>(body, p);
return queryable.Where(predicate);
}
usage
DataContext.EventHistories.WhereDateBetween(h => h.FromDate, h => h.ToDate, dateInTheMiddle));
but it's quite complicated to do
var myDate = DateTime.Now();
var result = DataContext.EventHistories.Where(m => m.FromDate >= myDate && m.ToDate<=myDate );
EDIT
Well, to simulate the DateTime.Date
, you could do :
var myDate = DateTime.Now.Date();
var res = histories.AsQueryable()
.Where(m =>
EntityFunctions.CreateDateTime(m.FromDate.Year, m.FromDate.Month, m.FromDate.Day, 0, 0, 0) >= DateTime.Now &&
EntityFunctions.CreateDateTime(m.ToDate.Year, m.ToDate.Month, m.ToDate.Day, 0, 0, 0) <= DateTime.Now)
Or create an interface
public interface IFromDateToDate
{
DateTime FromDate { get; set; }
DateTime ToDate { get; set; }
}
and an extension method
public static IQueryable<T> WhereDatesBetween<T>(this IQueryable<T> queryable, DateTime myDate) where T : IFromDateToDate
{
myDate = myDate.Date;
return queryable.Where(m =>
EntityFunctions.CreateDateTime(m.FromDate.Year, m.FromDate.Month, m.FromDate.Day, 0, 0, 0) >= myDate &&
EntityFunctions.CreateDateTime(m.FromDate.Year, m.FromDate.Month, m.FromDate.Day, 0, 0, 0) <= myDate);
}
usage :
DataContext.EventHistories.WhereDatesBetween(dateInTheMiddle));
score:-1
Why bother with all that magic? Why not just do this?
if (date >= startDate and date <= endDate) {
}
score:1
LinQ to Entities DOES support DateTime - i use it in a project and there's no problem about it:
var now = DateTime.Now;
var expiredEntities = entities.Repositories.Where(repository => repository.ExpiryDate < now).ToList();
score:0
Now, if I follow this correct, you would really like to write:
from item in MyTable
where FromDate <= Item.Somedate.Date
&& Item.Somedate.Date <= ToDate
select item
But, you can't because the Date
property of DateTime isn't supported in LINQ to entities.
Similarly, you can't write:
from item in MyTable
where FromDate <= Item.SomeDate
&& Item.SomeDate <= ToDate
select item
because, I assume, ToDate
is set to midnight, and if Item.SomeDate
has a time, it will be after ToDate
, even if it's on ToDate.
If that's the case, the proper solution, which is the common practice, is to make the upper limit an exclusive value (i.e, the first thing that isnot part of the set). Hence:
toDate = toDate.Date.AddDay(1);
from item in MyTable
where FromDate <= Item.SomeDate
&& Item.SomeDate < ToDate
select item
Source: stackoverflow.com
Related Articles
- Expression tree to know if a date is between 2 dates in c#
- C# Linq Where Date Between 2 Dates
- Date range falling between two dates in a LINQ query
- Order list by Date with split between future as past dates
- Code to apply expression tree directly to List
- Adding missing dates to list between maximum and minimum date in LIST using LINQ
- Need to build expression tree for max date value
- GroupJoin by date where date in second list between two dates from first
- I'd like to obtain record between 2 dates , but Date column is in string format in db. Using linq query
- LINQ expression to generate a list of days between two dates
- How to know if a DateTime is Between two Dates in Entity Framework Core
- Match data from two list<T> where date from one list falls between dates from second list
- convert sql to ling group by and average between 2 dates when end date can be null
- how to find date range between two dates in days format using linq c#
- Date falls between two dates and matches one date from table in Linq
- An expression tree lambda may not contain a null propagating operator
- Cannot use a lambda expression as an argument to a dynamically dispatched operation without first casting it to a delegate or expression tree type
- Mutating the expression tree of a predicate to target another type
- How to convert an expression tree to a partial SQL query?
- How to create LINQ Expression Tree to select an anonymous type
- How do I create an expression tree calling IEnumerable<TSource>.Any(...)?
- Difference between expression lambda and statement lambda
- How to search between two dates in LINQ to Entity?
- How Build Lambda Expression Tree with multiple conditions
- How to get dates between two dates in C#
- Difference between Query Expression and Method Expression in LINQ?
- How do I create a Linq expression tree with an F# lambda?
- Error: An expression tree may not contain a dynamic operation
- Get the earliest date in a LINQ expression
- Print out Linq Expression Tree Hierarchy
- LINQ to SQL where in (lambda syntax)
- Right way of logging in async methods
- How to apply between clause on two columns in Linq query while searching from datatable C#?
- C# - combining collections question
- What should be the type of variable?
- Is OData intended for use within Government and Financial envrionments? What security precautions do I need?
- Extract sql query from LINQ expressions
- LINQ lambda OrderByDescending on group by key.count() returning list of string of group by key for chart legend sorting
- Using Linq in .net framework 2.0?
- LINQ query left joining two tables with concatenation
- How to Customize Format HTTP JSON POST Request in VB
- How can I improve my filter of two lists?
- get's ArgumentException only when doing async call to database in c# using linq QueryableExpression
- How to get something like sql '%test%words%' in linq
- Non-Nullable value type issue when LEFT JOIN in Linq
- PagedList in asp.net mvc 3 not returning anything to any page after 1
- Is FirstOrDefault/First and OrderByDescending, quicker than LastOrDefault/Last and OrderBy?
- Performance difference between Linq Select and ToList
- ASP.net LINQ on DataView to use Like query
- c# Remove nodes from xml file irrespective of hierarchy