Avatar of johngrant_ks
johngrant_ks

asked on 

Linq MAX returns null on DateTime Type

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();
.NET ProgrammingASP.NETC#

Avatar of undefined
Last Comment
Alfred A.
Avatar of Alfred A.
Alfred A.
Flag of Australia image

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

Avatar of johngrant_ks
johngrant_ks

ASKER

Hi

Both suggestions returned errors

Operator '??' cannot be applied to operands of type 'System.DateTime' and 'System.DateTime
Avatar of Alfred A.
Alfred A.
Flag of Australia image

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()
                       
Avatar of Alfred A.
Alfred A.
Flag of Australia image

Try this as well:

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

ASKER

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
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

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
Avatar of Alfred A.
Alfred A.
Flag of Australia image

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);
                       }
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Sorry, that last line in my post should have read as follows:
I am assuming this is Linq to SQL and .Net 4.0.
Avatar of Alfred A.
Alfred A.
Flag of Australia image

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
Avatar of Alfred A.
Alfred A.
Flag of Australia image

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();
Avatar of johngrant_ks
johngrant_ks

ASKER

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
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

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
Avatar of johngrant_ks
johngrant_ks

ASKER

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();
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Alfred A.
Alfred A.
Flag of Australia image

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

Avatar of johngrant_ks
johngrant_ks

ASKER

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
Avatar of Alfred A.
Alfred A.
Flag of Australia image

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

Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

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
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Sorry johngrant_ks my last post should have had your username on it and not Alfred1.
Avatar of Alfred A.
Alfred A.
Flag of Australia image

@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.
Avatar of Alfred A.
Alfred A.
Flag of Australia image

@FernandoSoto,

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

The .NET Framework is not specific to any one programming language; rather, it includes a library of functions that allows developers to rapidly build applications. Several supported languages include C#, VB.NET, C++ or ASP.NET.

137K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo