sql server - Avoid duplicate records in linq query while joining multiple tables -

i have following tabes in db

testpack { id, name, type } documentation { id, tp_id, date, rev } flushing { id, tp_id, date, rev } 

tp_id testpack id , foreign key in both documentation , flushing tables means testpack may have 1-n documentations , flushings.

now want query database (using ef linq) return tests packs, , documentations , flushings against 1 test pack can show information in single datagrid?

test packs, dont have documentatoin/flushings should show in result.

here query have written far

 internal list<testpackregister> generatereport2() {     var alltestpacks = project.getalltestpacks();     var alldocumentation = project.getalldocoumentations();     var allflushings = project.getallpretestflushings();      var queryresult = tp in alltestpacks          join doc in alldocumentation on tp.id equals doc.test_pack_id.value         tpdoc         subtpdoc in tpdoc.defaultifempty()          join flushings in allflushings on tp.id equals flushings.test_pack_id.value         tpflings         subtpflushings in tpflings.defaultifempty()          select new testpackregister         {             test_pack_no = tp.test_pack_no,              documentation_notification = subtpdoc != null ? subtpdoc.const_notification_no : null,             documentation_rev = subtpdoc != null ? subtpdoc.const_notification_no_rev : null,             documentation_notification_date = subtpdoc != null ? subtpdoc.const_notification_date : null,             documentation_targetreadiness_date = subtpdoc != null ? subtpdoc.doc_readiness_target_date : null,             documentation_actualreadiness_date = subtpdoc != null ? subtpdoc.doc_readiness_date : null,             documentation_remarks = subtpdoc != null ? subtpdoc.remarks : null,              flushing_rfi = subtpflushings != null ? subtpflushings.rfino : null,             flushing_rev = subtpflushings != null ? subtpflushings.rfi_rev_no : null,             flushing_rfi_date = subtpflushings != null && subtpflushings.rfi_date != null ? subtpflushings.rfi_date : null,             flushing_planned_date = subtpflushings != null && subtpflushings.planned_date != null ? subtpflushings.planned_date : null,             flushing_actual_date = subtpflushings != null && subtpflushings.actual_date != null ? subtpflushings.actual_date : null,             flushing_acceptance_date = subtpflushings != null && subtpflushings.acceptance_date != null ? subtpflushings.acceptance_date : null,             flushing_remarks = subtpflushings != null ? subtpflushings.remarks : null,         };      queryresult = queryresult.tolist();     return queryresult; } 

and here result of query

basically in testpack table, have 2 testpacks. test pack 1 has 2 documentations , 2 flushing records while test pack 2 has 1 documentation , 1 flushing record. concern here testpack # 1 showing 4 rows, should not showing 2 rows? because test pack has 2 flushings , 2 documentation 1 testpack. of them should appear in 2 rows? why appearing 4 rows? , how can correct behavior?

i'm stuck in too. approach:

foreach (var tp in alltestpacks)         {             var lstallflh = allflushings.where(m => m.tpid == tp.idtp).tolist();             var lstalldoc = alldocumentation.where(m => m.tpid == tp.idtp).tolist();             foreach (var flh in lstallflh)                 lstresult.add(new { tpid = tp.idtp, tpname = tp.value, docname = lstalldoc.skip(lstallflh.findindex(f => f == flh) % lstalldoc.count).first().value, flushname = flh.value });         }