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

I need in the query bellow to group information by year and month, but its showing the following error:

{"LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression."}

The query:

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, 
                                         yearMonth = TABLE_03.creation_date.ToString("yyyyMM"), 
                                         TABLE_01.type_cust }
         into result
         orderby result.Key.code_person, result.Key.yearMonth
         select new
         {
             mtEpg = result.Key.code_person,
             yearMonth = result.Key.yearMonth,
             value_payment = ((int)(result.Sum(x => x.TABLE_01.value_payment) * 100)),
             type_cust = result.Key.type_cust == 1 ? 991 : 992
         }).ToList();

CodePudding user response：

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.yearMonth
    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
    };