CODEWITHSUNDEEP
c#linqentity-framework
Patrick Peters
Patrick Peters

Reputation: 9568

EF query with string as date filtering, how?

I have to filter out records that are valid within date ranges. These date columns are strings in the database ('JAN 01 1900', 'MAY 31 2014').

How can I using Entity Framework 5 (EF5) filter out those records using query method syntax (the problem is that the dates are strings and not DateTime..) ?

Here is a code sample I am working on now:

public PostcodeEntry GetPostcodeEntry(string postcode, int huisnummer)
{
    var isEven = (huisnummer % 2) == 0; //tbv performance vd query

    using (ITransactieDataContext transactieDataContext = new TransactieDataContext())
    {
        postcodeEntry = transactieDataContext.PostcodeEntries
            .Where(p => p.Postcode.ToUpper() == postcode.ToUpper())
            .Where(p => p.NummerIsEven == isEven)
            .Where(p => p.ScheidingHoog >= huisnummer)
            .Where(p => p.ScheidingLaag <= huisnummer)
            .Where(SelectValidDateRange())
            .SingleOrDefault();

        return postcodeEntry;
    }
}

private Expression<Func<PostcodeEntry, bool>> SelectValidDateRange()
{
    DateTime now = DateTime.Now;

    return x => x.StartDate... etc...
}

Upvotes: 3

Views: 1593

Answers (4)

Jonelle H. Castaneda
Jonelle H. Castaneda

Reputation: 24

.Where(x =>{
string[] strDates = x.DateFieldHere.Split(",");
DateTime d1,d2;
if(DateTime.TryParse(strDates[0], out d1) 
&& DateTime.TryParse(strDates[1], out d2))
{ 
  return d1>= parameterHere1 && d2 <= parameterHere2;
}
return false; }).SingleorDefault();

Upvotes: 0

Patrick Peters
Patrick Peters

Reputation: 9568

Jon Skeet already mentioned it correctly that the column type that acts as a Date should be of type DateTime instead of string. After converted this column to DateTime and shifting the 'issue' to a typical source to target conversion (flat file to SQL inserts), I was able to finish my private Expression function correctly.

Upvotes: 0

Shankar
Shankar

Reputation: 256

Convert.ToDateTime may give an exception while using on entities directly. Can you try getting records in list first and then using convert method for date comparison.

//Get complete list in postcodeEntry first
postcodeEntry = transactieDataContext.PostcodeEntries
                    .Where(p => p.Postcode.ToUpper() == postcode.ToUpper())
                    .Where(p => p.NummerIsEven == isEven)
                    .Where(p => p.ScheidingHoog >= huisnummer)
                    .Where(p => p.ScheidingLaag <= huisnummer).ToList()

//Then add Convert.ToDateTime() filter on list
var requiredResult = postcodeEntry.where(p=> Convert.ToDateTime(p.StratDate) < DateTime.Now && Convert.ToDateTime(p.EndDate) > DateTime.Now).SingleOrDefault();

Hope this will help.

Upvotes: 1

Mark Shevchenko
Mark Shevchenko

Reputation: 8197

You can't make separate method (SelectingValidDateRange) to check dates. But you can use .NET's Convert.ToDateTime:

.Where(p => Convert.ToDateTime(p.StartDate) < DateTime.Now
         && Convert.ToDateTime(p.EndDate) <= DateTime.Now)

Upvotes: 1

Related Questions