EF DateTime formatting

Question

I am using EF 6 code first and need to do a sql like on a dateTime in a specific format, IE something along the lines of:

my first attempt was something along the lines off:

var users = context.User
       .Where(x => x.BirthDate.ToString("dd/MMM/yyyy h:mm tt").Contains(searchTerm).ToList()

which throws an exception where as EF does not know how to convert and DateTime.ToString() to SQL, which makes sense.

the best solution I found so far was from this page: Entity Framework 6 ToString(), formatting (DateTime format), query intercept where the answer-er uses the functions: SqlFunctions.DatePart and DbFunctions.Right to produce a string that EF can perform a Contains on however for my scenario I specifically need the format to be "dd/MMM/yyyy hh:mm tt" (20/Jan/2017 08:22 am) at the moment I am struggling to get month out in the format MMM.

on a side note if there is another way of achieving this say by creating my own function extending from the DbFunctions or SqlFunctions classes, this could also do the trick.

Answer 1

To get the month at MMM format you can use:

DbFunctions.Left(SqlFunctions.DateName("month", x.BirthDate),3)

The final query may look like this:

var users = context.User
   .Where(x =>
        (DbFunctions.Right("0" + SqlFunctions.DatePart("d", x.BirthDate), 2) + "/" //day
            + DbFunctions.Left(SqlFunctions.DateName("month", x.BirthDate), 3) + "/" // month
            + SqlFunctions.DatePart("yyyy", x.BirthDate) + " "
            + DbFunctions.Right("0" + (x.BirthDate.Hour > 12 ? x.BirthDate.Hour % 12 : x.BirthDate.Hour).ToString(), 2) + ":"
            + DbFunctions.Right("0" + SqlFunctions.DatePart("mi", x.BirthDate), 2) + " "
            + (x.BirthDate.Value.Hour > 11 ? "PM" : "AM"))
        .Contains(searchTerm)
    ).ToList();

but I'm worried about performance.