joint between multitable in linq c# return unexpected rows

Question

I have this query :

from m in _ctx.MaterialRequestContractorDetails
where m.MaterialRequestContractorId == MRCId
join mat in _ctx.MaterialDescriptions on m.MaterialDescriptionId equals mat.Id
join l in _ctx.Lines on m.LineId equals l.Id 
join sheet in _ctx.Sheets on m.SheetId equals sheet.Id
join joint in _ctx.Joints on m.SheetId equals joint.SheetId 
join testjoint in _ctx.TestPackageJoints on joint.Id equals testjoint.Id
join testpack in _ctx.TestPackages on testjoint.TestPackageId equals testpack.Id

I have connection between:

MaterialRequestContractorDetails and sheet
MaterialRequestContractorDetails and line

but I don't have any connection between MaterialRequestContractorDetails and testpackage.

I can have a connection with testpackage using joint and testpackagejoint, but this connection makes a problem and my records repeat with similar data based on joint row

I mean if I have 2 records in my joint table, my result repeats 2 times with similar data.

It's normal, this is how joins (and SQL joins for many years) work. The question is though what are you trying to achieve? — Ivan Stoev, Aug 23 '16 at 18:47
but when i call my function in ui : List lstMaterialRequestContractorDetails = _reportMaterialRequestContractorRepository.ShowReport(Id).Distinct().ToList(); the result changed to two similar records.i have 2 records in sheet table — Ehsan Akbar, Aug 23 '16 at 18:53
All I'm saying that when you join `one` table with `many` table, you get many records. Again, we can help if you show **why** do you need that join. — Ivan Stoev, Aug 23 '16 at 18:54
@IvanStoev i need the packagenumber that is in the testpackage table — Ehsan Akbar, Aug 23 '16 at 18:55
Ok, the question then is how you can get **one** packagenumber when you have **many** joints? — Ivan Stoev, Aug 23 '16 at 18:57
What kind of LINQ are you using (to entities?). And where are the navigation properties? — Gert Arnold, Aug 23 '16 at 21:02

score 0 · Accepted Answer · answered Aug 24 '16 at 06:15

What you want to do is a GroupJoin function. In a query syntax it will look like this:

var result = (from m in MaterialRequestContractorDetails
              where m.MaterialRequestContractorId == MRCId

              join mat in MaterialDescriptions on m.MaterialDescriptionId equals mat.Id
              join l in Lines on m.LineId equals l.Id
              join sheet in Sheets on m.SheetId equals sheet.Id

              join joint in (from joint in Joints
                             join testjoint in TestPackageJoints on joint.Id equals testjoint.Id
                             join testpack in TestPackages on testjoint.TestPackageId equals testpack.Id
                             select new { joint.SheetId, testpack })

              on m.SheetId equals joint.SheetId into tt

              select new { Details = m, Line = l, TestPacks = tt.Select(x => x.testpack).ToList() }).ToList();

What I actually did is separate all the "second half" of the joins into a nested select and then joined it with the main part, but when doing so used the into keyword (GroupJoin).

With the testing data bellow I got 1 record with the MRCId of 1 and in the record had a list of TestPackage containing 2 items.

List<dynamic> MaterialRequestContractorDetails = new List<dynamic>
{
    new { MaterialRequestContractorId = 1, MaterialDescriptionId = 1, LineId = 1, SheetId = 1},
    new { MaterialRequestContractorId = 2, MaterialDescriptionId = 2, LineId = 2, SheetId = 2},
};

List<dynamic> MaterialDescriptions = new List<dynamic>
{
    new { Id = 1 }, new { Id = 2 },
};

List<dynamic> Lines = new List<dynamic> { new { Id = 1 } };
List<dynamic> Sheets = new List<dynamic> { new { Id = 1 } };
List<dynamic> Joints = new List<dynamic> { new { SheetId  = 1, Id = 1 } };

List<dynamic> TestPackageJoints = new List<dynamic>
{
    new { Id = 1, TestPackageId = 1 },
    new { Id = 1, TestPackageId = 2 },
};

List<dynamic> TestPackages = new List<dynamic>
{
    new { Id = 1 }, new { Id = 2 },
};

int MRCId = 1;

Hi .could you please take a look at this :http://stackoverflow.com/questions/39243771/efficiently-convert-rows-to-columns-in-sql-server-view — Ehsan Akbar, Aug 31 '16 at 09:42

joint between multitable in linq c# return unexpected rows

1 Answers1