时间戳

首页 > 解决方案 > 需要从 LINQ 中的连接列中获取总和

c# - 需要从 LINQ 中的连接列中获取总和

问题描述

需要从 linq 查询中的选定列中获取总和

我将表格分组并根据组获取记录，但我需要获取候选人获得的分数总和。

(from subCategory in NIS_SUBCATEGORies
group subCategory by subCategory.ID into sg

    join category in NIS_CATEGORies on sg.FirstOrDefault().CategoryId equals category.ID
    join question in NIS_QUESTIONS on sg.FirstOrDefault().ID equals question.SubcategoryId
    join nisAnswer in NIS_QUESTION_ANSWERS on question.ID equals nisAnswer.QuestionId
    join userAnswer in NIS_USER_ANSWERS on new 
    {
        X1 = nisAnswer.QuestionId,
        X2 = nisAnswer.IsCorrectAnswer
    } equals new
    {
        X1 = userAnswer.QuestionId,
        X2 = true
    }                                  
    join nisUser in NIS_USERS on userAnswer.UserId equals nisUser.Id
    select new 
    {
        SubCategoryName = sg.FirstOrDefault().Name,
        CategoryId = category.ID,
        FirstName = nisUser.FirstName,
        LastName = nisUser.LastName,
        MobileNumber = nisUser.UserName,
        userMarks =  question.Marks
    }
).Distinct()

我需要从下面的列中求和

userMarks =  question.Marks

就像是

userMarks =  Sum(question.Marks)

标签： c#linqlinqpad

解决方案

不确定我是否正确理解了您的模型，但我认为您最好从用户答案开始并从那里加入其他表格

例如

(from userAnswer in NIS_USER_ANSWERS
    where userAnswer.IsCorrectAnswer == true
    join nisUser     in NIS_USERS          on userAnswer.UserId       equals nisUser.ID                                                                   
    join question    in NIS_QUESTIONS      on userAnswer.QuestionId   equals question.ID   
    join subCategory in NIS_SUBCATEGORies  on question.SubcategoryId  equals subCategory.ID 

    group new { userAnswer, question } by new
    {
        SubCategoryName  = subCategory.Name,
        subCategory.CategoryId,
        nisUser.FirstName,
        nisUser.LastName,
        nisUser.MobileNumber,            
    }
    into g
    select new
    {
        g.Key.SubCategoryName,
        g.Key.CategoryId,
        g.Key.FirstName,
        g.Key.LastName,
        g.Key.MobileNumber,
        userMarks = g.Sum(a => a.question.Marks)
    }
)

您需要检查所有连接，因为它们可能不正确。

请注意，如果您的模型使用导航属性正确定义，这会简单得多，因为您不需要手动编写连接，从而消除了错误编写它们的可能性。

例如，您的查询可能是

(from userAnswer in NIS_USER_ANSWERS
    where userAnswer.IsCorrectAnswer == true

    group userAnswer by new
    {
        SubCategoryName  = userAnswer.Question.SubCategory.Name ,
        userAnswer.Question.SubCategory.CategoryId,
        userAnswer.User.FirstName,
        userAnswer.User.LastName,
        userAnswer.User.MobileNumber,            
    }
    into g
    select new
    {
        g.Key.SubCategoryName,
        g.Key.CategoryId,
        g.Key.FirstName,
        g.Key.LastName,
        g.Key.MobileNumber,
        userMarks = g.Sum(a => a.Question.Marks)
    }
)

推荐阅读