What is wrong with my Linq query (C#)?

Question

4.00/5 (1 vote)

See more:

I have a ViewModel;

 public class GetQuestionViewModel
{
    public IEnumerable<Question> Questions { get; set; }
    public IEnumerable<QuestionOption> QuestionOptions { get; set; }
    public int Id { get; set; }
    public Nullable<int> Options { get; set; }
    public string QuestionOption1 { get; set; }
    public Nullable<int> QuestionOptionRanking { get; set; }
    public string Question1 { get; set; }
    public int QuestionTypeId { get; set; }
    public Nullable<int> QuestionRanking { get; set; }
    public string Answer { get; set; }
}

And I have my Query structured as thus;

[HttpGet]
       public ActionResult ViewQuestion(int page = 1)
       {
           var userId = User.Identity.GetUserId();
           ICollection<GetQuestionViewModel> question = (from q in db.Questions
               join qo in db.QuestionOptions on q.Id equals qo.QuestionId
               join r in db.Responses on q.Id equals r.QuestionId
               where q.PageNumber == page
               select new GetQuestionViewModel
                {
                   QuestionOptionRanking = qo.QuestionOptionRanking,
                   Id = q.Id,
                   Options = q.Options,
                   QuestionTypeId = q.QuestionTypeId,
                   Question1 = q.Question1,
                   QuestionRanking = q.QuestionRanking,
                   Answer = r.Answer
                 }).ToList();

           ViewBag.PageNumber = page;
           return View(question);
       }

My relationships is one Question to many QuestionOptions but as yet I have not found the correct syntax to return ANY of the QuestionOptions although all the other fields contain the data I expect. Can anyone see what I am doing wrong, please?

Posted 18-Jan-14 6:37am

AlexHarmes

Updated 18-Jan-14 6:42am

v3

Add a Solution

Comments

Karthik_Mahalingam 18-Jan-14 13:07pm

what issue you are facing?

AlexHarmes 18-Jan-14 13:42pm

When my View Returns, there are no values for join qo in db.QuestionOptions on q.Id equals qo.QuestionId but I know there is data in there. As I said it is a one to many relationship and it should be returning a list of QuestionOptions for reach question.

Karthik_Mahalingam 18-Jan-14 13:45pm

i tried it is working fine..

AlexHarmes 18-Jan-14 14:05pm

what is the query returning - and how can you try it without the DB behind?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2014-01-18T08:06:00

You can simulate with this

Questions List - main list
QuestionsOptions list - secondary list
Respose List - secondary list

for example: join 1 ( question list and question option list ) -> it will give 2 rows
join 1 results joined with Resonse list -> gives 4 rows as the parent(for second join) ( join 1 has 2 rows )

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using console_poc;

namespace oops1
{

    public class Db
    {
        public Db()
        {
            Questions = new List<Question>();
            Questions.Add(new Question() { Id = 1, Options = 1, PageNumber = 1, Question1 = "q1", QuestionRanking = 1, QuestionTypeId = 1 });


            QuestionOptions = new List<QuestionOption>();
            QuestionOptions.Add(new QuestionOption() { QuestionId = 1, QuestionOptionRanking = 1 });
            QuestionOptions.Add(new QuestionOption() { QuestionId = 1, QuestionOptionRanking = 2 });
            QuestionOptions.Add(new QuestionOption() { QuestionId = 2, QuestionOptionRanking = 3 });
            QuestionOptions.Add(new QuestionOption() { QuestionId = 2, QuestionOptionRanking = 4 });


            Responses = new List<Response>();
            Responses.Add(new Response() { Answer = "a1", QuestionId = 1 });

        }

        public List<QuestionOption> QuestionOptions { get; set; }
        public List<Question> Questions { get; set; }
        public List<Response> Responses { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {

            int page = 1;

            Db db = new Db();
           // using lamda expression
           //var result =  db.Questions.Join(db.QuestionOptions, (k => k.Id), (k => k.QuestionId), (q, qo) => new { q, qo }).
           //     Join(db.Responses, (k => k.q.Id), (k => k.QuestionId), (qqo, r) => new { r, qqo }).Where(k=>k.qqo.q.PageNumber == page).Select(k =>
           //         new GetQuestionViewModel()
           //         {
           //             QuestionOptionRanking = k.qqo.qo.QuestionOptionRanking,
           //             Id = k.qqo.q.Id,
           //             Options = k.qqo.q.Options,
           //             QuestionTypeId = k.qqo.q.QuestionTypeId,
           //             Question1 = k.qqo.q.Question1,
           //             QuestionRanking = k.qqo.q.QuestionRanking,
           //             Answer = k.r.Answer
           //         }).ToList();


            var question = (from q in db.Questions
                            join qo in db.QuestionOptions on q.Id equals qo.QuestionId
                            join r in db.Responses on q.Id equals r.QuestionId
                            where q.PageNumber == page
                            select new GetQuestionViewModel
                             {
                                 QuestionOptionRanking = qo.QuestionOptionRanking,
                                 Id = q.Id,
                                 Options = q.Options,
                                 QuestionTypeId = q.QuestionTypeId,
                                 Question1 = q.Question1,
                                 QuestionRanking = q.QuestionRanking,
                                 Answer = r.Answer
                             }).ToList();


        }
    }
}

public class Question
{
    public int Id { get; set; }
    public int PageNumber { get; set; }
    public Nullable<int> Options { get; set; }
    public string Question1 { get; set; }
    public int QuestionTypeId { get; set; }
    public Nullable<int> QuestionRanking { get; set; }
}


public class QuestionOption
{
    public int QuestionId { get; set; }
    public Nullable<int> QuestionOptionRanking { get; set; }
}
public class Response
{
    public int QuestionId { get; set; }
    public string Answer { get; set; }
}

public class GetQuestionViewModel
{
    public IEnumerable<Question> Questions { get; set; }
    public IEnumerable<QuestionOption> QuestionOptions { get; set; }
    public int Id { get; set; }
    public Nullable<int> Options { get; set; }
    public string QuestionOption1 { get; set; }
    public Nullable<int> QuestionOptionRanking { get; set; }
    public string Question1 { get; set; }
    public int QuestionTypeId { get; set; }
    public Nullable<int> QuestionRanking { get; set; }
    public string Answer { get; set; }
}