2

I am trying to write a simple linq select query, where in the where clause the value is taken from a text box. I am having problem in case of datetime values. Now this works:

 var rawData = contextSearch.GetType().GetProperty(TableName).GetValue(contextSearch, null);
 truncatedData = ((IQueryable<object>)rawData).Where(columnName + ">=@0", DateTime.Parse(txtCrudSearch.Text)).ToList();

this also works for <=, but I need to find records for exactly the given date. So, when I try this:

truncatedData = ((IQueryable<object>)rawData).Where(columnName + "=@0", DateTime.Parse(txtCrudSearch.Text)).ToList();

It doesn't work. Why? How can I get this to work? FYI: columnname is taken from combobox dynamically and I'm using entity framework.

Paradox
  • 129
  • 1
  • 3
  • 14
  • It doesn't show any error but it also doesn't return anything. I get no records when I run it. – Paradox Feb 09 '15 at 14:07
  • Does the date value you are parsing out of the textbox have a time component? The query you are using will only work if the date exactly matches the value in the database (including the time). – Ash8087 Feb 09 '15 at 14:07
  • I never used LINQ like this, but shouldn't it be, .Where(columnName + "==@0", – Dawood Awan Feb 09 '15 at 14:07
  • that doesn't work either @DawoodAwan – Paradox Feb 09 '15 at 14:08
  • @Ash8087 yes, it has time component, in that case what should I do? I am giving the time in searchvalue -exactly like database, it doesn't seem to work in that case either. – Paradox Feb 09 '15 at 14:10
  • Are you trying to get records by day / month / year? There's a very little possibility that some record will match the exact full `DateTime` provided. – haim770 Feb 09 '15 at 14:12
  • Why aren't you using expression trees to build the dynamic queries? – tdbeckett Feb 09 '15 at 14:13
  • @haim770 I want to find the records according to the date, so I tried to write the query with contains before, but couldn't get it to work with date value. – Paradox Feb 09 '15 at 14:15
  • You could possibly change query to compare int values instead of dates for testing purposes. This way, you can see if the issue is dateTime parsing / in the query itself? – Niels Filter Feb 09 '15 at 14:16
  • @DawoodAwan No, that's literal SQL code used by dynamic-linq and SQL uses one `=` for equality. – juharr Feb 09 '15 at 14:18
  • @filtered I am using the same query for int, string values. I got them to work except date value. The query is alright. – Paradox Feb 09 '15 at 14:19
  • I see, try write out the query you want directly to SQL (or whatever database you're using), and see get the right results that way first, then go back to the dynamic query. Correct me if I'm wrong, but it seems you're comparing object to datetime, could that be the issue? – Niels Filter Feb 09 '15 at 14:23
  • It should work so long as the times are identical. Do the values in your database also have time components? It may be that those values have a more granular time value (e.g. a millisecond value), but the value getting parsed (from your textbox) does not. You could just search on the Date part (ignoring the time), if this turns out to be the problem. To do this, you could convert the DateTime on each side of the expression to Midnight. – Ash8087 Feb 09 '15 at 14:31

1 Answers1

0

If you only want to compare the date and ignore time, try this:

var parsedDt = DateTime.Parse(txtCrudSearch.Text);
var nextDay = parsedDt.AddDays(1);

truncatedData = ((IQueryable<object>)rawData)
                 .Where(columnName + ">= @0 && " + columnName + " < @1", parsedDt, nextDay)
                 .ToList();

(This solution is based on @algreat answer)

Community
  • 1
  • 1
haim770
  • 48,394
  • 7
  • 105
  • 133