Entity framework awful join

Question

await ctx.TBL_MST_TRANSPORT_CONFIG
                            .GroupJoin(ctx.TBL_MST_PROVINCE,
                                a=> a.from_province,
                                b=> b.province_code,
                                (a, b)=> new { a, b })
                            .SelectMany(x => x.b.DefaultIfEmpty(),
                                (aa, bb) => new { aa, bb })
                            .GroupJoin(ctx.TBL_MST_PROVINCE,
                                c=> c.aa.a.to_province,
                                d=> d.province_code,
                                (c, d)=> new { c, d })
                            .SelectMany(x => x.d.DefaultIfEmpty(),
                                (cc, dd) => new { cc, dd })
                            .Where(x =>  x.cc.c.aa.a.from_province == province &&
                                x.cc.c.aa.a.flag == true)
                                .Select(x =>  new TransportDTO
                                {
                                    departure = x.cc.c.aa.a.departure,
                                    destination = x.cc.c.aa.a.destination,
                                    from_province = x.cc.c.aa.a.from_province + "-" + x.cc.d.FirstOrDefault().province_desc,
                                    service_provider = x.cc.c.aa.a.service_provider,
                                    to_province = x.cc.c.aa.a.to_province + "-" + x.dd.province_desc,
                                    transport_leave_dc = x.cc.c.aa.a.transport_leave_dc,
                                    transport_no = x.cc.c.aa.a.transport_no,
                                    transport_type = x.cc.c.aa.a.transport_type,
                                })

anywhere to reduce this x.cc.c.aa.a, it look awfully bad, is there any better way to do it? I forsee it will for each join will just increase the size of the finding

it also generate awful sql

exec sp_executesql N'SELECT 
    1 AS [C1], 
    [Project5].[departure] AS [departure], 
    [Project5].[destination] AS [destination], 
    [Project5].[from_province] + N''-'' + CASE WHEN ([Project5].[C1] IS NULL) THEN N'''' ELSE [Project5].[C2] END AS [C2], 
    [Project5].[service_provider] AS [service_provider], 
    [Project5].[to_province] + N''-'' + CASE WHEN ([Project5].[province_desc] IS NULL) THEN N'''' ELSE [Project5].[province_desc] END AS [C3], 
    [Project5].[transport_leave_dc] AS [transport_leave_dc], 
    [Project5].[transport_no] AS [transport_no], 
    [Project5].[transport_type] AS [transport_type]
    FROM ( SELECT 
        [Project3].[transport_type] AS [transport_type], 
        [Project3].[from_province] AS [from_province], 
        [Project3].[to_province] AS [to_province], 
        [Project3].[service_provider] AS [service_provider], 
        [Project3].[transport_no] AS [transport_no], 
        [Project3].[transport_leave_dc] AS [transport_leave_dc], 
        [Project3].[departure] AS [departure], 
        [Project3].[destination] AS [destination], 
        [Project3].[province_desc] AS [province_desc], 
        [Project3].[C1] AS [C1], 
        (SELECT TOP (1) 
            [Extent5].[province_desc] AS [province_desc]
            FROM [dbo].[TBL_MST_PROVINCE] AS [Extent5]
            WHERE [Project3].[to_province] = [Extent5].[province_code]) AS [C2]
        FROM ( SELECT 
            [Project2].[transport_type] AS [transport_type], 
            [Project2].[from_province] AS [from_province], 
            [Project2].[to_province] AS [to_province], 
            [Project2].[service_provider] AS [service_provider], 
            [Project2].[transport_no] AS [transport_no], 
            [Project2].[transport_leave_dc] AS [transport_leave_dc], 
            [Project2].[departure] AS [departure], 
            [Project2].[destination] AS [destination], 
            [Project2].[province_desc] AS [province_desc], 
            [Project2].[C1] AS [C1]
            FROM ( SELECT 
                [Filter1].[transport_type] AS [transport_type], 
                [Filter1].[from_province] AS [from_province], 
                [Filter1].[to_province] AS [to_province], 
                [Filter1].[service_provider] AS [service_provider], 
                [Filter1].[transport_no] AS [transport_no], 
                [Filter1].[transport_leave_dc] AS [transport_leave_dc], 
                [Filter1].[departure] AS [departure], 
                [Filter1].[destination] AS [destination], 
                [Extent3].[province_desc] AS [province_desc], 
                (SELECT TOP (1) 
                    [Extent4].[province_desc] AS [province_desc]
                    FROM [dbo].[TBL_MST_PROVINCE] AS [Extent4]
                    WHERE [Filter1].[to_province] = [Extent4].[province_code]) AS [C1]
                FROM   (SELECT [Extent1].[transport_type] AS [transport_type], [Extent1].[from_province] AS [from_province], [Extent1].[to_province] AS [to_province], [Extent1].[service_provider] AS [service_provider], [Extent1].[transport_no] AS [transport_no], [Extent1].[transport_leave_dc] AS [transport_leave_dc], [Extent1].[departure] AS [departure], [Extent1].[destination] AS [destination]
                    FROM  [dbo].[TBL_MST_TRANSPORT_CONFIG] AS [Extent1]
                    LEFT OUTER JOIN [dbo].[TBL_MST_PROVINCE] AS [Extent2] ON [Extent1].[from_province] = [Extent2].[province_code]
                    WHERE 1 = [Extent1].[flag] ) AS [Filter1]
                LEFT OUTER JOIN [dbo].[TBL_MST_PROVINCE] AS [Extent3] ON [Filter1].[to_province] = [Extent3].[province_code]
                WHERE [Filter1].[from_province] = @p__linq__0
            )  AS [Project2]
        )  AS [Project3]
    )  AS [Project5]',N'@p__linq__0 varchar(8000)',@p__linq__0='01'

am i doing correct?

ever i format it like this, it still generate very bad query in sql

await ctx.TBL_MST_TRANSPORT_CONFIG
                            .GroupJoin(ctx.TBL_MST_PROVINCE,
                                a=> a.from_province,
                                b=> b.province_code,
                                (a, b)=> new { a, b = b.DefaultIfEmpty() })
                            .GroupJoin(ctx.TBL_MST_PROVINCE,
                                c=> c.a.to_province,
                                d=> d.province_code,
                                (c, d)=> new { c, d = d.DefaultIfEmpty() })
                            .Where(x =>  x.c.a.from_province == province &&
                                x.c.a.flag == true)

Answer 1

If you are doing multiple (left) joins in lambda syntax in LINQ, it really helps if you:

1. Name your lambda variables meaningful names: I prefer to name anonymous types by their member names concatenated (in this case, with _ due to the repeated join). I suffix GroupJoin results with j and GroupBy results with g.

2. Flatten your anonymous types as you go, including only the needed members as well.

If you do this, you may even discover you have an inconsistency in your query: why do you use the left join (GroupJoin/SelectMany) to flatten the from_province and then use FirstOrDefault on the join result anyway? You don't need to do both. Below, I kept the left join syntax:

var ans = await ctx.TBL_MST_TRANSPORT_CONFIG
                        .GroupJoin(ctx.TBL_MST_PROVINCE,
                            tc => tc.from_province,
                            pr => pr.province_code,
                            (tc, frprj)=> new { tc, frprj })
                        .SelectMany(tc_frprj => tc_frprj.frprj.DefaultIfEmpty(),
                            (tc_frprj, frpr) => new { tc_frprj.tc, frpr })
                        .GroupJoin(ctx.TBL_MST_PROVINCE,
                            tc_frpr => tc_frpr.tc.to_province,
                            topr => topr.province_code,
                            (tc_frpr, toprj)=> new { tc_frpr.tc, tc_frpr.frpr, toprj })
                        .SelectMany(tc_frpr_toprj => tc_frpr_toprj.toprj.DefaultIfEmpty(),
                            (tc_frpr_toprj, topr) => new { tc_frpr_toprj.tc, tc_frpr_toprj.frpr, topr })
                        .Where(tc_frpr_topr =>  tc_frpr_topr.tc.from_province == province &&
                            tc_frpr_topr.tc.flag)
                        .Select(tc_frpr_topr => new TransportDTO {
                            departure = tc_frpr_topr.tc.departure,
                            destination = tc_frpr_topr.tc.destination,
                            from_province = tc_frpr_topr.tc.from_province + "-" + tc_frpr_topr.frpr.province_desc,
                            service_provider = tc_frpr_topr.tc.service_provider,
                            to_province = tc_frpr_topr.tc.to_province + "-" + tc_frpr_topr.topr.province_desc,
                            transport_leave_dc = tc_frpr_topr.tc.transport_leave_dc,
                            transport_no = tc_frpr_topr.tc.transport_no,
                            transport_type = tc_frpr_topr.tc.transport_type,
                        });

NOTE: Since you only use frpr.province_desc and trpr.province_desc in the final result, you could change the SelectMany to just forward those fields (e.g. frprd = frpr.provice_desc) and change the lambda names (e.g. tc_frprd) and not the entire frpr object. It should make no difference to the final translated SQL query.

PS Don't compare booleans against true or false.

Answer 2

That's why LINQ Query syntax is better when you have a lot of joins.

var query =
    from tc in ctx.TBL_MST_TRANSPORT_CONFIG
    join fp in ctx.TBL_MST_PROVINCE on tc.from_province equals fp.province_code into fpj
    from fp in fpj.DefaultIfEmpty()
    join tp in ctx.TBL_MST_PROVINCE on tc.to_province equals tp.province_code into tpj
    from tp in tpj.DefaultIfEmpty()
    where tc.from_province == province && tc.flag == true
    select new TransportDTO
    {
        departure = tc.departure,
        destination = tc.destination,
        from_province = tc.from_province + "-" + fp.province_desc,
        service_provider = tc.service_provider,
        to_province = tc.to_province + "-" + tp.province_desc,
        transport_leave_dc = tc.transport_leave_dc,
        transport_no = tc.transport_no,
        transport_type = tc.transport_type,
    }

It does not guarantee better SQL, but maintenance and readability improved a lot.