locked
Problem in Converting object to json RRS feed

  • Question

  • User-1243688316 posted

    Hi friends :)

    when i use this query to return json,everything is ok!

                var Result2 = (from r in Result
                               join l in db.tbl_lessons
                               on r.idLesson2 equals l.idLesson
                               where r.idLesson2 == l.idLesson || r.idLesson2 == 0
    
                               join t in db.tbl_teachers
                               on r.idTeacher2 equals t.idTeacher
                               where r.idTeacher2 == t.idTeacher || r.idTeacher2 == 0
                               select new
    
                               {
                                   r.End,
                                   r.Start,
                                   r.idBell,
                                   r.idClass,
                                   r.idDataTable,
                                   r.idDay,
                                   r.idLesson,
                                   r.idLesson2,
                                   r.idTeacher,
                                   r.idTeacher2,
                                   r.idYear,
                                   r.isTak,
                                   TakStatr = r.TakStatr,
                                   TakEnd = r.TakEnd,
                                   r.dayName,
                                   r.BellName,
                                   r.yearName,
                                   LessonName = r.lessonName,
                                   LessonName2 = l.lessonName,
                                   r.teacheName,
                                   t.FName,
                                   t.LName,
                                   r.className
    
                               }).ToList().Select(r => new
                               {
                                   r.yearName,
                                   r.className,
                                   r.dayName,
                                   r.BellName,
                                   r.LessonName,
                                   TeacheName = r.teacheName,
                                   r.Start,
                                   r.End,
                                   r.isTak,
                                   r.TakStatr,
                                   r.TakEnd,
                                   LessonName2 = r.LessonName2,
                                   TeacheName2 = r.FName + " " + r.LName,
                                   r.idBell,
                                   r.idClass,
                                   r.idDataTable,
                                   r.idDay,
                                   r.idLesson,
                                   r.idLesson2,
                                   r.idTeacher,
                                   r.idTeacher2,
                                   r.idYear,
                               }).ToList();
    
                var jsonSerialiser = new JavaScriptSerializer();
                var json = jsonSerialiser.Serialize(Result2);
    
                return new HttpResponseMessage()
                {
                    Content = new StringContent(
                        json.ToString()
    
                    )
                };
    

    But use .Tolist() twice in query make it very lazy when the rows is more than 10000!

    I tried to use .AsQueryable inested of .Tolist()!
    but this time i get this error:
    Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.

    Thanks for any Help :)

    Wednesday, November 1, 2017 6:54 AM

All replies

  • User1120430333 posted

    All you have done here is make EF read every record in the DB tables twice, because you never disconnected from the DB while doing a ToList().

    Wednesday, November 1, 2017 1:38 PM
  • User-1243688316 posted
    very thanks for replay!
    and how can make this query more efficiently?
    how cad reach this query result by minimum database transection?
    it will more usefull for me if there was a sample query like my query!
    thanks alot :)
    Wednesday, November 1, 2017 8:46 PM
  • User-707554951 posted

    Hi arfaie

    Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.

    Don't use Equals in this query. Use == instead. Equals(object obj) converts in expression that need to cast this obj to concrete type, but LINQ to Entities does not support such casting.

    If you still have problem, you could refer to the solution in the following thread:

    https://stackoverflow.com/a/7874771

    https://stackoverflow.com/a/8496526

    https://forums.asp.net/t/2006939.aspx?+Unable+to+cast+the+type+System+Int32+to+type+System+Object+LINQ+to+Entities+only+supports+casting+EDM+primitive+or+enumeration+types+

    Best regards

    Cathy

    Thursday, November 2, 2017 2:04 AM
  • User1120430333 posted

    very thanks for replay!
    and how can make this query more efficiently?

    You make a concrete class/object based on all the properties where you were doing the first projection, and you project into the List<T> all of the objects into the List<T> based on the concrete object.  And then you close the connection to the DB, which the List<T> is now in a disconnected state from the DB, and there is no way the a query can cause the DB  be accessed on a ToList(), because the DB is closed don't do the Tolist().

    You open the connection again and query the DB and do the join on the disconnected List<T> with the DB table you're trying to join on,  again not doing the ToList() and close the connection.

     As long as the connection is closed, then any time yo iterate over the objects from a query, then you know it can be going back to the DB to read the records again as you iterate over them, even with a ToList().


     

    Friday, November 3, 2017 12:09 AM