Sharp2b
asked on
how to filter out rows where a date field is null using LINQ
Hi,
While still trying to learn some basic LINQ, I seem to run into "difficult" cases all the time.
I want to filter my data in a DataTable to show only the rows where a certain date field is NOT NULL (well, that would be what I would look for with Oracle SQL)
Once I get the not null query working, I want to expand to showing only the dates in a given quarter, so any hint on that would be extremely valuable as well.
The code shows my last effort but now I had to give up in solving this myself
Thanks a lot!
While still trying to learn some basic LINQ, I seem to run into "difficult" cases all the time.
I want to filter my data in a DataTable to show only the rows where a certain date field is NOT NULL (well, that would be what I would look for with Oracle SQL)
Once I get the not null query working, I want to expand to showing only the dates in a given quarter, so any hint on that would be extremely valuable as well.
The code shows my last effort but now I had to give up in solving this myself
Thanks a lot!
string caseOwnerQuery;
switch (supportSpecialist.Text)
{
case "All":
caseOwnerQuery = "";
break;
default:
caseOwnerQuery = supportSpecialist.Text;
break;
}
IEnumerable<DataRow> filtered = (from data in _fullTable.AsEnumerable()
where data.Field<string>("Case Owner") == caseOwnerQuery
&& data.Field<DateTime>("Case Open Date") != Convert.DBNull
select data);
ASKER
Thanks but I'm sorry to say that I think I tried that as well.
Anyway, trying again and I get the compile error:
Operator '!=' cannot be applied to operands of type 'System.DateTime' and 'System.DBNull'
Date types always confuse me as they never seem to behave logic to me...
Anyway, trying again and I get the compile error:
Operator '!=' cannot be applied to operands of type 'System.DateTime' and 'System.DBNull'
Date types always confuse me as they never seem to behave logic to me...
Hi Sharp2b;
Seeming there is no Convert.DBNull unless you have created one use the DBNull.Value in its place as shown in the code snippet below.
Fernando
Seeming there is no Convert.DBNull unless you have created one use the DBNull.Value in its place as shown in the code snippet below.
Fernando
IEnumerable<DataRow> filtered = (from data in _fullTable.AsEnumerable()
where data.Field<string>("Case Owner") == caseOwnerQuery
&& data.Field<DateTime>("Case Open Date") != DBNull.Value
select data);
IEnumerable<DataRow> filtered = (from data in _fullTable.AsEnumerable()
where data.Field<string>("Case Owner") == caseOwnerQuery
&& (object)data.Field<DateTime>("Case Open Date") != null
select data);
ASKER
Hi,
and thanks for your suggestions.
Fernando,
I don't see any difference in the snippet you posted compared to the previous one...maybe you intended to copy something else?
Rahu
This was slightly better but then I get a run-time error:
{"Cannot cast DBNull.Value to type 'System.DateTime'. Please use a nullable type."}
Could this be because there are actually null values in the field? Well, that's the reason I want to filter those records out in the first place. Hmmm...maybe I should try to get rid of those in my initial select when I retreive teh DataTable but I want to be able to view these as well so I would rather do it in the LINQ statement.
and thanks for your suggestions.
Fernando,
I don't see any difference in the snippet you posted compared to the previous one...maybe you intended to copy something else?
Rahu
This was slightly better but then I get a run-time error:
{"Cannot cast DBNull.Value to type 'System.DateTime'. Please use a nullable type."}
Could this be because there are actually null values in the field? Well, that's the reason I want to filter those records out in the first place. Hmmm...maybe I should try to get rid of those in my initial select when I retreive teh DataTable but I want to be able to view these as well so I would rather do it in the LINQ statement.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
IEnumerable<DataRow> filtered = (from data in _fullTable.AsEnumerable()
where data.Field<string>("Case Owner") == caseOwnerQuery
&& data.Field<DateTime?>("Cas e Open Date") != null
select data);
In this i used data.Field<DateTime?> which is nullable type so now you can compare it with null.
I assume that blank DateTimefield contain DBnull
where data.Field<string>("Case Owner") == caseOwnerQuery
&& data.Field<DateTime?>("Cas
select data);
In this i used data.Field<DateTime?> which is nullable type so now you can compare it with null.
I assume that blank DateTimefield contain DBnull
Hi Sharp2b;
In the code snippet below the first where is the one I posted and the second is yours, look at the very end of the statement.
Fernando
In the code snippet below the first where is the one I posted and the second is yours, look at the very end of the statement.
Fernando
where data.Field<string>("Case Owner") == caseOwnerQuery && data.Field<DateTime>("Case Open Date") != DBNull.Value
where data.Field<string>("Case Owner") == caseOwnerQuery && data.Field<DateTime>("Case Open Date") != Convert.DBNull
ASKER
YES!!!
Thanks a lot, that did the trick!
I think I tried to user at some point which didn't work as the field was defined as a Date in the DataTable but of course, the object type works.
Thanks a lot, that did the trick!
I think I tried to user at some point which didn't work as the field was defined as a Date in the DataTable but of course, the object type works.
ASKER
Sorry about that Fernando, that was also the same as the initial suggestion from Rahu.
He found a way that works so I already accepted his solution.
He found a way that works so I already accepted his solution.
As you selected correct answer data.Field<object>("Case Open Date") != null
but that is not type safe. It is correct way if your column contain any type of data. But as per your question that column contain datetime field so
data.Field<DateTime?>("Cas e Open Date") != null nullable type. This is new functinoality in .NET Framework 2.0
but that is not type safe. It is correct way if your column contain any type of data. But as per your question that column contain datetime field so
data.Field<DateTime?>("Cas
ASKER
jinal,
thanks a lot! I had seen this ? operator when searching the net but didn't understand it.
This initially seemed to work but when I tried the same thing on another filed, which as far as I can tell should be identical to the other date field, I get a run-time error:
"Specified cast is not valid."
I don't understand this as the data fields are identical with similar content.
thanks a lot! I had seen this ? operator when searching the net but didn't understand it.
This initially seemed to work but when I tried the same thing on another filed, which as far as I can tell should be identical to the other date field, I get a run-time error:
"Specified cast is not valid."
I don't understand this as the data fields are identical with similar content.
Open in new window