1

Here I want to join the out put of the first query(one column) to the result of the 2nd query to get a one result set. How can I merge them.(CONCAT doesn't work as required. eg: var query2 = query.concat(query1);)

  var query = (from PP in _db.paymentPlans
                     join APP in _db.Applications on PP.applicationID equals      APP.ApplicationId
                     join C in _db.Courses on APP.courseID equals C.courseID
                     where PP.active == true && APP.agentID == agentID
                     orderby C.courseID ascending
                     group new {C,PP} by new {C.courseID} into totalRecievable
                      select new PdPpAppCourseModel
                     {
                         courseID = totalRecievable.Key.courseID,                            
                         totalAmount = totalRecievable.Sum(x => x.PP.totalAmount)
                     }).ToList();


            var query1=(from PD in _db.paymentDetails
                     join PP in _db.paymentPlans on PD.paymentPlanID equals PP.paymentPlanID
                     join APP in _db.Applications on PP.applicationID equals APP.ApplicationId
                     join C in _db.Courses on APP.courseID equals C.courseID
                     where PP.active == true && APP.agentID == agentID
                     orderby C.courseID ascending
                     group new { C,PD } by new { C.courseID, C.cricosCode, C.courseName } into paymentsCourseWise

                     select new PdPpAppCourseModel
                     {
                         courseID = paymentsCourseWise.Key.courseID,
                         cricosCode = paymentsCourseWise.Key.cricosCode,
                         courseName = paymentsCourseWise.Key.courseName,
                         paidAmount = paymentsCourseWise.Sum(x => x.PD.paidAmount)

                     }).ToList();
Sudesh
  • 29
  • 4

1 Answers1

0

You could join query1 and query like this

 var result = (from q1 in query1
            join q in query on q1.courseID = q.courseID 
            select new PdPpAppCourseModel
            {
                courseID = q1.Key.courseID,
                cricosCode = q1.Key.cricosCode,
                courseName = q1.Key.courseName,
                paidAmount = q1.Sum(x => x.PD.paidAmount),
                totalAmount = q.totalAmount

            }).ToList();
TriV
  • 5,118
  • 2
  • 10
  • 18
  • 1
    Hi Thank you so much for your time and sharing the knowledge. But unfortunately I get the error "Unable to create a constant value of type 'studentAgentManagementSystem2016.Models.PdPpAppCourseModel'. Only primitive types or enumeration types are supported in this context." Any suggestions? – Sudesh Mar 23 '17 at 03:51
  • It's probably related to this problem: http://stackoverflow.com/questions/18929483/unable-to-create-a-constant-value-of-type-only-primitive-types-or-enumeration-ty . I edited my answer – TriV Mar 23 '17 at 08:59
  • but now it's giving this error; "The type 'sManagementSystem.Models.PdPpAppCourseModel' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order." – Sudesh Mar 23 '17 at 21:01
  • Good News I was able to resolve the issue by creating a separate Model to first query.. TriV thank you very much for your help. – Sudesh Mar 23 '17 at 22:56