Linq between dates query

I am trying to convert the following sql query as a linq query, however I keep experiencing an error -

Operator '<=' cannot be applied to operands of type 'string' and 'System.DateTime'.

SQL Query:

select top 3 Deal, [property], [event], [Date] from [dbo]. [Database_CRE_Events] 
where (convert(datetime,[Date],103) between '01-May-2015' and '15-May-2015')  and [property] is not NULL 
order by convert(datetime,[Date],103) desc

I believe this is happening because c.Date is a string field from the entity database. I have tried converting the date values to string and datetime to get the following to work, but I keep getting an operand error.

LINQ Query:

        DateTime dat = DateTime.Now.AddDays(-10);
        string preWeek = dat.ToString("dd-MMM-yyyy");

        DateTime dtt = DateTime.Now;
        string today = dat.ToString("dd-MMM-yyyy");

        var data = db.Database_CRE_Events.Where(c => c.Date <= Convert.ToDateTime(preWeek) && c.property != null)
                      .Select(x => new Loan() { Name = x.Deal, loan = x.property, evnt = x.Event })
                      .ToList().Take(3);
        return data;

Is it possible to convert the original sql query to a linq query as c.Date being a string parameter?

Thank you for any further assistance.

Jon Skeet
people
quotationmark

The problem is that you're introducing strings into the mix for no reason at all. Unless you have to convert a DateTime to or from a string, don't do it.

Your query should be as simple as:

DateTime preWeek = DateTime.Today.AddDays(-10);

var data = db.Database_CRE_Events
    .Where(c => c.Date <= preWeek && c.property != null)
    .Select(x => new Loan() { Name = x.Deal, loan = x.property, evnt = x.Event })
    .ToList()
    .Take(3);
return data;

If c.Date is actually a string, you should fix your database so that it isn't a string. It's meant to be a date, so represent it as a date! If you absolutely have to keep it as a string, you should at least use a sortable format, such as yyyy-MM-dd. At that point you could use CompareTo - but it's horrible :(

If the format is dd-MMM-yyyy (as it sounds) you could try performing the parse in the LINQ query, still passing in a DateTime but parsing each value in the database:

.Where(c =>
   DateTime.ParseExact(c.Date, "dd-MMM-yyyy", CultureInfo.InvariantCulture) <= preWeek
   && c.property != null)

... but I wouldn't be surprised if that fails. You may want to add a view in SQL which gives a more appropriate version of the data. Fundamentally, if you have to work with a broken schema (in this case using the wrong type and making a poor decision about how to format the data within that type) then you should expect pain. Pass this pain up to managers in order to prioritize changing the schema...

Notes:

  • You're fetching all the data, and then just taking the first three elements. That's a bad idea. Switch round the calls to ToList and Take after addressing the next bullet...
  • "First three elements" is only meaningful with ordering. Use OrderBy to specify an ordering
  • You're not currently using today, so I removed it
  • If you're only interested in a date, use DateTime.Today
  • You should carefully consider time zones, both in your database and in your calling code. This is currently using the system default time zone - is that what you want?

people

See more on this question at Stackoverflow