Clever Geek Handbook

EF query with string as date filtering, how?

I need to filter out records that are valid in date ranges. These date columns are rows in the database ("JAN 01 1900", "31 2014").

How can I use Entity Framework 5 (EF5) to filter these records using query method syntax (the problem is that dates are strings, not DateTime ..)?

Here's some sample code I'm currently working on:

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...
}
+3


source to share


4 answers


Jon Skeet already correctly said that a column type that acts like a date should be of type DateTime instead of a string. After converting that column to DateTime and translating the "problem" into a typical source for the target conversion (flat file to SQL insert), I was able to finish my personal Expression function correctly.
0


source


You cannot make a separate ( SelectingValidDateRange

) method for validating dates. But you can use .NET Convert.ToDateTime

:



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


source


Convert.ToDateTime may throw an exception when used directly on objects. Can you try to get the entries in the list first and then use the conversion method to compare the date.

//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 helps.

+1


source 


.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();
0


source






More articles:


All Articles