locked
I tried to pick rows base on the another data from different column in same table, but I still have problem in that. can you please help me. RRS feed

  • Question

  • User1694748171 posted

    public List<string> Get_Categories_Questions(List<Student_Exam_ViewModel> model )
    {

    List<string> questions = new List<string>();
    List<string> pickList = new List<string>();

    var data = db.Exam_Questions.SqlQuery("Select * from BE.Exam_Questions").ToList();

    foreach(var item in data)
    {
    if(item.Assessment_Section == "1.1")
    {
    var pick_Question = db.Exam_Questions.Select(x => x.Mytable).Take(2).FirstOrDefault(); // want to pick only 2 question id ==Assessment_Section ==2.1
    pickList.Add(pick_Question);

    }
    if (item.Assessment_Section == "2.1")
    {
    var pick_Question = db.Exam_Questions.Select(x => x.QuestionName).Take(4).FirstOrDefault();  want to pick only 4 question id ==Assessment_Section ==2.1
    pickList.Add(pick_Question);
    break;
    }

    }
    var r = 0;

    //foreach (var item in model)
    //{


    //}

    return questions;

    }

    Friday, July 10, 2020 7:42 PM

Answers

  • User1686398519 posted

    Hi luay20032003,

    Cause the code you gave is not comprehensive enough, I cannot understand your needs and the meaning of some variables in great detail.

    I try to guess the desired function based on your description and code as much as possible.

    1. Select(x => x.Mytable)
      • What is Mytable?
      • Do you want to achieve the following requirements?
        • You want to distinguish questions base on Assessment_Section which is the “another data from different column” you described in title, and the type of it is string.
        • Different value of Assessment_Section get different numbers of questions and only need these questions’name, but all these questions store in same picklist.
    2.  want to pick only 4 question id
      • When you use “take()” to pick different numbers of questions but combine with “FirstOrDefault()”, FirstOrDefault will only return the first record.If you want to get four data here, you need to use "ToList()".

    Model

        public class Question
        {
            public int id { get; set; }
            public string Assessment_Section { get; set; }
            public string Title { get; set; }
        }
        public class ExamContext:DbContext
        {
            public DbSet<Question> Exam_Questions { get; set; }
        }

    Controller

            public List<string> Get_Categories_Question()
            {
                List<string> questions = new List<string>();
                List<string> pickList = new List<string>();
                var GenreList = new List<string>();
                var assquery = from e in db.Exam_Questions orderby e.Assessment_Section select e.Assessment_Section;
                GenreList.AddRange(assquery.Distinct());
                foreach (var item in GenreList)
                {
                    if (item == "1.1")
                    {
                        var pick_Question = db.Exam_Questions.Where(x => x.Assessment_Section == item).Select(x => x.Title).Take(2).ToList();
                        pickList.AddRange(pick_Question);
                    }
                    if (item == "2.1")
                    {
                        var pick_Question = db.Exam_Questions.Where(x => x.Assessment_Section == item).Select(x => x.Title).Take(4).ToList();
                        pickList.AddRange(pick_Question);
                        //break;
                    }
                }
                return pickList;
            }

    Best regards,

    Yihui Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 13, 2020 9:24 AM
  • User1694748171 posted

    yes That't what I did to prevent conversion from iqueryable to list like below:

    var pick_Question = db.Exam_Questions.OrderBy(t => Guid.NewGuid()).Where(x => x.Assessment_Section == item).Select(x => x.QuestionName).Take(2).ToList();

    Thank you so much for your valuable help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 13, 2020 3:43 PM

All replies

  • User1686398519 posted

    Hi luay20032003,

    Cause the code you gave is not comprehensive enough, I cannot understand your needs and the meaning of some variables in great detail.

    I try to guess the desired function based on your description and code as much as possible.

    1. Select(x => x.Mytable)
      • What is Mytable?
      • Do you want to achieve the following requirements?
        • You want to distinguish questions base on Assessment_Section which is the “another data from different column” you described in title, and the type of it is string.
        • Different value of Assessment_Section get different numbers of questions and only need these questions’name, but all these questions store in same picklist.
    2.  want to pick only 4 question id
      • When you use “take()” to pick different numbers of questions but combine with “FirstOrDefault()”, FirstOrDefault will only return the first record.If you want to get four data here, you need to use "ToList()".

    Model

        public class Question
        {
            public int id { get; set; }
            public string Assessment_Section { get; set; }
            public string Title { get; set; }
        }
        public class ExamContext:DbContext
        {
            public DbSet<Question> Exam_Questions { get; set; }
        }

    Controller

            public List<string> Get_Categories_Question()
            {
                List<string> questions = new List<string>();
                List<string> pickList = new List<string>();
                var GenreList = new List<string>();
                var assquery = from e in db.Exam_Questions orderby e.Assessment_Section select e.Assessment_Section;
                GenreList.AddRange(assquery.Distinct());
                foreach (var item in GenreList)
                {
                    if (item == "1.1")
                    {
                        var pick_Question = db.Exam_Questions.Where(x => x.Assessment_Section == item).Select(x => x.Title).Take(2).ToList();
                        pickList.AddRange(pick_Question);
                    }
                    if (item == "2.1")
                    {
                        var pick_Question = db.Exam_Questions.Where(x => x.Assessment_Section == item).Select(x => x.Title).Take(4).ToList();
                        pickList.AddRange(pick_Question);
                        //break;
                    }
                }
                return pickList;
            }

    Best regards,

    Yihui Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 13, 2020 9:24 AM
  • User348806598 posted

    Hi Luay,

    What exactly you are trying to do?

    Are you trying to get the top two questions with Assessment_Section=1.1 and the top 4 questions with Assessment_Section=2.1?

    Monday, July 13, 2020 10:38 AM
  • User1694748171 posted

    I'm trying to get random pick question from each time ready from questions table depend on section_Id. for example if section_id = '1.1' then I need to pick only two random question and if section_id ='2.1' then need to pick only 5 random questions and so one.

    Monday, July 13, 2020 12:35 PM
  • User1694748171 posted

    yes your right :

    • What is Mytable?
    • Do you want to achieve the following requirements?
      • You want to distinguish questions base on Assessment_Section which is the “another data from different column” you described in title, and the type of it is string.
      • Different value of Assessment_Section get different numbers of questions and only need these questions’name, but all these questions store in same picklist.    
      • But when I used take(4) I want random select of question, for example if the Assessment_Section ="1.1" I will choose a random 2 question from question table.
      •  
    Monday, July 13, 2020 12:42 PM
  • User348806598 posted

    Hi,

    May be you can try something like -

    var data1 = db.Exam_Questions.SqlQuery("Select top 2 * from BE.Exam_Questions where assessment_section='1.1' order by newid()").ToList();
    var data2 = db.Exam_Questions.SqlQuery("Select top 4 * from BE.Exam_Questions where assessment_section='2.1' order by newid()").ToList();

    data1 contains top 2 random questions with assignment_section == 1.1, data2 contains top 4 random questions with assignment_section == 2.1. If the assignment section value is dynamic, you can first select distinct assignment_section and then loop through the same.

    Monday, July 13, 2020 2:37 PM
  • User1694748171 posted

    This will always select only the top question, what I need to select a random question from each categories list.

    Monday, July 13, 2020 2:54 PM
  • User348806598 posted

    Hi,

    Did you try it- 

    order by newid()

    should make sure you are getting random rows based on were clause.

    Monday, July 13, 2020 2:59 PM
  • User1694748171 posted

    I forgot to add newid(). I added and it bring different question with each time I call the program. Thank you so much for your help. Can I use only LINQ like my code below:

    var pick_Question = db.Exam_Questions.Where(x => x.Assessment_Section == item).Select(x => x.QuestionName).Take(1).ToList();

    Monday, July 13, 2020 3:12 PM
  • User348806598 posted

    yes, you can use order by before select in the same way with Guid.NewId

    Monday, July 13, 2020 3:31 PM
  • User1694748171 posted

    yes That't what I did to prevent conversion from iqueryable to list like below:

    var pick_Question = db.Exam_Questions.OrderBy(t => Guid.NewGuid()).Where(x => x.Assessment_Section == item).Select(x => x.QuestionName).Take(2).ToList();

    Thank you so much for your valuable help.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 13, 2020 3:43 PM
  • User348806598 posted

    I think you should do where clause first and then order by for performance improvement.

    Monday, July 13, 2020 3:48 PM
  • User1694748171 posted

    If I do that I will get the error 

    var pick_Question = db.Exam_Questions.Where(x => x.Assessment_Section == item).OrderBy(t =>Guid.NewGuid).Select(x => x.QuestionName).Take(1).ToList();

    the error:

    CS0411 C# The type arguments for method cannot be inferred from the usage. Try specifying the type arguments explicitly.

    Monday, July 13, 2020 4:08 PM