c# - 如何将具有多个连接的 Linq 表达式转换为方法语法或检索选择索引？

Question

我有一个现有的(有效!)linq 表达式:

from ca in db.CustomAnswer
join ss in db.SurveySubmission on ca.SubmissionId equals ss.Id
join cq in db.CustomQuestion on ca.QuestionId equals cq.Id
where (ss.SurveyId == request.SurveyId)
orderby ss.Submitted, cq.SortOrder
select new
{
    SubmissionId = ss.Id,
    Answer = ca.Answer
}

我想将选择的索引添加到新对象中，例如

from ca in db.CustomAnswer
join ss in db.SurveySubmission on ca.SubmissionId equals ss.Id
join cq in db.CustomQuestion on ca.QuestionId equals cq.Id
where (ss.SurveyId == request.SurveyId)
orderby ss.Submitted, cq.SortOrder
select new
{
    SubmissionId = ss.Id,
    **Code = selectIndex,**
    Answer = ca.Answer
}

为此，我相信我需要先将查询转换为方法语法，以便我可以使用 Select((q, index) => ...)形式。以我简单的想法，我认为应该是:

  db.SurveySubmission
    .Where(ss => ss.SurveyId == request.SurveyId)
    .OrderBy(ss => ss.Submitted)
    .Join(db.CustomAnswer, ss => ss.Id, ca => ca.SubmissionId, (ss, ca) => new { ss, ca })
    .Join(db.CustomQuestion, o => o.ca.QuestionId, cq => cq.Id, (o, cq) => new { o.ss, o.ca, cq })
    .OrderBy(q => q.cq.SortOrder)
    .Select((q, idx) => new {
                                SubmissionId = q.ss.Id,
                                Answer = q.ca.Answer,
                                Code = idx
                            });

但是，当计算表达式时我得到一个错误:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[<>f__AnonymousTypef3[System.Guid,System.String,System.Int32]] Select[<>f_AnonymousTypee3,<>f__AnonymousTypef3] (System.Linq.IQueryable1[<>f__AnonymousTypee3[My.Data.Namespace.SurveySubmission, My.Data.Namespace.CustomAnswer,My.Data.Namespace.CustomQuestion]], System.Linq.Expressions.Expression1[System.Func3[<>f_AnonymousTypee3[My.Data.Namespace.SurveySubmission,
My.Data.Namespace.CustomAnswer,My.Data.Namespace.CustomQuestion],System.Int32,<>f__AnonymousTypef3[System.Guid, System.String,System.Int32]]])' method, and this method cannot be translated into a store expression.

我希望这对某些人来说是显而易见的？我盯着它看了好几个小时，我唯一能得出的结论是我不够聪明……有人能帮忙吗？？

Answer 1

EF 无法将其转换为 SQL，因为在 SQL 中集合是无序的；索引的想法对它没有任何意义。

而是使用 EF 查询获取索引，然后在 linq to objects 查询中添加索引:

var query = //your original query goes here

var finalQuery = query.AsEnumerable()
    .Select((answer, index) => new
        {
            answer.SubmissionId,
            answer.Answer,
            Code = index,
        });