How to shorten the Join layers in Dynamic Linq-CodePudding

Whenever you add join a new layer is added and this is difficult to work especially in the where dynamic clause How can I summarize each join with one word I put a query that shows the queries I mean I use here several join and each Gwen adds me t Every field I want to add or query must add a long string of t I want to write the name you named for the join directly

context.Trips
    .GroupJoin(context.Entities, trips => trips.BeneficiaryEntitiesId, beneficiarys => beneficiarys.Id,
        (trips, tripsEntities) => new { trips, tripsEntities })
    .SelectMany(t => t.tripsEntities.DefaultIfEmpty(),
        (t, beneficiarys) => new { t, beneficiarys })
    .GroupJoin(context.Items, t => t.t.trips.ItemId, items => items.Id,
        (t, tripsItems) => new { t, tripsItems })
    .SelectMany(t => t.tripsItems.DefaultIfEmpty(),
        (t, items) => new { t, items })
    .Join(context.Entities, t => t.t.t.t.trips.CarrierEntitiesId,
        carriers => carriers.Id,
        (t, carriers) => new { t, carriers })
    .Join(context.Vehicl, t => t.t.t.t.t.trips.VehiclId,
        vehicls => vehicls.Id,
        (t, vehicls) => new { t, vehicls })
    .Join(context.DefineLists, t => t.vehicls.VehiclType,
        vehiclsType => vehiclsType.Id,
        (t, vehiclsType) => new { t, vehiclsType })
    .Join(context.Drivers, t => t.t.t.t.t.t.t.trips.EntryDriverId,
         endrivers => endrivers.Id,
        (t, endrivers) => new { t, endrivers })
    .GroupJoin(context.Drivers, t => t.t.t.t.t.t.t.t.trips.OutputDriverId,
        oudrivers => oudrivers.Id,
        (t, oudrivers) => new { t, oudrivers })
    .SelectMany(t => t.oudrivers.DefaultIfEmpty(),
        (t, oudrivers) => new { t, oudrivers })
    .Where(@$"(t.t.t.vehiclsType.ListType==1 {SDateWhere}{EDateWhere})
                      {SubWhere}{MainWhere}", StartDate, ed)
    .Select(s => new
    {
        s.t.t.t.t.t.t.t.t.t.trips.Id,
        TripsFullNo = $"{s.t.t.t.t.t.t.t.t.t.trips.TripsFullNo}   {s.t.t.t.t.t.t.t.t.t.trips.TripsNo}",
        OperationTtype =
            s.t.t.t.t.t.t.t.t.t.trips.Loading
                ? operationTtypes[s.t.t.t.t.t.t.t.t.t.trips.OperationTtype ?? 0]
                : $"{Language.ListOperateTransfer} {Language.ListEmpt}",
        Vehicl = $"{s.t.t.t.t.vehicls.LicensePlate}/{s.t.t.t.vehiclsType.ListItem}",
        CarrierEntities = s.t.t.t.t.t.carriers.EntitieName,
        BeneficiaryEntities = s.t.t.t.t.t.t.t.t.beneficiarys!.EntitieName,
        Item = s.t.t.t.t.t.t.items!.NoName,
        Driver = $"{s.t.t.endrivers.DriverLicense}/{s.t.t.endrivers.DriverName}"
    }).OrderByDescending(o=>o.Id)

CodePudding user response：

Here is an example of transforming depth into width as you create many joins. Note that using EF/EF Core properly can make nested joins unnecessary and much easier to work with.

var test = db.GroupJoin(context.Entities,
                 trips => trips.BeneficiaryEntitiesId, beneficiarys => beneficiarys.Id,
                (trips, tripEntities) => new { trips, tripEntities })
            .SelectMany(
                 tb => tb.tripEntities.DefaultIfEmpty(),
                (tb, beneficiaries) => new { tb.trips, beneficiaries })
            .GroupJoin(context.Items,
                 tb => tb.trips.ItemId, items => items.Id,
                (tb, tripsItems) => new { tb.trips, tb.beneficiaries, tripsItems })
            .SelectMany(
                 tbi => tbi.tripsItems.DefaultIfEmpty(),
                (tbi, items) => new { tbi.trips, tbi.beneficiaries, items })
            .Join(context.Entities,
                 tbi => tbi.trips.CarrierEntitiesId, carriers => carriers.Id,
                (tbi, carriers) => new { tbi.trips, tbi.beneficiaries, tbi.items, carriers })
            .Join(context.Vehicl,
                 tbic => tbic.trips.VehiclId, vehicls => vehicls.Id,
                (tbic, vehicls) => new { tbic.trips, tbic.beneficiaries, tbic.items, tbic.carriers, vehicls })
            .Join(context.DefineLists,
                 tbicv => tbicv.vehicls.VehiclType, vehiclsType => vehiclsType.Id,
                (tbicv, vehiclsType) => new { tbicv.trips, tbicv.beneficiaries, tbicv.items, tbicv.carriers, tbicv.vehicls, vehiclsType })
            .Join(context.Drivers,
                 tbicvv => tbicvv.trips.EntryDriverId, endrivers => endrivers.Id,
                (tbicvv, endrivers) => new { tbicvv.trips, tbicvv.beneficiaries, tbicvv.items, tbicvv.carriers, tbicvv.vehicls, tbicvv.vehiclsType, endrivers })
            .GroupJoin(context.Drivers,
                 tbicvve => tbicvve.trips.OutputDriverId, oudrivers => oudrivers.Id,
                (tbicvve, oudrivers) => new { tbicvve.trips, tbicvve.beneficiaries, tbicvve.items, tbicvve.carriers, tbicvve.vehicls, tbicvve.vehiclsType, tbicvve.endrivers, oudrivers })
            .SelectMany(
                 tbicvveo => tbicvveo.oudrivers.DefaultIfEmpty(),
                (tbicvveo, oudrivers) => new { tbicvveo.trips, tbicvveo.beneficiaries, tbicvveo.items, tbicvveo.carriers, tbicvveo.vehicls, tbicvveo.vehiclsType, tbicvveo.endrivers, oudrivers })
            .Where(@$"(trips.vehiclsType.ListType==1 {SDateWhere}{EDateWhere}) {SubWhere}{MainWhere}", StartDate, ed)
            .Select(tbcvvo => new {
                tbcvvo.trips.Id,
                TripsFullNo = $"{tbcvvo.trips.TripsFullNo}   {tbcvvo.trips.TripsNo}",
                OperationTtype =
                    tbcvvo.trips.Loading
                        ? operationTtypes[tbcvvo.trips.OperationTtype ?? 0]
                        : $"{Language.ListOperateTransfer} {Language.ListEmpt}",
                Vehicl = $"{tbcvvo.vehicls.LicensePlate}/{tbcvvo.vehiclsType.ListItem}",
                CarrierEntities = tbcvvo.carriers.EntitieName,
                BeneficiaryEntities = tbcvvo.beneficiaries!.EntitieName,
                Item = tbcvvo.items!.NoName,
                Driver = $"{tbcvvo.endrivers.DriverLicense}/{tbcvvo.endrivers.DriverName}"
            })
            .OrderByDescending(o=>o.Id)

CodePudding user response：

By creating an extension method to represent LeftJoin, which requires using a known class for the results, you can combine the GroupJoin and SelectMany operations with some minor naming issues as well.

Given:

public class AnswerClass<TOuter, TInner> {
    public TOuter O;
    public TInner I;
}

public static class IQueryableExt {
    public static IQueryable<AnswerClass<TOuter, TInner>> LeftJoin<TOuter, TInner, TKey>(this IQueryable<TOuter> outer,
        IQueryable<TInner> inner, Expression<Func<TOuter,TKey>> outerKeyFn, Expression<Func<TInner, TKey>> innerKeyFn)
        => outer.GroupJoin(inner, outerKeyFn, innerKeyFn, (outer, inners) => new { outer, inners })
                .SelectMany(oi => oi.inners.DefaultIfEmpty(), (oi, i) => new AnswerClass<TOuter, TInner> { O = oi.outer, I = i });
}

var test = db.LeftJoin(context.Entities, trips => trips.BeneficiaryEntitiesId, beneficiaries => beneficiaries.Id)
            .LeftJoin(context.Items, oi => oi.O.ItemId, items => items.Id)
            .Join(context.Entities,
                 ooi => ooi.O.O.CarrierEntitiesId, carriers => carriers.Id,
                (ooi, carriers) => new { trips = ooi.O.O, beneficiary = ooi.O.I, items = ooi.I, carriers })
            .Join(context.Vehicl,
                 tbic => tbic.trips.VehiclId, vehicls => vehicls.Id,
                (tbic, vehicls) => new { tbic.trips, tbic.beneficiary, tbic.items, tbic.carriers, vehicls })
            .Join(context.DefineLists,
                 tbicv => tbicv.vehicls.VehiclType, vehiclsType => vehiclsType.Id,
                (tbicv, vehiclsType) => new { tbicv.trips, tbicv.beneficiary, tbicv.items, tbicv.carriers, tbicv.vehicls, vehiclsType })
            .Join(context.Drivers,
                 tbicvv => tbicvv.trips.EntryDriverId, endrivers => endrivers.Id,
                (tbicvv, endrivers) => new { tbicvv.trips, tbicvv.beneficiary, tbicvv.items, tbicvv.carriers, tbicvv.vehicls, tbicvv.vehiclsType, endrivers })
            .LeftJoin(context.Drivers, tbicvve => tbicvve.trips.OutputDriverId, oudrivers => oudrivers.Id)
            // Remap LeftJoin result to something with better naming
            .Select(oi => new { oi.O.trips, oi.O.beneficiary, oi.O.items, oi.O.carriers, oi.O.vehicls, oi.O.vehiclsType, oi.O.endrivers, ouDrivers = oi.I })
            //.Where(@$"(trips.vehiclsType.ListType==1 {SDateWhere}{EDateWhere}) {SubWhere}{MainWhere}", StartDate, ed)
            .Select(tbcvvo => new {
                tbcvvo.trips.Id,
                TripsFullNo = $"{tbcvvo.trips.TripsFullNo}   {tbcvvo.trips.TripsNo}",
                OperationTtype =
                    tbcvvo.trips.Loading
                        ? operationTtypes[tbcvvo.trips.OperationTtype ?? 0]
                        : $"{Language.ListOperateTransfer} {Language.ListEmpt}",
                Vehicl = $"{tbcvvo.vehicls.LicensePlate}/{tbcvvo.vehiclsType.ListItem}",
                CarrierEntities = tbcvvo.carriers.EntitieName,
                BeneficiaryEntities = tbcvvo.beneficiary!.EntitieName,
                Item = tbcvvo.items!.NoName,
                Driver = $"{tbcvvo.endrivers.DriverLicense}/{tbcvvo.endrivers.DriverName}"
            })
            .OrderByDescending(o=>o.Id);