Linq MAX returns null on DateTime Type

johngrant_ks
johngrant_ks used Ask the Experts™
on
Hi

System.Nullable<DateTime> DateToSelect = (from p in db.Archived select p.TestDate).Max();

I'm trign to return a date from a table but if there are no records in the table i get the Error

The null value cannot be assigned to a member with type System.DateTime which is a non-nullable value type.

How do i stop this null error if there are no records matching the criteria

I also have the problem when there are records in the table and i place a where statement in the LINQ i get the same error even if there are records that meet the criteria.

e.g.
System.Nullable<DateTime> WinningDateToSelect = (from p in db.qtxt_Competitions_Archived_Winners where p.ID = 1 select p.WinningDate).Max();
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could try using a null-coalescing operator (??).

http://msdn.microsoft.com/en-us/library/ms173224%28v=VS.100%29.aspx

For example, see the code snippet below:
System.Nullable<DateTime> DateToSelect = (from p in db.Archived select p.TestDate).Max() ?? new DateTime(1900, 1, 1);

Or,

DateTime? DateToSelect = (from p in db.Archived select p.TestDate).Max() ?? new DateTime(1900, 1, 1);

Open in new window

Author

Commented:
Hi

Both suggestions returned errors

Operator '??' cannot be applied to operands of type 'System.DateTime' and 'System.DateTime
OK.  You could try the following:

DateTime? DateToSelect = (from p in db.Archived
                       select new {
                               TstDate = (DateTime?) p.TestDate).Single().Max() ?? new DateTime(1900, 1, 1);
                       }

Or,

DateTime? DateToSelect = (from p in db.Archived
                                            where p.TestDate is not null
                                            select p.TestDate).Max()
                       
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Try this as well:

DateTime? DateToSelect = (from p in db.Archived
                       select new {
                               TstDate = (DateTime?) p.TestDate;
                       }) ?? (object)DBNull.Value;

Author

Commented:
Ok used this one but still gives the same error if there are no recods in the table

DateTime? DateToSelect = (from p in db.Archived
                                            where p.TestDate is not null
                                            select p.TestDate).Max()
Rewrote
DateTime? DateToSelect = (from p in db.Archived where p.TestDate != null select p.TestDate).Max();

Also works with the second condition included
DateTime? DateToSelect = (from p in db.Archived where p.TestDate != null && ID = 1 select p.TestDate).Max();


This one i couldnt work out the syntax mistakes Could you have another look please :)

DateTime? DateToSelect = (from p in db.Archived
                       select new {
                               TstDate = (DateTime?) p.TestDate).Single().Max() ?? new DateTime(1900, 1, 1);
                       }

Thanks
John
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi johngrant_ks;

To your question:

I'm trign to return a date from a table but if there are no records in the table i get the Error The null value cannot be assigned to a member with type System.DateTime which is a non-nullable value type. How do i stop this null error if there are no records matching the criteria

The statement you have posted, shown below, will correctly handle a table which has no records or one that has data in it but the TestDate column has no values in any row, which will return a null. This is also true if the query had a where clause in it. Although the sample query you posted with the where clause had a syntax error in it, you had this, where p.ID = 1, and it should have been this where p.ID == 1.

System.Nullable<DateTime> DateToSelect = 
    (from p in db.Archived 
     select p.TestDate).Max();

Open in new window


I am saauming this is Linq to SQL and .Net 4.0.

Fernando
Just missed an opening parenthesis.  I just typed this as I go along.  :-)

DateTime? DateToSelect = (from p in db.Archived
                       select new {
                               TstDate = ((DateTime?) p.TestDate).Single().Max() ?? new DateTime(1900, 1, 1);
                       }
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Sorry, that last line in my post should have read as follows:
I am assuming this is Linq to SQL and .Net 4.0.
Oops.  I think I missed another ending parenthesis.

DateTime? DateToSelect = (from p in db.Archived
                       select new {
                               TstDate = ((DateTime?) p.TestDate).Single().Max() ?? new DateTime(1900, 1, 1);
                       });

Also, have a look at this link.  This might help.

http://weblogs.asp.net/scottgu/archive/2007/09/20/the-new-c-null-coalescing-operator-and-using-it-with-linq.aspx
Also as @FernandoSoto already mentioned, this

Also works with the second condition included
DateTime? DateToSelect = (from p in db.Archived where p.TestDate != null && ID = 1 select p.TestDate).Max();

Should be,

DateTime? DateToSelect = (from p in db.Archived where p.TestDate != null && p.ID == 1 select p.TestDate).Max();

Author

Commented:
Hi FernandoSoto/Alfred1

Alfred, still get errors tring to get your code going you may wan to try doing in VS, it maybe more than just a missing parenthesis.

FernandoSoto, If i delete the rows from the table and try and run it i still get the same error


Problem1.jpg
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi johngrant_ks;

The image resolution is very low and all that can be seen from the characters is pixelation, non readable.

A couple of questions:
What version of Visual Studio, 2008, 2010?
Is this Linq to SQL or Linq to Entity Framework?
Post the actual code and not just the query please?
Is the DateTime field in the database allows nulls?

Fernando

Author

Commented:
You vcan click on the image to see it better

What version of Visual Studio, 2008, 2010? VS 2010
Is this Linq to SQL or Linq to Entity Framework? LINQ to SQL
Is the DateTime field in the database allows nulls? No

This is all the Code
qtxtDataContext db = new qtxtDataContext();
DateTime? DateToSelect = (from p in db.qtxt_Competitions_Archived_Winners select p.WinningDate).Max();

lblDateLastDrawn.Text = DateToSelect.ToString();
Retired
Distinguished Expert 2017
Commented:
Hi johngrant_ks;

You can use one of the following Linq syntax to get the wanted results.

// Linq Query Format
DateTime? DateToSelect1 = ( from p in db.qtxt_Competitions_Archived_Winners 
                            select p.WinningDate
                          ).Max( d => (DateTime?)d); 
                          
// Linq Methid Format
DateTime? DateToSelect2 = db.qtxt_Competitions_Archived_Winners.Max(d => (DateTime?)d.TestDate);

Open in new window


Fernando
OK.  Out of curiosity,  I tried to reproduce this (LINQ to SQL) in my Test Server and it seems to be working fine.  Please note that the datetime column I tested allows null.  


protected void Page_Load(object sender, EventArgs e)
        {
            TestDataContext TestData1 = new TestDataContext();

            DateTime myDate = (DateTime)DateTime.Now;

            //Both of the following works. Max date shown.
            DateTime? DateToSelect = (from p in TestData1.SAMPLEs 
                                      where p.Status_Updated_Date 
                                      select p.Status_Updated_Date).Max();

            DateTime? DateToSelect = (from p in TestData1.SAMPLEs 
                                      where p.Status_Updated_Date != null
                                      select p.Status_Updated_Date).Max();

            //This works. Nothing is shown and no error.
            DateTime? DateToSelect = (from p in TestData1.SAMPLEs 
                                      where p.Status_Updated_Date == null
                                      select p.Status_Updated_Date).Max();

            //I also tried to return zero rows and it works just fine.
            DateTime? DateToSelect = (from p in TestData1.SAMPLEs 
                                      where p.Status_Updated_Date > myDate
                                      select p.Status_Updated_Date).Max();

            Response.Write(DateToSelect);

        }

Open in new window

Author

Commented:
Hi thanks to you both Alfred and Fernando

I'm not sure why it would work on your test server alfred and not on my instance. Maybe Fernando could let us know why, as the solution he has found has fixed my issue.

Thanks Again
John
Oh, I didn't notice in your response that the column you are working on DOES NOT ALLOW NULLS.

The reason why you are getting that error because DateToSelect is set as nullable.

Fernando's solution cast it to nullable DateTime and that is why it works.

Actually if you do the following, it might just work fine.

DateTime DateToSelect = (from p in db.Archived select p.TestDate).Max();

Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Alfred1;

The Nullable type can be assign a value or in the absents of any value a null. So the DateTime? can hold a valid DateTime value or can have a null assigned to it. For example if you have the following:

// A Nullable DateTime variable 
DateTime? dt;

// Then you assign to dt a Nullable type a DateTime value 
// it will have no problem storing that value in dt.
dt = DateTime.Today;

// And of course you can also assign a null value to a Nullable type
dt = null;

Open in new window


So where does the problem come in. The Max operator is defined as follows :

public static TSource Max<TSource>(
      this IQueryable<TSource> source
)

In our case the Max method is expecting a DateTime which by definition has to have a value because objects of this type are NOT Nullable. But when it receives an null object it immediately throws an exception. By casting it to a Nullable type in the Max method it returns it immediately as a null.

I hope that helps.

Fernando
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Sorry johngrant_ks my last post should have had your username on it and not Alfred1.
@FernandoSoto,

I was actually thinking awhile ago if I posted a question.  LOL. :-)

Anyway, Thanks, Mate!  I know it already as I summarized it in ID:35769969 but explanation appreciated.
@FernandoSoto,

Oops.  Wrong ID, it should be ID:35767144  :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial