Join two table from different context RRS feed

  • Question

  • User1694748171 posted

    I tried to join two tables from different context using LINQ, My approch is working but the time for joining and excuting the query took a very long time.

            public JsonResult test()
                //var customer = new List<Customer>(db2.Customers.);
                var customer = db2.Customers.Select(x => new { x.id , x.firstName , x.lastName}). Where(x => x.firstName != null && x.lastName != null).ToList().Distinct();
                var studnetEnrollment = new List<Exam_Student_Enrollment>(db.Exam_Student_Enrollment);
                var model = (from C in customer
                             join SE in studnetEnrollment on C.id.ToString() equals SE.TWRAId
                             select new StudentsRecordsViewModel
                                 TWRAID = SE.TWRAId,
                                 StudentFullName = C.firstName + " " + C.lastName,
                                ExamResult = SE.Letter_Grade,
                                 ExamScore = SE.Score,
                                 ExamDate = SE.Exam_Date.ToString()
                return Json(model, JsonRequestBehavior.AllowGet);

    And some time I got this error 

    System.OutOfMemoryException: 'Exception of type 'System.OutOfMemoryException' was thrown.'

    any idea ?

    Monday, December 7, 2020 7:25 PM

All replies

  • User-474980206 posted

    You are loading both tables in memory then joining, so you really have two copies of each table in memory and the json is the another copy. How big are the tables?  Anyhowthis seems too big to be practical. You need to implement paging.

    Monday, December 7, 2020 8:22 PM
  • User1694748171 posted

    I'm implementing paging and I'm using dataTable.  the table is very long and it took about 5 min to pass the line

      var customer = db2.Customers.Select(x => new { x.id, x.firstName, x.lastName }).ToList(); // 5,589,944 rows

    Monday, December 7, 2020 8:32 PM
  • User1120430333 posted

    A datatable is a real resource hog compared to a list. And a datatable is slower than a list, because of boxing and unboxing.

    Research is Fun!: DataTable vs List (lauteikkehn.blogspot.com)

    Reasons to Move from DataTables to Generic Collections - DZone Big Data

    Monday, December 7, 2020 11:36 PM
  • User-474980206 posted

    You just select a page at a time, maybe 200 rows or less. To get the matching rows in the join table you will need to do select in of the paged keys. You will limited to the max entries in the select in to under 255

    i except to get paging of 200 rows to be under 50ms

    Tuesday, December 8, 2020 12:19 AM