Consider these queries:
var days = (from t in Query.All<RegTransaction>()
where t.AuthDate < t.InputDate && !t.FinTool.FinToolType.Id.In(exlude)
select new { t.FinTool, t.Fund, t.AuthDate, Date = t.InputDate }).Distinct().OrderBy(t => t.Date).ThenBy(t => t.AuthDate);
var days = (from t in Query.All<RegTransaction>()
where t.AuthDate < t.InputDate && !t.FinTool.FinToolType.Id.In(exlude)
orderby t.InputDate, t.AuthDAte
select new { t.FinTool, t.Fund, t.AuthDate, Date = t.InputDate }).Distinct();
Aren't they the same? Nope:)
DECLARE @p0_0_0_0 UNIQUEIDENTIFIER ,
@p0_0_1_0 UNIQUEIDENTIFIER ,
@p0_0_2_0 UNIQUEIDENTIFIER ,
@p0_0_3_0 UNIQUEIDENTIFIER
SELECT @p0_0_0_0 = '040C1ADF-A6BE-4B37-8644-6C104D8634B2' ,
@p0_0_1_0 = 'A894574B-5D73-4E88-AF88-FCA99671ACD4' ,
@p0_0_2_0 = '8BFAA101-338D-40F1-9471-F28BA00D2C5D' ,
@p0_0_3_0 = '8013BC23-2546-4C1B-865E-979460E34E96'
SELECT DISTINCT
[a].[AuthDate] ,
[a].[InputDate] ,
[b].[#c.Id] ,
[b].[#c.TypeId] ,
[b].[#c.Currency_Id] ,
[b].[#c.Emitter_Id] ,
[b].[#c.FinToolGroup_Id] ,
[b].[#c.FinToolType_Id] ,
[b].[#c.FullName] ,
[b].[#c.IntId] ,
[b].[#c.IsMultiMarket] ,
[b].[#c.Name] ,
[b].[#c.Version] ,
[c].[#d.Id] ,
[c].[#d.TypeId] ,
[c].[#d.BeginDate] ,
[c].[#d.Category_Id] ,
[c].[#d.EndDate] ,
[c].[#d.Hierarchy_Left] ,
[c].[#d.Hierarchy_Right] ,
[c].[#d.Hierarchy_Level] ,
[c].[#d.IntId] ,
[c].[#d.IsJournaled] ,
[c].[#d.IsSaleAllowed] ,
[c].[#d.ManagementCompany_Id] ,
[c].[#d.Parent_Id] ,
[c].[#d.PduDate] ,
[c].[#d.PduRegNumber] ,
[c].[#d.Person_Id] ,
[c].[#d.Type_Id] ,
[c].[#d.Version]
FROM ( SELECT [d].[Id] ,
197 AS [TypeId] ,
[d].[AccountingType] ,
[d].[AuthDate] ,
[d].[Contractor_Id] ,
[d].[Currency_Id] ,
[d].[CurrencyRate] ,
[d].[InputDate] ,
[d].[DayOrder] ,
[d].[FinTool_Id] ,
[d].[Fund_Id] ,
[d].[IsAccounted] ,
[d].[IsManual] ,
[d].[IsNovation] ,
[d].[IsPlanned] ,
[d].[MarketPlace_Id] ,
[d].[OperationType_Id] ,
[d].[PercentDate] ,
[d].[PlanCloseDate] ,
[d].[Price] ,
[d].[Quantity] ,
[d].[Sum] ,
[d].[BeginSum] ,
[d].[BeginQty] ,
[d].[AveragePrice] ,
[d].[Owner_Id]
FROM [dbo].[RegTransaction] [d]
) [a]
INNER JOIN ( SELECT [e].[Id] AS [#a.Id] ,
153 AS [#a.TypeId] ,
[e].[Currency_Id] AS [#a.Currency_Id] ,
[e].[Emitter_Id] AS [#a.Emitter_Id] ,
[e].[FinToolGroup_Id] AS [#a.FinToolGroup_Id] ,
[e].[FinToolType_Id] AS [#a.FinToolType_Id] ,
[e].[FullName] AS [#a.FullName] ,
[e].[IntId] AS [#a.IntId] ,
[e].[IsMultiMarket] AS [#a.IsMultiMarket] ,
[e].[Name] AS [#a.Name] ,
[e].[Version] AS [#a.Version]
FROM [dbo].[FinTool] [e]
) [f] ON ( [a].[FinTool_Id] = [f].[#a.Id] )
INNER JOIN ( SELECT [g].[Id] AS [#c.Id] ,
153 AS [#c.TypeId] ,
[g].[Currency_Id] AS [#c.Currency_Id] ,
[g].[Emitter_Id] AS [#c.Emitter_Id] ,
[g].[FinToolGroup_Id] AS [#c.FinToolGroup_Id] ,
[g].[FinToolType_Id] AS [#c.FinToolType_Id] ,
[g].[FullName] AS [#c.FullName] ,
[g].[IntId] AS [#c.IntId] ,
[g].[IsMultiMarket] AS [#c.IsMultiMarket] ,
[g].[Name] AS [#c.Name] ,
[g].[Version] AS [#c.Version]
FROM [dbo].[FinTool] [g]
) [b] ON ( [a].[FinTool_Id] = [b].[#c.Id] )
INNER JOIN ( SELECT [h].[Id] AS [#d.Id] ,
158 AS [#d.TypeId] ,
[h].[BeginDate] AS [#d.BeginDate] ,
[h].[Category_Id] AS [#d.Category_Id] ,
[h].[EndDate] AS [#d.EndDate] ,
[h].[Hierarchy_Left] AS [#d.Hierarchy_Left] ,
[h].[Hierarchy_Right] AS [#d.Hierarchy_Right] ,
[h].[Hierarchy_Level] AS [#d.Hierarchy_Level] ,
[h].[IntId] AS [#d.IntId] ,
[h].[IsJournaled] AS [#d.IsJournaled] ,
[h].[IsSaleAllowed] AS [#d.IsSaleAllowed] ,
[h].[ManagementCompany_Id] AS [#d.ManagementCompany_Id] ,
[h].[Parent_Id] AS [#d.Parent_Id] ,
[h].[PduDate] AS [#d.PduDate] ,
[h].[PduRegNumber] AS [#d.PduRegNumber] ,
[h].[Person_Id] AS [#d.Person_Id] ,
[h].[Type_Id] AS [#d.Type_Id] ,
[h].[Version] AS [#d.Version]
FROM [dbo].[Fund] [h]
) [c] ON ( [a].[Fund_Id] = [c].[#d.Id] )
WHERE ( ( [a].[AuthDate] < [a].[InputDate] )
AND ( NOT [f].[#a.FinToolType_Id] IN ( @p0_0_0_0, @p0_0_1_0, @p0_0_2_0, @p0_0_3_0 )
)
)
ORDER BY [a].[InputDate] ASC ,
[a].[AuthDate] ASC ;
The second:
DECLARE @p0_0_0_0 UNIQUEIDENTIFIER ,
@p0_0_1_0 UNIQUEIDENTIFIER ,
@p0_0_2_0 UNIQUEIDENTIFIER ,
@p0_0_3_0 UNIQUEIDENTIFIER
SELECT @p0_0_0_0 = '040C1ADF-A6BE-4B37-8644-6C104D8634B2' ,
@p0_0_1_0 = 'A894574B-5D73-4E88-AF88-FCA99671ACD4' ,
@p0_0_2_0 = '8BFAA101-338D-40F1-9471-F28BA00D2C5D' ,
@p0_0_3_0 = '8013BC23-2546-4C1B-865E-979460E34E96'
SELECT DISTINCT
[a].[AuthDate] ,
[a].[InputDate] ,
[b].[#c.Id] ,
[b].[#c.TypeId] ,
[b].[#c.Currency_Id] ,
[b].[#c.Emitter_Id] ,
[b].[#c.FinToolGroup_Id] ,
[b].[#c.FinToolType_Id] ,
[b].[#c.FullName] ,
[b].[#c.IntId] ,
[b].[#c.IsMultiMarket] ,
[b].[#c.Name] ,
[b].[#c.Version] ,
[c].[#d.Id] ,
[c].[#d.TypeId] ,
[c].[#d.BeginDate] ,
[c].[#d.Category_Id] ,
[c].[#d.EndDate] ,
[c].[#d.Hierarchy_Left] ,
[c].[#d.Hierarchy_Right] ,
[c].[#d.Hierarchy_Level] ,
[c].[#d.IntId] ,
[c].[#d.IsJournaled] ,
[c].[#d.IsSaleAllowed] ,
[c].[#d.ManagementCompany_Id] ,
[c].[#d.Parent_Id] ,
[c].[#d.PduDate] ,
[c].[#d.PduRegNumber] ,
[c].[#d.Person_Id] ,
[c].[#d.Type_Id] ,
[c].[#d.Version]
FROM ( SELECT [d].[Id] ,
197 AS [TypeId] ,
[d].[AccountingType] ,
[d].[AuthDate] ,
[d].[Contractor_Id] ,
[d].[Currency_Id] ,
[d].[CurrencyRate] ,
[d].[InputDate] ,
[d].[DayOrder] ,
[d].[FinTool_Id] ,
[d].[Fund_Id] ,
[d].[IsAccounted] ,
[d].[IsManual] ,
[d].[IsNovation] ,
[d].[IsPlanned] ,
[d].[MarketPlace_Id] ,
[d].[OperationType_Id] ,
[d].[PercentDate] ,
[d].[PlanCloseDate] ,
[d].[Price] ,
[d].[Quantity] ,
[d].[Sum] ,
[d].[BeginSum] ,
[d].[BeginQty] ,
[d].[AveragePrice] ,
[d].[Owner_Id]
FROM [dbo].[RegTransaction] [d]
) [a]
INNER JOIN ( SELECT [e].[Id] AS [#a.Id] ,
153 AS [#a.TypeId] ,
[e].[Currency_Id] AS [#a.Currency_Id] ,
[e].[Emitter_Id] AS [#a.Emitter_Id] ,
[e].[FinToolGroup_Id] AS [#a.FinToolGroup_Id] ,
[e].[FinToolType_Id] AS [#a.FinToolType_Id] ,
[e].[FullName] AS [#a.FullName] ,
[e].[IntId] AS [#a.IntId] ,
[e].[IsMultiMarket] AS [#a.IsMultiMarket] ,
[e].[Name] AS [#a.Name] ,
[e].[Version] AS [#a.Version]
FROM [dbo].[FinTool] [e]
) [f] ON ( [a].[FinTool_Id] = [f].[#a.Id] )
INNER JOIN ( SELECT [g].[Id] AS [#c.Id] ,
153 AS [#c.TypeId] ,
[g].[Currency_Id] AS [#c.Currency_Id] ,
[g].[Emitter_Id] AS [#c.Emitter_Id] ,
[g].[FinToolGroup_Id] AS [#c.FinToolGroup_Id] ,
[g].[FinToolType_Id] AS [#c.FinToolType_Id] ,
[g].[FullName] AS [#c.FullName] ,
[g].[IntId] AS [#c.IntId] ,
[g].[IsMultiMarket] AS [#c.IsMultiMarket] ,
[g].[Name] AS [#c.Name] ,
[g].[Version] AS [#c.Version]
FROM [dbo].[FinTool] [g]
) [b] ON ( [a].[FinTool_Id] = [b].[#c.Id] )
INNER JOIN ( SELECT [h].[Id] AS [#d.Id] ,
158 AS [#d.TypeId] ,
[h].[BeginDate] AS [#d.BeginDate] ,
[h].[Category_Id] AS [#d.Category_Id] ,
[h].[EndDate] AS [#d.EndDate] ,
[h].[Hierarchy_Left] AS [#d.Hierarchy_Left] ,
[h].[Hierarchy_Right] AS [#d.Hierarchy_Right] ,
[h].[Hierarchy_Level] AS [#d.Hierarchy_Level] ,
[h].[IntId] AS [#d.IntId] ,
[h].[IsJournaled] AS [#d.IsJournaled] ,
[h].[IsSaleAllowed] AS [#d.IsSaleAllowed] ,
[h].[ManagementCompany_Id] AS [#d.ManagementCompany_Id] ,
[h].[Parent_Id] AS [#d.Parent_Id] ,
[h].[PduDate] AS [#d.PduDate] ,
[h].[PduRegNumber] AS [#d.PduRegNumber] ,
[h].[Person_Id] AS [#d.Person_Id] ,
[h].[Type_Id] AS [#d.Type_Id] ,
[h].[Version] AS [#d.Version]
FROM [dbo].[Fund] [h]
) [c] ON ( [a].[Fund_Id] = [c].[#d.Id] )
WHERE ( ( [a].[AuthDate] < [a].[InputDate] )
AND ( NOT [f].[#a.FinToolType_Id] IN ( @p0_0_0_0, @p0_0_1_0, @p0_0_2_0, @p0_0_3_0 )
)
)
;
PS Блин, это баг стоил нам целого дня дебага...
You are trying to select distinct rows of type
and outermost SELECT is like following:
so I can't understand what is wrong here.
Sure, now I see, I thought I've selected the ID's :) But anyway, the bug with lost ORDER BY still exists
The bug with lost ORDER BY is accepted