how to query nullable datetime in linq

Question

I am calling the following query, to display only latest date from fieldname 'UploadDate', however I am experiencing exception error such as:

The entity or complex type 'cdwModel.database_BD' cannot be constructed in a LINQ to Entities query.","ExceptionType":"System.NotSupportedException","StackTrace":

Code:

public IEnumerable<database_BD> GetDate()
    {
        var data = (from c in db.database_BD
           select new database_BD()
           {
               UploadDate = c.UploadDate

           }).ToList().OrderByDescending(c => c.UploadDate).Take(1);

         return data;
    }

database_BD model class:

public partial class database_BD
 {
    public Nullable<System.DateTime> UploadDate { get; set; }
 }

Working solution:

public DateTime? GetDate()
    {
        return data = db.database_BD.Select(d => d.UploadDate)
                     .OrderByDescending(c => c)
                     .FirstOrDefault();
    }

Is this Linq-To-Sql or Entity Framework (they are two separate frameworks)? — Erik Philips, Apr 14 '14 at 16:35
apology for the confusion, the data model was created using entity framework. sorry. — user3070072, Apr 14 '14 at 16:45

crthompson · Accepted Answer · 2014-04-14T20:13:02.533

4

If you just want to display the latest date from UploadDate, then you dont need to create a new database object.

In this example data will be a single date value, or null if there are no records:

var data = db.database_BD.Select(d => d.UploadDate)
                         .OrderByDescending(c => c)
                         .FirstOrDefault();

If you need to return a database_BD object, you'd remove the select which changes the output:

public database_BD GetDate()
{
    var data = db.database_BD.OrderByDescending(c => c.UploadDate)
                             .FirstOrDefault(a => a.UploadDate.HasValue);
     return data;
}

That will give you the newest database_BD object in your table.

edited Apr 14 '14 at 20:13

answered Apr 14 '14 at 16:35

crthompson

15,653
6
58
80

Thank you for your feedback. I have already tried this approach and this code gives me the -- Cannot implicitly convert type 'System.DateTime?' to 'System.Collections.Generic.IEnumerable -- error, on the return data; line code. – user3070072 Apr 14 '14 at 16:39
Oh.. you have already defined `data` previously? If you use my code as it is, you wont get that error. Perhaps your problem is that your code says it wants an enumerable of `API_09.database_BD`, but you only want to pull the date. – crthompson Apr 14 '14 at 16:43
1

@user3070072 if you can change your method to only return one object use what i have posted above, otherwise go with Erik's answer – crthompson Apr 14 '14 at 20:18
@user3070072, did you find your answer? – crthompson Apr 16 '14 at 09:33
1

Many thanks for your response. I manage to get the code to work by changing the swapping couple of code. Thanks – user3070072 Apr 16 '14 at 15:26

Erik Philips · Answer 2 · 2014-04-14T19:08:25.270

4

Since your field is nullable you probablye want:

IEnumerable<database_BD> data = db.database_BD
  .Where(d => d.UpdateDate.HasValue)
  .OrderByDescending(d => d.UpdateDate.Value)
  .Take(1);

updated per your change in the question

edited Apr 14 '14 at 19:08

answered Apr 14 '14 at 16:38

Erik Philips

53,428
11
128
150

hi, I am sorry to inform, but the code above gives error --- Cannot implicitly convert type 'System.DateTime?' to 'System.Collections.Generic.IEnumerable -- error, on the return data; line code. Does this issue indicate, i would need to parse the uploadDate fieldname? – user3070072 Apr 14 '14 at 16:44
1

I'm am extremely confident that my code example would not throw an exception unless you changed it. I've explicitly set the type for data, so unless your `UpdateDate` is not a `Nullable` it should work exactly as I posted. – Erik Philips Apr 14 '14 at 16:46
1

A `FirstOrDefault` wouldnt return an enumerable Erik, it would return the object. Or am i missing something? – crthompson Apr 14 '14 at 18:46
The question was definitely ambiguous, did my best to answer it accordingly and of course as I changed to meet the more precise question it broke it. – Erik Philips Apr 14 '14 at 19:08

how to query nullable datetime in linq

2 Answers2