How to combine 4 entity tables into one expression?

Question

I need help understanding how to perform multiple joins in a Linq-to-Entities statement. I always have a very hard time understanding the expressions for Linq joins, and especially multiple joins. I always end up using sub-queries instead. For example:

public List<MyReportItem> GetReportItemsHelper(string[] years, string[] quarters, string[] areas, string myType, string[] ownerships, IEnumerable<String> fieldCodes)
{
    var _reportItems = _db.MytableEntity1
        .Where(c => c.FieldID.Equals(MY_ID)             //Id Field
            && years.Contains(c.PeriodYear)             //PeriodYear/Year
            && quarters.Contains(c.Period)              //Period/Quarters
            && c.MyType.Equals(myType)                  //Special Type
            && areas.Contains(c.Area)                   //Area                   
            && ownerships.Contains(c.Ownership))        //Ownership
        .Where(c => fieldCodes.Contains(c.FieldCode))   //Field Code
        .Where(c => c.Suppress.Equals("0"))             //Suppression is false
        .Select(c => new MyReportItem
        {
            Field1 = c.FieldA,
            Field2 = c.FieldB,
            Field3 = c.FieldC.TrimEnd(),

            //Sub-queries
            Field4 = _db.MytableEntity2.Where(g => g.FieldID.Equals(c.FieldID) && g.MyType.Equals(c.MyType) && g.Area.Equals(c.Area)).Select(g => g.AreaName.TrimEnd()).FirstOrDefault(),
            Field5 = _db.MytableEntity3.Where(o => o.Ownership.Equals(c.Ownership)).Select(o => o.OwnerTitle.TrimEnd()).FirstOrDefault(),                   
            Field6 = _db.MytableEntity4.Where(i => c.FieldCode.Equals(i.FieldCode) && myType.Equals(c.MyType)).Select(i => i.FieldTitle).FirstOrDefault(),
            Field7 = _db.MytableEntity4.Where(i => c.FieldCode.Equals(i.FieldCode) && myType.Equals(c.MyType)).Select(i => i.FieldLevel).FirstOrDefault(),

            Field8 = c.FieldD,
            Field9 = c.FieldE,
            Field10 = c.FieldF,
            Field11 = c.FieldG,
            Field12 = c.FieldH,
            Field13 = c.FieldI,
            Field14 = c.FieldJ,
            Field15 = c.FieldK
        }).Distinct().ToList();

    return _reportItems; //return report detail items
}

But now I am faced with a very large database (maybe 60 million records or more?) and the sub-queries are bottlenecking our response times. I would like to fix that and make use of "joins". I see many examples showing how to perform Joins, but I hardly see any examples with multiple joins.

How can I perform multiple joins (within the new 'MyReportItem') on 'MytableEntity2', 'MytableEntity3', 'MytableEntity4', to eliminate the sub-queries for fields 4-7? How do I combine 4 entity tables into one expression? Thank you!

Answer 1

You can use GroupJoin to perform the same as the sub-queries with FirstOrDefault. Note that if you know that each sub-query results in 1 match, you could use Join instead without FirstOrDefault.

var rpt = MytableEntity1
        .Where(c => c.FieldID.Equals(MY_ID)             //Id Field
            && years.Contains(c.PeriodYear)             //PeriodYear/Year
            && quarters.Contains(c.Period)              //Period/Quarters
            && c.MyType.Equals(myType)                  //Special Type
            && areas.Contains(c.Area)                   //Area                   
            && ownerships.Contains(c.Ownership))        //Ownership
        .Where(c => fieldCodes.Contains(c.FieldCode))   //Field Code
        .Where(c => c.Suppress.Equals("0"))             //Suppression is false
        .GroupJoin(MytableEntity2, c => new { c.FieldID, c.MyType, c.Area }, g => new { g.FieldID, g.MyType, g.Area }, (c, gj) => new { c, g = gj.Select(g => g.AreaName.TrimEnd()).FirstOrDefault() })
        .GroupJoin(MytableEntity3, cg => cg.c.Ownership, o => o.Ownership, (cg, oj) => new { cg.c, cg.g, o = oj.Select(o => o.OwnerTitle.TrimEnd()).FirstOrDefault() })
        .GroupJoin(MytableEntity4, cgo => cgo.c.FieldCode, i => i.FieldCode, (cgo, ij) => new { cgo.c, cgo.g, cgo.o, i = ij.FirstOrDefault() })
        .Select(cgoi => new MyReportItem {
            Field1 = cgoi.c.FieldA,
            Field2 = cgoi.c.FieldB,
            Field3 = cgoi.c.FieldC.TrimEnd(),
            Field4 = cgoi.g,
            Field5 = cgoi.o,
            Field6 = cgoi.i.FieldTitle,
            Field7 = cgoi.i.FieldLevel,
            Field8 = cgoi.c.FieldD,
            Field9 = cgoi.c.FieldE,
            Field10 = cgoi.c.FieldF,
            Field11 = cgoi.c.FieldG,
            Field12 = cgoi.c.FieldH,
            Field13 = cgoi.c.FieldI,
            Field14 = cgoi.c.FieldJ,
            Field15 = cgoi.c.FieldK
        }).Distinct().ToList();