Issue Report - Function MergeTable() 返回

Issue Report - Function MergeTable().pdf

await _db.Queryable<BaseRate>()

    .Select((baseRate) => new { baseRate.WeightId ,baseRate.ValueId })

    .MergeTable()

    .InnerJoinIF<BaseDataWeight>(false,(x, baseDataWeight) =>

        x.WeightId == baseDataWeight.WeightId)

    .InnerJoinIF<BaseDataValue>(false, (x, baseDataWeight, baseDataValue) =>

        x.ValueId == baseDataValue.ValueId)

    .ToListAsync();

稳定报错

Issue Report - Function MergeTable()

The Problem

当我使用方法 MergeTable() 时，后续再调用 InnerJoinIF<T>() 方法时，会出现错误。错误信息如下：

1. 程序报错

   Microsoft.Data.SqlClient.SqlException: “Incorrect syntax near ')'.”

2. 报错的代码

1. C# 代码

      var data = await _db.Queryable<MappingArBaseRate, BaseRate>((mappingArBaseRate, baseRate) =>    new JoinQueryInfos(JoinType.Inner, mappingArBaseRate.RateId == baseRate.RateId && mappingArBaseRate.CustomerVersionId == customerVersionId))
       .InnerJoinIF<BaseDataCarton>(rateTypeTable == "Carton", (mappingArBaseRate, baseRate, baseDataCarton) => baseRate.CartonId == baseDataCarton.CartonId)
       .InnerJoinIF<BaseDataClassID>(rateTypeTable == "Class", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId) => baseRate.ClassId == baseDataClassId.ClassIdId)
       .InnerJoinIF<BaseDataHour>(rateTypeTable == "Hour", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour) => baseRate.HourId == baseDataHour.HourId)
       .InnerJoinIF<BaseDataLinear>(rateTypeTable == "Linear", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear) =>
           baseRate.LinearId == baseDataLinear.LinearId)
       .InnerJoinIF<BaseDataLoad>(rateTypeTable == "Load", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad) =>
           baseRate.LoadId == baseDataLoad.LoadId)
       .InnerJoinIF<BaseDataMileage>(rateTypeTable == "Mileage", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad, baseDataMileage) =>
           baseRate.MileageId == baseDataMileage.MileageId)
       .InnerJoinIF<BaseDataPallet>(rateTypeTable == "Pallet", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad, baseDataMileage, baseDataPallet) =>
           baseRate.PalletId == baseDataPallet.PalletId)
       .InnerJoinIF<BaseDataPltSpace>(rateTypeTable == "PltSpace", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad, baseDataMileage, baseDataPallet, baseDataPltSpace) =>
           baseRate.PltSpaceId == baseDataPltSpace.PltSpaceId)
       .InnerJoinIF<BaseDataRevenue>(rateTypeTable == "Revenue", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad, baseDataMileage, baseDataPallet, baseDataPltSpace, baseDataRevenue) =>
           baseRate.RevenueId == baseDataRevenue.RevenueId)
       .InnerJoinIF<BaseDataStop>(rateTypeTable == "Stop", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad, baseDataMileage, baseDataPallet, baseDataPltSpace, baseDataRevenue, baseDataStop) =>
           baseRate.StopId == baseDataStop.StopId)    //超过12张表先合并再继续连接
       .Select((mappingArBaseRate, baseRate) => new
       {
           baseRate.TripId, mappingArBaseRate.CustomerVersionId, baseRate.WeightId ,mappingArBaseRate.LaneName,baseRate.LaneId,baseRate.TaskTypeId,
           mappingArBaseRate.RateId,mappingArBaseRate.DirectionId, mappingArBaseRate.CalculationOptionId, mappingArBaseRate.Markup, mappingArBaseRate.BaseRate,
           baseRate.VolumeId,baseRate.CartonId,baseRate.ValueId
       })
       .MergeTable()
       .InnerJoinIF<BaseDataTrip>(rateTypeTable == "Trip", (x, baseDataTrip) =>
           x.TripId == baseDataTrip.TripId)
   
       .InnerJoinIF<BaseDataValue>(rateTypeTable == "Value", (x, baseDataTrip, baseDataValue) =>
           x.ValueId == baseDataValue.ValueId)
   

2. SQL 代码

      SELECT
           [x].*
       FROM
           (            SELECT
                   *
               FROM
                   (                    SELECT
                           *
                       FROM
                           (                            SELECT
                                   [baseRate].[TripID] AS [TripId],
                                   [mappingArBaseRate].[CustomerVersionID] AS [CustomerVersionId],
                                   [baseRate].[WeightID] AS [WeightId],
                                   [mappingArBaseRate].[LaneName] AS [LaneName],
                                   [baseRate].[LaneID] AS [LaneId],
                                   [baseRate].[TaskTypeID] AS [TaskTypeId],
                                   [mappingArBaseRate].[RateID] AS [RateId],
                                   [mappingArBaseRate].[DirectionID] AS [DirectionId],
                                   [mappingArBaseRate].[CalculationOptionID] AS [CalculationOptionId],
                                   [mappingArBaseRate].[Markup] AS [Markup],
                                   [mappingArBaseRate].[BaseRate] AS [BaseRate],
                                   [baseRate].[VolumeID] AS [VolumeId],
                                   [baseRate].[CartonID] AS [CartonId],
                                   [baseRate].[ValueID] AS [ValueId]                            FROM
                                   [Def_Mapping_AR_BaseRate] [mappingArBaseRate]                                Inner JOIN [Def_BaseRate] [baseRate] ON (
                                       (
                                           [mappingArBaseRate].[RateID] = [baseRate].[RateID]
                                       )                                    AND (
                                           [mappingArBaseRate].[CustomerVersionID] = 5904
                                       )
                                   )
                           ) MergeTable
                   )
           ) [x]
   

3. 正常的代码

1. C# 代码

      var data = await _db.Queryable<MappingArBaseRate, BaseRate>((mappingArBaseRate, baseRate) =>    new JoinQueryInfos(JoinType.Inner, mappingArBaseRate.RateId == baseRate.RateId && mappingArBaseRate.CustomerVersionId == customerVersionId))
       .InnerJoinIF<BaseDataCarton>(rateTypeTable == "Carton", (mappingArBaseRate, baseRate, baseDataCarton) => baseRate.CartonId == baseDataCarton.CartonId)
       .InnerJoinIF<BaseDataClassID>(rateTypeTable == "Class", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId) => baseRate.ClassId == baseDataClassId.ClassIdId)
       .InnerJoinIF<BaseDataHour>(rateTypeTable == "Hour", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour) => baseRate.HourId == baseDataHour.HourId)
       .InnerJoinIF<BaseDataLinear>(rateTypeTable == "Linear", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear) =>
           baseRate.LinearId == baseDataLinear.LinearId)
       .InnerJoinIF<BaseDataLoad>(rateTypeTable == "Load", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad) =>
           baseRate.LoadId == baseDataLoad.LoadId)
       .InnerJoinIF<BaseDataMileage>(rateTypeTable == "Mileage", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad, baseDataMileage) =>
           baseRate.MileageId == baseDataMileage.MileageId)
       .InnerJoinIF<BaseDataPallet>(rateTypeTable == "Pallet", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad, baseDataMileage, baseDataPallet) =>
           baseRate.PalletId == baseDataPallet.PalletId)
       .InnerJoinIF<BaseDataPltSpace>(rateTypeTable == "PltSpace", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad, baseDataMileage, baseDataPallet, baseDataPltSpace) =>
           baseRate.PltSpaceId == baseDataPltSpace.PltSpaceId)
       .InnerJoinIF<BaseDataRevenue>(rateTypeTable == "Revenue", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad, baseDataMileage, baseDataPallet, baseDataPltSpace, baseDataRevenue) =>
           baseRate.RevenueId == baseDataRevenue.RevenueId)
       .InnerJoinIF<BaseDataStop>(rateTypeTable == "Stop", (mappingArBaseRate, baseRate, baseDataCarton, baseDataClassId, baseDataHour, baseDataLinear, baseDataLoad, baseDataMileage, baseDataPallet, baseDataPltSpace, baseDataRevenue, baseDataStop) =>
           baseRate.StopId == baseDataStop.StopId)    //超过12张表先合并再继续连接
       .Select((mappingArBaseRate, baseRate) => new
       {
           baseRate.TripId, mappingArBaseRate.CustomerVersionId, baseRate.WeightId ,mappingArBaseRate.LaneName,baseRate.LaneId,baseRate.TaskTypeId,
           mappingArBaseRate.RateId,mappingArBaseRate.DirectionId, mappingArBaseRate.CalculationOptionId, mappingArBaseRate.Markup, mappingArBaseRate.BaseRate,
           baseRate.VolumeId,baseRate.CartonId,baseRate.ValueId
       })
       .MergeTable()
       .InnerJoinIF<BaseDataTrip>(rateTypeTable == "Trip", (x, baseDataTrip) =>
           x.TripId == baseDataTrip.TripId)    //注释此段代码
       //.InnerJoinIF<BaseDataValue>(rateTypeTable == "Value", (x, baseDataTrip, baseDataValue) =>
           //x.ValueId == baseDataValue.ValueId)
   

2. SQL 代码

      SELECT
       [x].*
       FROM
           (            SELECT
                   *
               FROM
                   (                    SELECT
                           [baseRate].[TripID] AS [TripId],
                           [mappingArBaseRate].[CustomerVersionID] AS [CustomerVersionId],
                           [baseRate].[WeightID] AS [WeightId],
                           [mappingArBaseRate].[LaneName] AS [LaneName],
                           [baseRate].[LaneID] AS [LaneId],
                           [baseRate].[TaskTypeID] AS [TaskTypeId],
                           [mappingArBaseRate].[RateID] AS [RateId],
                           [mappingArBaseRate].[DirectionID] AS [DirectionId],
                           [mappingArBaseRate].[CalculationOptionID] AS [CalculationOptionId],
                           [mappingArBaseRate].[Markup] AS [Markup],
                           [mappingArBaseRate].[BaseRate] AS [BaseRate],
                           [baseRate].[VolumeID] AS [VolumeId],
                           [baseRate].[CartonID] AS [CartonId],
                           [baseRate].[ValueID] AS [ValueId]                    FROM
                           [Def_Mapping_AR_BaseRate] [mappingArBaseRate]                        Inner JOIN [Def_BaseRate] [baseRate] ON (
                               (
                                   [mappingArBaseRate].[RateID] = [baseRate].[RateID]
                               )                            AND (
                                   [mappingArBaseRate].[CustomerVersionID] = @CustomerVersionId0
                               )
                           )
                   ) MergeTable
           ) [x]
   

4. 结论 多嵌套了一层 SELECT导致报错