CODEWITHSUNDEEP
c#sqlasp.netentity-frameworklinq
Imran Ahmad Shahid
Imran Ahmad Shahid

Reputation: 822

Complex Group By linq Query

I have a question... i have 3 tables...

user(id,name)

Answers(id,title)

UserAnswers(id,user_id,right_answer_id,user_answer_id)

now i want to get user name and total score which is count(where (right_answer_id==user_answer_id)) * 10 so result can be like this

UserName Score

Imran 20

john 30

I don't have such experience in Linq. how can i do that? i know in sql i can do like

select u.Name, 
       sum (case 
              when q.Right_Anwer_id = q.user_answerr_id then 1 
              else 0 
            end)
 from quiz q
 inner join [User] u 
 on u.id = q.user_id
 group by u.Name,
          q.test_id
 having q.test_id = 1

Classes:

class Answer
{
    public int Id { get; set; }
    public string Title{ get; set; }
}

class User
{
    public int Id { get; set; }
    public string Name{ get; set; }
}

class UserAnswers
{
    public int id { get; set; }
    public int user_id { get; set; }
    public int user_answer_id { get; set; }
    public int right_answerr_id { get; set; }
    public virtual Answer Answer { get; set; }
    public virtual User User { get; set; }
}

Upvotes: 0

Views: 472

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109109

If you also add a navigation property User.UserAnswers you can use this query:

from u in context.Users
where u.UserId == userid
select new
{
    u.Name,
    Score = u.UserAnswers
             .Count(ua => ua.user_answerr_id == ua.right_answerr_id) * 10
}

I don't see a TestId property in UserAnswer (please use the singular name for the class), or anywhere, so this test_id = 1 condition in your SQL can't be applied in LINQ (yet).

Upvotes: 1

Related Questions