Linq EF6 Merging two lists

Question

My application is MVC5, MS SQL. I am trying to develop a dynamic survey using KnockoutJs. I need to develop an array that contains each question information as follows:

var jsonData = [
        {
            Name: "Are you happy?",
            Type: "Yes/No",
            Options: [
                { id: "1", name: "Yes", Qid: "1", Question: "Question", Qtype: "Yes/No" },
                { id: "2", name: "No", Qid: "1", Question: "Question", Qtype: "Yes/No" }
            ],
            Default: "" 
        }

The Options / name is stored in the database as a comma separated column; I used string_spilt to generate rows for each name. In the SQL view each question is repeated.

I get the options using:

var grid = _db1.vw_thesurvey.AsNoTracking().Where(c=> c.SurveyId == surveyid).DistinctBy(p => new { p.Qid }).Where(c=> c.SurveyId == 1)
                .Select(h =>new {
                 Options = new
                    { 
                    name = h.name, id = h.id, Qtype = h.Qtype, Qid = h.Qid}
                }).ToList(); 

This produces 9 rows for three questions (2,3,4 rows per question). I get other question information using:

 var grid1 = _db1.SurveyQuestions.AsNoTracking().Where(c => c.SurveyId == surveyid).Where(c => c.SurveyId == 1)
                .Select(h => new {
                    Name = h.Title,
                    Type = h.Type,
                    Default = ""
                }).ToList();

This produces three rows. My challenge is how to merge the two lists to generate the required array.

Answer 1

It's better to use a model class(in your case probably Options) having Name, Id, QType, QId, Default attributes, and select this class in your Select in both of your LINQ queries. This will give the list of same types then you can combine both of the lists easily.

First list will look like this:

var grid = _db1.vw_thesurvey.AsNoTracking().Where(c=> c.SurveyId == surveyid).DistinctBy(p => new { p.Qid }).Where(c=> c.SurveyId == 1)
            .Select(h =>new Options() { 
                Name = h.name,
                Id = h.id,
                QType = h.Qtype,
                QId = h.Qid,
                Default = ""
               }).ToList(); 

And second list Like:

 var grid1 = _db1.SurveyQuestions.AsNoTracking().Where(c => c.SurveyId == surveyid).Where(c => c.SurveyId == 1)
            .Select(h =>new Options() { 
                Name = h.Title,
                Id = 1, //Some default values, if you want otherwise you can omit
                QType = h.Type,
                QId = 1, //Some default values, if you want otherwise you can omit
                Default = ""
               }).ToList(); 

And then you can add the second list in the first one like:

grid.AddRange(grid1);

I hope it resolves your problem.