我需要在下面的查询中按年份和月份对信息进行分组,但它显示以下错误:
{“LINQ to Entities无法识别方法'System.String ToString(System.String)'方法,并且无法将此方法转换为存储表达式。”}
查询:
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();
因此,不支持翻译。所以要正确:按部分划分日期。
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
};