CODEWITHSUNDEEP
.netasp.net-mvcentity-frameworklinqmodel-view-controller
john
john

Reputation: 45

Trying to Use multi EF model in Controller using LINQ MVC?

I am trying to use multi EF model in Controller using LINQ my code is,

public JsonResult Index(int prefix)
{
    List<Survey_Response> ResponseList = new List<Survey_Response>();
    SurveyAppEntities ObjectSur = new SurveyAppEntities();           
    ObjectSur.Configuration.ProxyCreationEnabled = false;
    ResponseList = (from r in ObjectSur.Survey_Response
                   join q in ObjectSur.Questions on r.SurveyID equals q.ID
                   where r.SurveyID.Equals(prefix)
                   select new { r.QuestionID, q.Text, r.Response, r.FilledBy }).ToList();
    return Json(ResponseList, JsonRequestBehavior.AllowGet);
}

I have two entities "Suervey_Response" and "Questions" i need data from both of them

Entity "Survey_Response" is like,

    public partial class Survey_Response
    {
        public int ID { get; set; }
        public int SurveyID { get; set; }
        public int QuestionID { get; set; }
        public string Response { get; set; }
        public int FilledBy { get; set; }

        public virtual Question Question { get; set; }
        public virtual Survey Survey { get; set; }
        public virtual User User { get; set; }
    }

Linq query is giving conversion type error Anonymous on this line,

select new { r.QuestionID, q.Text, r.Response, r.FilledBy }).ToList();

If i put "r" in place of these selected values it works fine.

Hopes for your suggestion

Upvotes: 0

Views: 32

Answers (1)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

The problem caused by assigning an list of anonymous type which contains result from multiple table joins into List<Survey_Response> collection, which cannot be implicitly converted. You should create another class to obtain query results like this:

Model (Placeholder)

public class SurveyVM
{
    public int QuestionID { get; set; }
    public string Text { get; set; }
    public string Response { get; set; }
    public int FilledBy { get; set; }
}

And use that class inside LINQ query as in example below:

Controller Action

public JsonResult Index(int prefix)
{
    List<SurveyVM> ResponseList = new List<SurveyVM>();
    SurveyAppEntities ObjectSur = new SurveyAppEntities();           
    ObjectSur.Configuration.ProxyCreationEnabled = false;
    ResponseList = (from r in ObjectSur.Survey_Response
                   join q in ObjectSur.Questions on r.SurveyID equals q.ID
                   where r.SurveyID.Equals(prefix)
                   select new SurveyVM 
                   { 
                       QuestionID = r.QuestionID, 
                       Text = q.Text, 
                       Response = r.Response, 
                       FilledBy = r.FilledBy 
                   }).ToList();
    return Json(ResponseList, JsonRequestBehavior.AllowGet);
}

Note that you can neither use Survey_Response nor Question as table name mentioned inside select new statement, you need third class definition which contains all properties defined in both tables.

Upvotes: 1

Related Questions