Linq Query : Translate Local Variables reference
-
Hello ! I'm using the information from this MSDN blog : http://blogs.msdn.com/b/mattwar/archive/2007/08/01/linq-building-an-iqueryable-provider-part-iii.aspx[^] to translate local variable references in linq queries. Everything works very well except the case when I have a DateTime variable inside the query that I have to translate . This is my case : On my database I have the table orders where dt column have Date Type and store only the date. Now if I have this query :
DateTime dt1 = Convert.ToDateTime("01/01/2012");
var query = db.Orders.Where(c => c.dt == dt1);If I try to translate , the returned expression is :
SELECT * FROM (SELECT * FROM Orders) AS T WHERE (dt = '01/01/2012 12:00:00
AM')As you can see the translated query contains the time at the end , and my query everytime return no records. What can I do that in translated query the value to be only the date without the time.? I try many variants , I format the variable only to date format , but it's the same thing. Thank you !
-
Hello ! I'm using the information from this MSDN blog : http://blogs.msdn.com/b/mattwar/archive/2007/08/01/linq-building-an-iqueryable-provider-part-iii.aspx[^] to translate local variable references in linq queries. Everything works very well except the case when I have a DateTime variable inside the query that I have to translate . This is my case : On my database I have the table orders where dt column have Date Type and store only the date. Now if I have this query :
DateTime dt1 = Convert.ToDateTime("01/01/2012");
var query = db.Orders.Where(c => c.dt == dt1);If I try to translate , the returned expression is :
SELECT * FROM (SELECT * FROM Orders) AS T WHERE (dt = '01/01/2012 12:00:00
AM')As you can see the translated query contains the time at the end , and my query everytime return no records. What can I do that in translated query the value to be only the date without the time.? I try many variants , I format the variable only to date format , but it's the same thing. Thank you !
-
Try:
var query = db.Orders.Where( c => c.dt.Date == dt1.Date);
This should effectively cause the times to be the same for both dates.
Doesn't work. The problem is that however the query is formatted , the return translated query will be :
SELECT * FROM (SELECT * FROM Orders) AS T WHERE (dt = '01/01/2012 12:00:00
AM')and so the dt column from database is already a date type and only hold the date . The problem is not how the query is formatted , but how is translated with the methods from the link I posted.It seems that a datetime value (even formatted with only the date part , is translated to Date + Default time "12.00.00". So what modifications should made on the code at the link that I posted in order to modify only for date types.
-
Doesn't work. The problem is that however the query is formatted , the return translated query will be :
SELECT * FROM (SELECT * FROM Orders) AS T WHERE (dt = '01/01/2012 12:00:00
AM')and so the dt column from database is already a date type and only hold the date . The problem is not how the query is formatted , but how is translated with the methods from the link I posted.It seems that a datetime value (even formatted with only the date part , is translated to Date + Default time "12.00.00". So what modifications should made on the code at the link that I posted in order to modify only for date types.
-
Hello ! I'm using the information from this MSDN blog : http://blogs.msdn.com/b/mattwar/archive/2007/08/01/linq-building-an-iqueryable-provider-part-iii.aspx[^] to translate local variable references in linq queries. Everything works very well except the case when I have a DateTime variable inside the query that I have to translate . This is my case : On my database I have the table orders where dt column have Date Type and store only the date. Now if I have this query :
DateTime dt1 = Convert.ToDateTime("01/01/2012");
var query = db.Orders.Where(c => c.dt == dt1);If I try to translate , the returned expression is :
SELECT * FROM (SELECT * FROM Orders) AS T WHERE (dt = '01/01/2012 12:00:00
AM')As you can see the translated query contains the time at the end , and my query everytime return no records. What can I do that in translated query the value to be only the date without the time.? I try many variants , I format the variable only to date format , but it's the same thing. Thank you !
dilkonika wrote:
What can I do that in translated query the value to be only the date without the time.?
There is no point in time that can be defined without time. The DateTime object is a decimal; the integer part holds the days, the fractional part holds the time.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Hello ! I'm using the information from this MSDN blog : http://blogs.msdn.com/b/mattwar/archive/2007/08/01/linq-building-an-iqueryable-provider-part-iii.aspx[^] to translate local variable references in linq queries. Everything works very well except the case when I have a DateTime variable inside the query that I have to translate . This is my case : On my database I have the table orders where dt column have Date Type and store only the date. Now if I have this query :
DateTime dt1 = Convert.ToDateTime("01/01/2012");
var query = db.Orders.Where(c => c.dt == dt1);If I try to translate , the returned expression is :
SELECT * FROM (SELECT * FROM Orders) AS T WHERE (dt = '01/01/2012 12:00:00
AM')As you can see the translated query contains the time at the end , and my query everytime return no records. What can I do that in translated query the value to be only the date without the time.? I try many variants , I format the variable only to date format , but it's the same thing. Thank you !
If I understand correctly, your issue is that your database only has a DATE value (01/01/2015) But when you try to query the database, the matching variable has the time included (01/01/2015 12:00:00 AM) so the query never finds any matches??? Try this:
DateTime dt1 = Convert.ToDateTime("01/01/2012");
var query = db.Orders.Where(c => c.dt.ToString() == dt1.ToString("d"));