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: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
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();
Source: stackoverflow.com
Related Query
- 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
More Query from same tag
- Get first value from table with where either have the value match or null with LINQ
- Creating comma separated string from custom collection
- Parsing multiple objects using Jobject in Newtonsoft.Json.Linq
- Creating a linq expression dynamically or parsing the string value of the expression
- How to query all tables that implement an interface
- Cannot convert WhereListIterator
- LINQ query on datatable, join multiple selects
- convert string to DateTime in linq query with where clause?
- Use LINQ in order to select a list by matched sublist values in C#
- How to rotate data by converting rows to columns using LINQ
- GroupBy Date in LINQ C#
- LINQ and CASE Sensitivity
- Difference between OfType<>() and checking type in Where() extension
- c# linq to xml , find nested element by it's attribute
- Can't find property or field on a dynamic object
- c# Lambda Expression built with LinqKit does not compile
- How to change the order of the list?
- Creating custom DTO class results in duplicate JSON properties
- Remove items from a List which are in another List by a specific property?
- Can not join tables with LINQ
- NHibernate & Linq - "could not resolve property"
- Error while insert/update records in asp.net core
- Exclude list items that contain values from another list
- Any() in a Chained Linq expression
- How to optimize this linq to objects query?
- query problem with linq: Unable to cast object
- How would I write this dynamic linq query?
- How can I select one of two objects depending if one is null or not with a LinQ to Entities query.
- LINQ Query using if condition and && Operation
- How to group LINQ to Entities results by enum and date?