Select distinct Linq

Question

I have Question and Answer tables in a database. I used Join to select questions with answers and tried to get questions and answers with LINQ. Here is my code:

List<Question> newQuestionList = 
    (from q in dt.AsEnumerable()
     where (q.Field<Guid>("Question") != null))
     select new Question
     {
         Oid = q.Field<Guid>("Question"),
         QuestionContext = q.Field<String>("QuestionContext"),
         Priority = q.Field<Int32>("Priority"),
         Order = q.Field<Int32>("OrderQuestion"),
         Subject = q.Field<Guid>("Subject"),
         Answers = (from a in dt.AsEnumerable()
                    where a.Field<Guid>("Question") == q.Field<Guid>("Question")
                    select
                    new Answer
                    {
                        Oid = a.Field<Guid>("AnswerOid"),
                        AnswerContext = a.Field<String>("Answer"),
                        IsCorrect = a.Field<bool>("Correct")
                    }).ToList()
         }).Distinct().ToList();

There are a lot of rows that have the same question (Id). It iterates through all. I need to iterate only once for a question. If I have 7 questions, and every question has 4 answers, this code gives me a list that's count is 28. Its count must be 7. Can anyone help?

public class Question
{
    private Guid oid;

    public Guid Oid
    {
        get { return oid; }
        set { oid = value; }
    }

    private string questionContext;

    public string QuestionContext
    {
        get { return questionContext; }
        set { questionContext = value; }
    }

    private int priority;

    public int Priority
    {
        get { return priority; }
        set { priority = value; }
    }

    private Guid subject;

    public Guid Subject
    {
        get { return subject; }
        set { subject = value; }
    }

    private List<Answer> answers;

    public List<Answer> Answers
    {
        get { return answers; }
        set { answers = value; }
    }

    private int order;

    public int Order
    {
        get { return order; }
        set { order = value; }
    }
}

public class Answer 
{
    private Guid oid;

    public Guid Oid
    {
        get { return oid; }
        set { oid = value; }
    }

    private string answerContext;

    public string AnswerContext
    {
        get { return answerContext; }
        set { answerContext = value; }
    }

    private bool isCorrect;

    public bool IsCorrect
    {
        get { return isCorrect; }
        set { isCorrect = value; }
    }      
}

So do you want the first answer for each question, do you want a particular one based on some custom logic, a random one, do you want all of the answers aggregated into a list or other combined structure of some sort, or what? — Servy, Nov 21 '12 at 18:43
you might try doing this query against a database that everyone has like Northwind or similar. It would help people understand what you are seeing and where you are going wrong and it may help you understand as well — Mario, Nov 21 '12 at 18:43

score 2 · Accepted Answer · edited Nov 21 '12 at 19:25

I believe you just want to group your questions using .GroupBy(n=>n.Oid) (I've made the assumption that each question has a unique Oid as it is a Guid)

var questions = (from q in dt.AsEnumerable()
                 where (q.Field<Guid>("Question") != null))
                 select new Question
                 {
                     Oid = q.Field<Guid>("Question"),
                     QuestionContext = q.Field<String>("QuestionContext"),
                     Priority = q.Field<Int32>("Priority"),
                     Order = q.Field<Int32>("OrderQuestion"),
                     Subject = q.Field<Guid>("Subject"),
                     Answers = (from a in dt.AsEnumerable()
                                where a.Field<Guid>("Question") == q.Field<Guid>("Question")
                                select new Answer
                                {
                                    Oid = a.Field<Guid>("AnswerOid"),
                                    AnswerContext = a.Field<String>("Answer"),
                                    IsCorrect = a.Field<bool>("Correct")
                                }).ToList()
                 }).GroupBy(n=>n.Oid).ToList();

This will not return a flat collection. If I understand your structure correctly, this will return 7 Unique questions which have been joined to their answers. You can choose to flatten this collection by getting the first answer if you don't care about the others. You would append this to your GroupBy(n=>n.Oid) for:

GroupBy(n=>n.Oid).Select(g => g.First()).ToList();

I can still see your code without scrolling to the right. You should add more spaces :) — L.B, Nov 21 '12 at 19:11

score 2 · Answer 2 · answered Nov 21 '12 at 19:37

I think if you do grouping first, then you will not have to enumerate whole table for each question

List<Question> questions = 
    (from q in dt.AsEnumerable()
     where (q.Field<Guid>("Question") != null)
     group q by new {  Oid = q.Field<Guid>("Question"),
                       QuestionContext = q.Field<String>("QuestionContext"),
                       Priority = q.Field<Int32>("Priority"),
                       Order = q.Field<Int32>("OrderQuestion"),
                       Subject = q.Field<Guid>("Subject")
    } into g
    select new Question {
        Oid = g.Key.Oid,
        QuestionContext = g.Key.QuestionContext,
        Priority = g.Key.Priority,
        Order = g.Key.Order,
        Subject = g.Key.Subject,
        Answers = g.Select(a => new Answer() 
        {
            Oid = a.Field<Guid>("AnswerOid"),
            AnswerContext = a.Field<String>("Answer"),
            IsCorrect = a.Field<bool>("Correct")
        }).ToList()
    }).ToList();

Select distinct Linq

2 Answers2