Assign/Compare DateTime value from Database with DateTime value from C# in Linq query

Question

I have following class:

public class Top10Date
    {
        [Key]
        public int DateId { get; set; }
        public DateTime Date { get; set; }        
    }

Which is associated with Top10Date table in database. I am trying to do a comparison and assign values to latestDate in controller class:

public ActionResult Index(string date, DateTime? latestDate)
    {
        var topDate = db.Top10Dates;

        if (!latestDate.HasValue)
            latestDate = (DateTime?)(topDate.OrderByDescending(d => d.Date).FirstOrDefault());


       if (date.Value == "PreviousDate")
        {
            latestDate = (DateTime?)(topDate.Where(d => d.Date < latestDate.Date).OrderByDescending(d => d.Date).FirstOrDefault());                
        }

        if (date.Value == "NextDate")
        {
            latestDate = (DateTime?)(topDate.Where(d => d.Date > latestDate.Date).OrderBy(d => d.Date).FirstOrDefault());
        }

        .... 

        ViewBag.latestDate = latestDate

        return View();
    }

But it is giving me all sort of errors. I have tried other things too but none are working:

latestDate = topDate.OrderByDescending(d => d.Date).FirstOrDefault();
latestDate.Value = topDate.OrderByDescending(d => d.Date).FirstOrDefault();

latestDate = topDate.Where(d => d.Date > latestDate.Value.Date).OrderBy(d => d.Date).FirstOrDefault();

Also how can i get DateId from the Database? I tried this it is now working.

var dateId = (DateTime?)top10Date.Where(d => d.Date == latestDate.Value.Date).Select(x => x.DateId).FirstOrDefault();

StanK · Accepted Answer · 2012-08-28 04:42:21Z

1

try

if (!latestDate.HasValue)
    latestDate = (DateTime?)(topDate.OrderByDescending(d => d.Date).Select( x => x.Date).FirstOrDefault());    

if (date == "PreviousDate")
{
    latestDate = (DateTime?)(topDate.Where(d => d.Date < latestDate.Date).OrderByDescending(d => d.Date).Select(x => x.Date).FirstOrDefault());
}

if (date == "NextDate")
{
    latestDate = (DateTime?)(topDate.Where(d => d.Date > latestDate.Date).OrderBy(d => d.Date).Select(x => x.Date).FirstOrDefault());
}

Your query on topDate is returning an instance of Top10Date which cannot be automatically converted to DateTime?. You can use the linq method Select() to have your query return something different - in this case the Date property in Top10Date

answered Aug 28, 2012 at 4:42

StanK

4,7702 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

NoviceMe Over a year ago

Thanks for the query i am trying this! But am stuck at one more point. There is another place where latestDate is used: var topTitles = topsong.Where(s => s.DateId == latestDate).ToList(); This is also comparing Database date from another table where DateId is foreign key to. Any ideas how i can use latestDate here?

NoviceMe Over a year ago

I tried to get DateId using this: var dateId = (DateTime?)top10Date.Where(d => d.Date == latestDate.Value.Date).Select(x => x.DateId).FirstOrDefault(); But this is now working?

NoviceMe Over a year ago

+1 from me for the help. I would give more but am not able to do that :) . Thanks a lot again!

StanK Over a year ago

You are welcome. You should probably read up on linq - this is the part of the .Net framework where all magic around Select, Where, OrderBy etc lives.

NoviceMe · Accepted Answer · 2012-08-28 05:26:22Z

Here is the working code if someone else had similar problem. It took me whole day to figure this out. And i cant thank StanK enough for showing me the right path:

if (!latestDate.HasValue)
            {
                latestDate = (DateTime?)(topDate.OrderByDescending(d => d.Date).Select(x => x.Date).FirstOrDefault());
            }


           if ( date == "PreviousDate")
            {
                latestDate = (DateTime?)(topDate.Where(d => d.Date < latestDate).OrderByDescending(d => d.Date).Select(x => x.Date).FirstOrDefault());                
            }

            if (date == "NextDate")
            {
                latestDate = (DateTime?)(topDate.Where(d => d.Date > latestDate).OrderBy(d => d.Date).Select(x => x.Date).FirstOrDefault());
            }

Also here is how to get the DateId of the latestDate:

    int dateId = topDate.Where(d => d.Date == latestDate).Select(x => x.DateId).FirstOrDefault();

Collectives™ on Stack Overflow

Assign/Compare DateTime value from Database with DateTime value from C# in Linq query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related