Hi. I got a query to a database table with Include method call in which I just try to filter out related entity by objectId
this one entity should give me some properties in profile
and then GetQueryAsync is executed on this query(IQueriable) with a mapper and some generic filters
for some reason, it takes properties not from the filtered entity, but from the first related entity in the database (which is taken by FirstOrDefault method). In other words, a Where clause from the first screen doesn't make sense.
A query taken from profiler returns these two CostGroupMember properties in columns with no name, and also the correct properties from [t2] subquery. However, it doesn't use [t2] results in mapper
exec sp_executesql N'
SELECT [t].[ObjectID], [t].[ApportionmentMethod], [t].[BidQuantity], [t].[BidQuantitySystemOfMeasure], [t].[CalculatedIndirectType], [t].[CategorizeAsCostCategory], [t].[CategorizeAsMinorityTypeREF], [t].[ChangeOrderREF], [t].[ComparisonCostSource], [t].[ComponentType], [t].[CostMethod], [t].[CreatedOn], [t].[EstimateREF], [t].[Factor], [t].[FactorREF], [t].[FactorSystemOfMeasure], [t].[HistoricalUnitCost], [t].[HistoricalUnitCostSystemOfMeasure], [t].[ImperialUnitOfMeasure], [t].[IsCompleted], [t].[IsDeleted], [t].[IsFactorUsed], [t].[IsFlagged], [t].[IsSuppressed], [t].[ItemNumber], [t].[ItemREF], [t].[ItemTaskType], [t].[ItemTemplateREF], [t].[JobCostIDCode], [t].[JobCostIDREF], [t].[MetricUnitOfMeasure], [t].[ModifiedByInitials], [t].[ModifiedByREF], [t].[ModifiedOn], [t].[Name], [t].[NodeDepth], [t].[NodeOrder], [t].[NodeOrderModifiedOn], [t].[Notes], [t].[ParentREF], [t].[Percentage], [t].[PercentageOfCostCategory], [t].[PercentageOfMinorityTypeREF], [t].[PlugUnitPriceSystemOfMeasure], [t].[Quantity], [t].[QuantitySystemOfMeasure], [t].[RichTextName], [t].[SubcontractedUnitCost], [t].[SubcontractedUnitCostSystemOfMeasure], [t].[SummaryValueCategoryID], [t].[SummaryValueSubCategoryREF], [t].[TakeoffQuantity], [t].[TakeoffQuantitySystemOfMeasure], [t].[TaskJobCostIDCode], [t].[TaskJobCostIDREF], [t].[TaskType], [t].[TotalManHoursCalculatedCategoryREF], [t].[TotalManHoursCalculatedSubcategoryREF], [t].[UnitCost], [t].[UnitCostSystemOfMeasure], [t].[UnitSummaryValueCost], [t].[UnitSummaryValueCostSystemOfMeasure], [t].[WorkSubtypeREF], [t].[WorkTypeREF], [t].[WorkersCompCodeREF], CAST([t].[ApportionmentMethod] AS int),
(
SELECT TOP(1) [c].[ApportionmentPercent]
FROM [CostGroupMember] AS [c]
WHERE [t].[ObjectID] = [c].[ItemTaskComponentREF]), (
SELECT TOP(1) [c0].[ApportionmentQuantity]
FROM [CostGroupMember] AS [c0]
WHERE [t].[ObjectID] = [c0].[ItemTaskComponentREF]), CASE
WHEN [t].[JobCostIDREF] = ''00000000-0000-0000-0000-000000000000'' THEN [t].[JobCostIDCode]
ELSE [j].[Name]
END, CASE
WHEN [t].[JobCostIDREF] = ''00000000-0000-0000-0000-000000000000'' THEN NULL
ELSE [j].[Description]
END, CASE
WHEN [t].[TaskJobCostIDREF] = ''00000000-0000-0000-0000-000000000000'' THEN [t].[TaskJobCostIDCode]
ELSE [j0].[Name]
END, CASE
WHEN [t].[TaskJobCostIDREF] = ''00000000-0000-0000-0000-000000000000'' THEN NULL
ELSE [j0].[Description]
END, CASE
WHEN [t].[WorkSubtypeREF] = ''00000000-0000-0000-0000-000000000000'' THEN NULL
ELSE [t0].[Name]
END, CASE
WHEN [t].[WorkTypeREF] = ''00000000-0000-0000-0000-000000000000'' THEN NULL
ELSE [t1].[Name]
END, [j].[ObjectID], [j0].[ObjectID], [t0].[ObjectID], [t1].[ObjectID], [t2].[ObjectID], [t2].[ApportionmentPercent], [t2].[ApportionmentQuantity], [t2].[CreatedByInitials], [t2].[CreatedByREF], [t2].[CreatedOn], [t2].[EstimateREF], [t2].[IsDeleted], [t2].[ItemTaskComponentREF], [t2].[ModifiedByInitials], [t2].[ModifiedByREF], [t2].[ModifiedOn], [t2].[OwnerREF]
FROM (
SELECT [i].[ObjectID], [i].[ApportionmentMethod], [i].[BidQuantity], [i].[BidQuantitySystemOfMeasure], [i].[CalculatedIndirectType], [i].[CategorizeAsCostCategory],
[i].[CategorizeAsMinorityTypeREF], [i].[ChangeOrderREF], [i].[ComparisonCostSource], [i].[ComponentType], [i].[CostMethod], [i].[CreatedOn], [i].[EstimateREF], [i].[Factor], [i].[FactorREF], [i].[FactorSystemOfMeasure], [i].[HistoricalUnitCost], [i].[HistoricalUnitCostSystemOfMeasure], [i].[ImperialUnitOfMeasure], [i].[IsCompleted], [i].[IsDeleted], [i].[IsFactorUsed], [i].[IsFlagged], [i].[IsSuppressed], [i].[ItemNumber], [i].[ItemREF], [i].[ItemTaskType], [i].[ItemTemplateREF], [i].[JobCostIDCode], [i].[JobCostIDREF], [i].[MetricUnitOfMeasure], [i].[ModifiedByInitials], [i].[ModifiedByREF], [i].[ModifiedOn], [i].[Name], [i].[NodeDepth], [i].[NodeOrder], [i].[NodeOrderModifiedOn], [i].[Notes], [i].[ParentREF], [i].[Percentage], [i].[PercentageOfCostCategory], [i].[PercentageOfMinorityTypeREF], [i].[PlugUnitPriceSystemOfMeasure], [i].[Quantity], [i].[QuantitySystemOfMeasure], [i].[RichTextName], [i].[SubcontractedUnitCost], [i].[SubcontractedUnitCostSystemOfMeasure], [i].[SummaryValueCategoryID], [i].[SummaryValueSubCategoryREF], [i].[TakeoffQuantity], [i].[TakeoffQuantitySystemOfMeasure], [i].[TaskJobCostIDCode], [i].[TaskJobCostIDREF], [i].[TaskType], [i].[TotalManHoursCalculatedCategoryREF], [i].[TotalManHoursCalculatedSubcategoryREF], [i].[UnitCost], [i].[UnitCostSystemOfMeasure], [i].[UnitSummaryValueCost], [i].[UnitSummaryValueCostSystemOfMeasure], [i].[WorkSubtypeREF], [i].[WorkTypeREF], [i].[WorkersCompCodeREF]
FROM (
SELECT itc.* FROM ItemTaskComponent itc INNER JOIN Estimate ON itc.EstimateREF = Estimate.ObjectID AND Estimate.IsDeleted = 0 INNER JOIN CostGroupMember cgm on cgm.ItemTaskComponentREF = itc.ObjectID WHERE cgm.OwnerREF = @objectId
) AS [i]
WHERE ([i].[IsDeleted] <> CAST(1 AS bit)) AND ([i].[EstimateREF] = @TypedProperty_2)
ORDER BY [i].[Name]
OFFSET @__p_3 ROWS FETCH NEXT @p_4 ROWS ONLY
) AS [t]
LEFT JOIN [JobCostID] AS [j] ON [t].[JobCostIDREF] = [j].[ObjectID]
LEFT JOIN [JobCostID] AS [j0] ON [t].[TaskJobCostIDREF] = [j0].[ObjectID]
LEFT JOIN (
SELECT [s].[ObjectID], [s].[Name]
FROM [Subcategory] AS [s]
WHERE [s].[CategoryType] = 32
) AS [t0] ON [t].[WorkSubtypeREF] = [t0].[ObjectID]
LEFT JOIN (
SELECT [c1].[ObjectID], [c1].[Name]
FROM [Category] AS [c1]
WHERE [c1].[CategoryType] = 32
) AS [t1] ON [t].[WorkTypeREF] = [t1].[ObjectID]
LEFT JOIN (
SELECT [c2].[ObjectID], [c2].[ApportionmentPercent], [c2].[ApportionmentQuantity], [c2].[CreatedByInitials], [c2].[CreatedByREF],
[c2].[CreatedOn], [c2].[EstimateREF], [c2].[IsDeleted], [c2].[ItemTaskComponentREF], [c2].[ModifiedByInitials], [c2].[ModifiedByREF], [c2].[ModifiedOn], [c2].[OwnerREF]
FROM [CostGroupMember] AS [c2]
WHERE ([c2].[OwnerREF] = @objectId_1) AND ([c2].[IsDeleted] <> CAST(1 AS bit))
) AS [t2] ON [t].[ObjectID] = [t2].[ItemTaskComponentREF]
ORDER BY [t].[Name], [t].[ObjectID], [j].[ObjectID], [j0].[ObjectID],
[t0].[ObjectID], [t1].[ObjectID], [t2].[ObjectID]',
N'@objectId uniqueidentifier,@__TypedProperty_2 uniqueidentifier,@p_3 int,@p_4 int,@__objectId_1 uniqueidentifier',
@objectId='0ED90CF5-2F33-4B30-964C-A2D401842958',
@TypedProperty_2='C84A5BE7-85EB-493C-8E47-4F3067323E75',
@p_3=0,@__p_4=2,@objectId_1='0ED90CF5-2F33-4B30-964C-A2D401842958'
Hi. I got a query to a database table with Include method call in which I just try to filter out related entity by objectId
this one entity should give me some properties in profile
and then GetQueryAsync is executed on this query(IQueriable) with a mapper and some generic filters
for some reason, it takes properties not from the filtered entity, but from the first related entity in the database (which is taken by FirstOrDefault method). In other words, a Where clause from the first screen doesn't make sense.
A query taken from profiler returns these two CostGroupMember properties in columns with no name, and also the correct properties from [t2] subquery. However, it doesn't use [t2] results in mapper
exec sp_executesql N' SELECT [t].[ObjectID], [t].[ApportionmentMethod], [t].[BidQuantity], [t].[BidQuantitySystemOfMeasure], [t].[CalculatedIndirectType], [t].[CategorizeAsCostCategory], [t].[CategorizeAsMinorityTypeREF], [t].[ChangeOrderREF], [t].[ComparisonCostSource], [t].[ComponentType], [t].[CostMethod], [t].[CreatedOn], [t].[EstimateREF], [t].[Factor], [t].[FactorREF], [t].[FactorSystemOfMeasure], [t].[HistoricalUnitCost], [t].[HistoricalUnitCostSystemOfMeasure], [t].[ImperialUnitOfMeasure], [t].[IsCompleted], [t].[IsDeleted], [t].[IsFactorUsed], [t].[IsFlagged], [t].[IsSuppressed], [t].[ItemNumber], [t].[ItemREF], [t].[ItemTaskType], [t].[ItemTemplateREF], [t].[JobCostIDCode], [t].[JobCostIDREF], [t].[MetricUnitOfMeasure], [t].[ModifiedByInitials], [t].[ModifiedByREF], [t].[ModifiedOn], [t].[Name], [t].[NodeDepth], [t].[NodeOrder], [t].[NodeOrderModifiedOn], [t].[Notes], [t].[ParentREF], [t].[Percentage], [t].[PercentageOfCostCategory], [t].[PercentageOfMinorityTypeREF], [t].[PlugUnitPriceSystemOfMeasure], [t].[Quantity], [t].[QuantitySystemOfMeasure], [t].[RichTextName], [t].[SubcontractedUnitCost], [t].[SubcontractedUnitCostSystemOfMeasure], [t].[SummaryValueCategoryID], [t].[SummaryValueSubCategoryREF], [t].[TakeoffQuantity], [t].[TakeoffQuantitySystemOfMeasure], [t].[TaskJobCostIDCode], [t].[TaskJobCostIDREF], [t].[TaskType], [t].[TotalManHoursCalculatedCategoryREF], [t].[TotalManHoursCalculatedSubcategoryREF], [t].[UnitCost], [t].[UnitCostSystemOfMeasure], [t].[UnitSummaryValueCost], [t].[UnitSummaryValueCostSystemOfMeasure], [t].[WorkSubtypeREF], [t].[WorkTypeREF], [t].[WorkersCompCodeREF], CAST([t].[ApportionmentMethod] AS int), ( SELECT TOP(1) [c].[ApportionmentPercent] FROM [CostGroupMember] AS [c] WHERE [t].[ObjectID] = [c].[ItemTaskComponentREF]), ( SELECT TOP(1) [c0].[ApportionmentQuantity] FROM [CostGroupMember] AS [c0] WHERE [t].[ObjectID] = [c0].[ItemTaskComponentREF]), CASE WHEN [t].[JobCostIDREF] = ''00000000-0000-0000-0000-000000000000'' THEN [t].[JobCostIDCode] ELSE [j].[Name] END, CASE WHEN [t].[JobCostIDREF] = ''00000000-0000-0000-0000-000000000000'' THEN NULL ELSE [j].[Description] END, CASE WHEN [t].[TaskJobCostIDREF] = ''00000000-0000-0000-0000-000000000000'' THEN [t].[TaskJobCostIDCode] ELSE [j0].[Name] END, CASE WHEN [t].[TaskJobCostIDREF] = ''00000000-0000-0000-0000-000000000000'' THEN NULL ELSE [j0].[Description] END, CASE WHEN [t].[WorkSubtypeREF] = ''00000000-0000-0000-0000-000000000000'' THEN NULL ELSE [t0].[Name] END, CASE WHEN [t].[WorkTypeREF] = ''00000000-0000-0000-0000-000000000000'' THEN NULL ELSE [t1].[Name] END, [j].[ObjectID], [j0].[ObjectID], [t0].[ObjectID], [t1].[ObjectID], [t2].[ObjectID], [t2].[ApportionmentPercent], [t2].[ApportionmentQuantity], [t2].[CreatedByInitials], [t2].[CreatedByREF], [t2].[CreatedOn], [t2].[EstimateREF], [t2].[IsDeleted], [t2].[ItemTaskComponentREF], [t2].[ModifiedByInitials], [t2].[ModifiedByREF], [t2].[ModifiedOn], [t2].[OwnerREF] FROM ( SELECT [i].[ObjectID], [i].[ApportionmentMethod], [i].[BidQuantity], [i].[BidQuantitySystemOfMeasure], [i].[CalculatedIndirectType], [i].[CategorizeAsCostCategory], [i].[CategorizeAsMinorityTypeREF], [i].[ChangeOrderREF], [i].[ComparisonCostSource], [i].[ComponentType], [i].[CostMethod], [i].[CreatedOn], [i].[EstimateREF], [i].[Factor], [i].[FactorREF], [i].[FactorSystemOfMeasure], [i].[HistoricalUnitCost], [i].[HistoricalUnitCostSystemOfMeasure], [i].[ImperialUnitOfMeasure], [i].[IsCompleted], [i].[IsDeleted], [i].[IsFactorUsed], [i].[IsFlagged], [i].[IsSuppressed], [i].[ItemNumber], [i].[ItemREF], [i].[ItemTaskType], [i].[ItemTemplateREF], [i].[JobCostIDCode], [i].[JobCostIDREF], [i].[MetricUnitOfMeasure], [i].[ModifiedByInitials], [i].[ModifiedByREF], [i].[ModifiedOn], [i].[Name], [i].[NodeDepth], [i].[NodeOrder], [i].[NodeOrderModifiedOn], [i].[Notes], [i].[ParentREF], [i].[Percentage], [i].[PercentageOfCostCategory], [i].[PercentageOfMinorityTypeREF], [i].[PlugUnitPriceSystemOfMeasure], [i].[Quantity], [i].[QuantitySystemOfMeasure], [i].[RichTextName], [i].[SubcontractedUnitCost], [i].[SubcontractedUnitCostSystemOfMeasure], [i].[SummaryValueCategoryID], [i].[SummaryValueSubCategoryREF], [i].[TakeoffQuantity], [i].[TakeoffQuantitySystemOfMeasure], [i].[TaskJobCostIDCode], [i].[TaskJobCostIDREF], [i].[TaskType], [i].[TotalManHoursCalculatedCategoryREF], [i].[TotalManHoursCalculatedSubcategoryREF], [i].[UnitCost], [i].[UnitCostSystemOfMeasure], [i].[UnitSummaryValueCost], [i].[UnitSummaryValueCostSystemOfMeasure], [i].[WorkSubtypeREF], [i].[WorkTypeREF], [i].[WorkersCompCodeREF] FROM ( SELECT itc.* FROM ItemTaskComponent itc INNER JOIN Estimate ON itc.EstimateREF = Estimate.ObjectID AND Estimate.IsDeleted = 0 INNER JOIN CostGroupMember cgm on cgm.ItemTaskComponentREF = itc.ObjectID WHERE cgm.OwnerREF = @objectId ) AS [i] WHERE ([i].[IsDeleted] <> CAST(1 AS bit)) AND ([i].[EstimateREF] = @TypedProperty_2) ORDER BY [i].[Name] OFFSET @__p_3 ROWS FETCH NEXT @p_4 ROWS ONLY ) AS [t] LEFT JOIN [JobCostID] AS [j] ON [t].[JobCostIDREF] = [j].[ObjectID] LEFT JOIN [JobCostID] AS [j0] ON [t].[TaskJobCostIDREF] = [j0].[ObjectID] LEFT JOIN ( SELECT [s].[ObjectID], [s].[Name] FROM [Subcategory] AS [s] WHERE [s].[CategoryType] = 32 ) AS [t0] ON [t].[WorkSubtypeREF] = [t0].[ObjectID] LEFT JOIN ( SELECT [c1].[ObjectID], [c1].[Name] FROM [Category] AS [c1] WHERE [c1].[CategoryType] = 32 ) AS [t1] ON [t].[WorkTypeREF] = [t1].[ObjectID] LEFT JOIN ( SELECT [c2].[ObjectID], [c2].[ApportionmentPercent], [c2].[ApportionmentQuantity], [c2].[CreatedByInitials], [c2].[CreatedByREF], [c2].[CreatedOn], [c2].[EstimateREF], [c2].[IsDeleted], [c2].[ItemTaskComponentREF], [c2].[ModifiedByInitials], [c2].[ModifiedByREF], [c2].[ModifiedOn], [c2].[OwnerREF] FROM [CostGroupMember] AS [c2] WHERE ([c2].[OwnerREF] = @objectId_1) AND ([c2].[IsDeleted] <> CAST(1 AS bit)) ) AS [t2] ON [t].[ObjectID] = [t2].[ItemTaskComponentREF] ORDER BY [t].[Name], [t].[ObjectID], [j].[ObjectID], [j0].[ObjectID], [t0].[ObjectID], [t1].[ObjectID], [t2].[ObjectID]', N'@objectId uniqueidentifier,@__TypedProperty_2 uniqueidentifier,@p_3 int,@p_4 int,@__objectId_1 uniqueidentifier', @objectId='0ED90CF5-2F33-4B30-964C-A2D401842958', @TypedProperty_2='C84A5BE7-85EB-493C-8E47-4F3067323E75', @p_3=0,@__p_4=2,@objectId_1='0ED90CF5-2F33-4B30-964C-A2D401842958'