如何将4个实体表组合成一个表达式？

Question

我需要帮助理解如何在Linq到实体语句中执行多个联接。我总是很难理解Linq联接的表达式，尤其是多个联接。我总是用子查询代替。例如：

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
}

但是现在我面对的是一个非常大的数据库(可能有6000万个记录，或者更多？)子查询限制了我们的响应时间。我想解决这个问题，并使用“联接”。我看到了许多演示如何执行Joins的例子，但是我很少看到有多个联接的例子。

如何在“MytableEntity4 2”、“MytableEntity4 3”、“MytableEntity4 4”上执行多个联接(在新的“MyReportItem”中)以消除对字段4-7的子查询？如何将4个实体表组合成一个表达式？谢谢!

Answer 1

您可以使用GroupJoin执行与FirstOrDefault的子查询相同的操作。请注意，如果您知道每个子查询的结果为1匹配，则可以使用Join而不使用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();