实体框架核心收益

debugcn 发表于 Dev

约翰

我有这种方法从我的数据库返回数据：

public IList<Questions> GetAllQuestions(string orgId)
    {
        IList<Questions> questions = new List<Questions>();
        var x = (from o in _context.OrganizationProducts
                     join t in _context.Trackers on o.OrganizationId equals t.OrganizationId
                     join tq in _context.TrackerQuestions on t.TrackerId equals tq.TrackerId
                     join tra in _context.TrackerResponseAnswers on tq.TrackerQuestionId equals tra.TrackerQuestionId
                     join tqc in _context.TrackerQuestionChoices on tq.TrackerQuestionId equals tqc.TrackerQuestionId
                     join tr in _context.TrackerResponseAnswers on tq.TrackerQuestionId equals tr.TrackerQuestionId
                     where o.Organization.Name == orgId
                     
                     orderby tq.SortOrder
                     
                     select new
                     {
                         o.OrganizationId,
                         t.Name,
                         tq.QuestionText,
                         tqc.DisplayValue,
                         tqc.Value,
                         tqc.SortOrder,
                         tq.InputType
                     }).Distinct();
        foreach(var record in x)
        {
            questions.Add(new Questions { DisplayValue = record.DisplayValue, 
                                          InputType = record.InputType, 
                                          Name = record.Name, 
                                          OrganizationId = record.OrganizationId, 
                                          QuestionText = record.QuestionText, 
                                          SortOrder = record.SortOrder, 
                                          Value = record.Value });
        }
        return questions;
    }

使用相应的Questions类：

public class Questions
{
    public Guid OrganizationId { get; set; }
    public string Name { get; set; }
    public string QuestionText { get; set; }
    public string DisplayValue { get; set; }
    public string Value { get; set; }
    public int SortOrder { get; set; }
    public string InputType { get; set; }
}

这是我从此类中获得的响应（为安全起见删除了一些信息）：

 {
    "organizationId": "[ID]",
    "name": "Quiz 1",
    "questionText": "Are you and your romantic partner:",
    "displayValue": "Dating",
    "value": "Dating",
    "sortOrder": 1,
    "inputType": "radio"
},
{
    "organizationId": "[ID]",
    "name": "Quiz 1",
    "questionText": "Are you and your romantic partner:",
    "displayValue": "Engaged",
    "value": "Engaged",
    "sortOrder": 2,
    "inputType": "radio"
},
{
    "organizationId": "[ID]",
    "name": "Quiz 1",
    "questionText": "Are you and your romantic partner:",
    "displayValue": "Married",
    "value": "Married",
    "sortOrder": 3,
    "inputType": "radio"
},

我想使它更像这样：

{
    "organizationId": "[ID]",
    "name": "Quiz 1",
    "questionText": "Are you and your romantic partner:",
    "displayValue": ["Engaged", "Married", "Dating"],
    "value": ["Engaged", "Married", "Dating"],
   
    "inputType": "radio"
},

或更妙的是：

{
        "organizationId": "[ID]",
        "name": "Quiz 1",
        "questionText": "Are you and your romantic partner:",
        "Answers": [
                  {
                     "DiplayValue": "Dating",
                     "value": "Dating",
                     "sortOrder": 1
                  },
                  {
                     "DiplayValue": "Engaged",
                     "value": "Engaged",
                     "sortOrder": 2
                  },
                  {
                     "DiplayValue": "Married",
                     "value": "Married",
                     "sortOrder": 2
                  },
         ],
        "inputType": "radio"
    },

所以我试图将这样的问题归类：

var groupQuestions = questions.Where(x => x.QuestionText != null).GroupBy(x => new { x.QuestionText, x.Value }).Select(g => g.ToList()).ToList();

但是它仍然没有输出。怎么做？此外，此查询的接缝速度有点慢，对性能改进有什么建议吗？

Svyatoslav Danyliv

其实没有什么特别的。但是应在客户端提供分组。

public IList<Questions> GetAllQuestions(string orgId)
{
    var query = from o in _context.OrganizationProducts
                join t in _context.Trackers on o.OrganizationId equals t.OrganizationId
                join tq in _context.TrackerQuestions on t.TrackerId equals tq.TrackerId
                join tra in _context.TrackerResponseAnswers on tq.TrackerQuestionId equals tra.TrackerQuestionId
                join tqc in _context.TrackerQuestionChoices on tq.TrackerQuestionId equals tqc.TrackerQuestionId
                join tr in _context.TrackerResponseAnswers on tq.TrackerQuestionId equals tr.TrackerQuestionId
                where o.Organization.Name == orgId
                orderby tq.SortOrder
                select new
                {
                    o.OrganizationId,
                    t.Name,
                    tq.QuestionText,
                    tqc.DisplayValue,
                    tqc.Value,
                    tqc.SortOrder,
                    tq.InputType
                };

    var enumerable = query.Distinct().AsEnumerable();

    var questionsQuery = 
       from q in enumerable
       group q by new { q.OrganizationId, q.Name, q.QuestionText, q.InputType } into g
       select new Questions
       {
           organizationId = g.Key.OrganizationId,
           name = g.Key.name,
           questionText = g.Key.QuestionText,
           Answers = g.OrderBy(a => a.SortOrder).Select(a => new Answer
           {
              DiplayValue = a.DiplayValue,
              value = a.Value,
              sortOrder = a.SortOrder
           }).ToArray()

           inputType = g.Key.InputType
       }

   var questions = questionsQuery.ToList();

}

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。