Learn the top ten threats that are present in modern web-application development and how to protect your business from them.
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_Archi
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_Archi
Do more with
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:
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);
Hi
Both suggestions returned errors
Operator '??' cannot be applied to operands of type 'System.DateTime' and 'System.DateTime
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()
}
Or,
DateTime? DateToSelect = (from p in db.Archived
where p.TestDate is not null
select p.TestDate).Max()
DateTime? DateToSelect = (from p in db.Archived
select new {
TstDate = (DateTime?) p.TestDate).Single().Max()
}
Or,
DateTime? DateToSelect = (from p in db.Archived
where p.TestDate is not null
select p.TestDate).Max()
Try this as well:
DateTime? DateToSelect = (from p in db.Archived
select new {
TstDate = (DateTime?) p.TestDate;
}) ?? (object)DBNull.Value;
DateTime? DateToSelect = (from p in db.Archived
select new {
TstDate = (DateTime?) p.TestDate;
}) ?? (object)DBNull.Value;
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()
}
Thanks
John
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()
}
Thanks
John
Hi johngrant_ks;
To your question:
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.
I am saauming this is Linq to SQL and .Net 4.0.
Fernando
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();
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()
}
DateTime? DateToSelect = (from p in db.Archived
select new {
TstDate = ((DateTime?) p.TestDate).Single().Max()
}
Sorry, that last line in my post should have read as follows:
I am assuming this is Linq to SQL and .Net 4.0.
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()
});
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
DateTime? DateToSelect = (from p in db.Archived
select new {
TstDate = ((DateTime?) p.TestDate).Single().Max()
});
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();
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();
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
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
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
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
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_Archi
lblDateLastDrawn.Text = DateToSelect.ToString();
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_Archi
lblDateLastDrawn.Text = DateToSelect.ToString();
Hi johngrant_ks;
You can use one of the following Linq syntax to get the wanted results.
Fernando
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);
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);
}
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
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();
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();
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:
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
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;
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial