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 Блин, это баг стоил нам целого дня дебага...

asked Sep 02 '10 at 04:16

xumix's gravatar image

xumix
425717682

edited Sep 02 '10 at 10:37

Alex%20Yakunin's gravatar image

Alex Yakunin
29714412

You are trying to select distinct rows of type

new { t.FinTool, t.Fund, t.AuthDate, Date = t.InputDate }

and outermost SELECT is like following:

SELECT DISTINCT
    [a].[AuthDate] , // <-- AuthDate field
    [a].[InputDate] , // <-- InputDate field
    [b].[#c.Id] , // <-- FinTool entity with all fields
    [b].....
    [c].[#d.Id] , // <-- Fund entity
    [c].....
FROM ...

so I can't understand what is wrong here.

(Sep 02 '10 at 06:41) Alexis Kochetov Alexis%20Kochetov's gravatar image

Sure, now I see, I thought I've selected the ID's :) But anyway, the bug with lost ORDER BY still exists

(Sep 02 '10 at 06:55) xumix xumix's gravatar image

The bug with lost ORDER BY is accepted

(Sep 02 '10 at 07:15) Dmitri Maximov Dmitri%20Maximov's gravatar image

One Answer:

The bug is accepted. We'll notify you here when it's fixed (or star it there).

answered Sep 02 '10 at 10:27

Alex%20Yakunin's gravatar image

Alex Yakunin
29714412

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×569
×120
×56
×23

Asked: Sep 02 '10 at 04:16

Seen: 3,106 times

Last updated: Sep 02 '10 at 10:37

powered by OSQA