Formatting Date in Linq-To-Entities Query Causes Exception

Formatting date in Linq-to-Entities query causes exception

Remember that your query is going to be translated to SQL and sent to the database. Your attempt to format the date is not supported in the query, which is why you are seeing that particular error message. You need to retrieve the results and then format after the data has been materialized.

One option is to simply select the date as it is. As you iterate over the result, format it as you add it to your list. But you can also achieve the construction of the list with the formatted date in a single statement by using method chaining.

List<SelectListItem> _months = db.Envelopes.OrderByDescending(d => d.ReportDate)
        .Select(d => d.ReportDate)
        .AsEnumerable() // <-- this is the key method
        .Select(date => date.ToString("MMM-yyyy"))
        .Distinct()
        .Select(formattedDate => new SelectListItem { Text = formattedDate, Value = formattedDate })
        .ToList(); 

The method .AsEnumerable() will force the execution of the first portion of the query against the database and the rest will be working with the results in memory.

Error Linq to Entities Datetime

Well, the error is actually quite clear. There is no translation in Linq to Entities of ParseExact to SQL.

Remember, Entity Framework, under the covers, converts the query to a SQL command or set of commands. If EF doesn't know how to translate something, it throws this error.

One possible solution, while not terribly efficient, is to convert the IQueryable to IEnumerable, which will allow you to execute the statement.

var dates = query.ToList().Select(
             x => DateTime.ParseExact(x.Date, "yyyy-MM", CultureInfo.InvariantCulture));

Linq-to-Entities: Format Date in select query expression

I found one workaround:

 nonBusinessDays = (from dt in
                            (from ac in db.AdminCalendar
                             where ac.DateTimeValue >= calendarStartDate && ac.DateTimeValue <= calendarEndDate && ac.IsBusinessDay == false
                             select ac.DateTimeValue).ToList()
                    select string.Format("{0:M/d/yyyy}", dt)).ToList();

Get formatted date in string format in LINQ to Entities

This happens because the LINQ expression is evaluated on the server side i.e. inside SQL Server and there the function ToString() is not available.

As suggested in the comments already: Get a DateTime, format on the client side.

LINQ Date formatting not allowed to group by a formatted date YearMonth

So, ToString(Format) translation is not supported. So make it correctly: split date by parts.

var query = 
    from TABLE_01 in Context.payment
    join TABLE_02 in Context.documents on TABLE_01.code_doc     equals TABLE_02.cod_doc
    join TABLE_03 in Context.dispatch  on TABLE_01.cod_dispatch equals TABLE_03.code_dispatch
    where (new int[] { 1, 13, 14 }).Contains(TABLE_01.type_cust)
        && (TABLE_02.status < 14)
        && (TABLE_03.id_file_creation == 1)
        && (TABLE_03.creation_date >= initialDate && TABLE_03.creation_date <= finalDate)
    group new { TABLE_02, 
                TABLE_01, 
                TABLE_03 } by new { TABLE_02.code_person, 
                                    Year = TABLE_03.creation_date.Year,
                                    Month = TABLE_03.creation_date.Month,
                                    TABLE_01.type_cust }
    into result
    orderby result.Key.code_person, result.Key.Year, result.Key.Month
    select new
    {
        mtEpg = result.Key.code_person,
        result.Key.Year,
        result.Key.Month,
        value_payment = ((int)(result.Sum(x => x.TABLE_01.value_payment) * 100)),
        type_cust = result.Key.type_cust == 1 ? 991 : 992
    };

How to format date in Linq

This code works for (LINQ to Objects case)

    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public DateTime DateOfBirth { get; set; }
    }


    private static void Main(string[] args)
    {

        var people = new List<Person>
        {
            new Person
            {
               Id = 1,
               Name = "James",
               DateOfBirth = DateTime.Today.AddYears(-25),
            },

            new Person
            {
                Id = 2,
                Name = "John",
                DateOfBirth = DateTime.Today.AddYears(-20),
            },

            new Person
            {
               Id = 3,
               Name = "Peter",
               DateOfBirth = DateTime.Today.AddYears(-15),
            }
       };

       var result = people.Select(t => new
       {
           t.Name,
           DateStr = t.DateOfBirth.ToString("dd/MM/yyyy")
       }).Take(2).ToList();

       foreach (var r in result)
       {
          Console.WriteLine(r.Name);
          Console.WriteLine(r.DateStr);
       }

       Console.ReadLine();
}

For Linq to Entities case, check this answer
Formatting date in Linq-to-Entities query causes exception

LINQ to Entities does not recognize my method

EF cannot translate your custom method to SQL. You can inject a .AsEnumerable() call to change the underlying context from EF to Linq-to-Objects:

var result = (from ord in db.vw_orders
              where ord.uid == user.id
              orderby ord.order_date descending select ord
             )
             .AsEnumerable()
             .Select(o => new { o.id,
                                date = Tools.toPersianDateTime((DateTime)o.order_date),
                                o.is_final, 
                                o.status, 
                                o.image_count, 
                                o.order_count, 
                                o.total_price }
                    );

The specified type member 'Date' is not supported in LINQ to Entities Exception

LINQ to Entities cannot translate most .NET Date methods (including the casting you used) into SQL since there is no equivalent SQL.

The solution is to use the Date methods outside the LINQ statement and then pass in a value. It looks as if Convert.ToDateTime(rule.data).Date is causing the error.

Calling Date on a DateTime property also cannot be translated to SQL, so a workaround is to compare the .Year .Month and .Day properties which can be translated to LINQ since they are only integers.

var ruleDate = Convert.ToDateTime(rule.data).Date;
return jobdescriptions.Where(j => j.Deadline.Year == ruleDate.Year 
                       && j.Deadline.Month == ruleDate.Month 
                       && j.Deadline.Day == ruleDate.Day);

---

Related Topics