Solved

how to filter out rows where a date field is null using LINQ

Posted on 2009-07-01
12
958 Views
Last Modified: 2013-11-11
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!
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);

Open in new window

0
Comment
Question by:Sharp2b
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 9

Expert Comment

by:Rahul Goel
ID: 24755952

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);

Open in new window

0
 

Author Comment

by:Sharp2b
ID: 24755992
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...
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24756018
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
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);

Open in new window

0
 
LVL 9

Expert Comment

by:Rahul Goel
ID: 24756025

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);

Open in new window

0
 

Author Comment

by:Sharp2b
ID: 24756100
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.
0
 
LVL 9

Accepted Solution

by:
Rahul Goel earned 500 total points
ID: 24756164

IEnumerable<DataRow> filtered = (from data in _fullTable.AsEnumerable()

where data.Field<string>("Case Owner") == caseOwnerQuery

&& data.Field<object>("Case Open Date") != null

select data);

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 15

Expert Comment

by:jinal
ID: 24756207
IEnumerable<DataRow> filtered = (from data in _fullTable.AsEnumerable()
                                         where data.Field<string>("Case Owner") == caseOwnerQuery
                                         && data.Field<DateTime?>("Case 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
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 24756208
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
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

Open in new window

0
 

Author Closing Comment

by:Sharp2b
ID: 31598837
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.
0
 

Author Comment

by:Sharp2b
ID: 24756294
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.
0
 
LVL 15

Expert Comment

by:jinal
ID: 24756297
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?>("Case Open Date") != null  nullable type. This is new functinoality in .NET Framework 2.0

0
 

Author Comment

by:Sharp2b
ID: 24757276
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now