Try to calculate the number of elements in the group with condition statement in key.


var operationsToCheck = q.All<docinventoryoperation>()
                         .Where(z => z.OperationCreationDate >= DateTime.Parse("14.10.2012")
                                  && z.OperationKind.Id.In(operTypes)
                                  && z.Status.Id == PlatformHardcodedValues.Statuses.SubmitedId
                                  && z.IsExchange == false);
//There are many operations(Enrollments and WriteOffs) in operationsToCheck

var firstCheckOperationGroup = operationsToCheck .GroupBy(z => new { DepoAccount = z.PassiveAccountCredit ?? z.PassiveAccountDebit, FinTool = z.CreditedFinTool ?? z.WrittenOffFinTool, FinToolCount = z.CreditedFinToolCount ?? z.WrittenOffFinToolCount }); // we have 1 group with ~1000 elements in firstCheckOperationGroup. It's all right

var suspiciousOpsGroups = firstCheckOperationGroup.Select(z => new { Count = z.Count()});

Result: "Count" equals 0 but expected ~1000

If I don't use condition statement to build the group, the value of Count is correct.

Model Example:


namespace Documents
{
    using System;
    using Xtensive.Orm;

[HierarchyRoot]
[Serializable]
[Id("90147C70-E589-4F3C-0000-000000000000")]
[Index("OperationNumber", Clustered = true)]
public abstract class DocDepositaryOperation
{
    [Field(Nullable = true, Length = 13)]
    [Id("00000000-0000-0000-8254-138215BDD915")]
    public string OperationNumber { get; set; }

    [Field(Nullable = false)]
    [Id("00000000-0000-0000-A5FD-8BAD8E2DD441")]
    public DateTime OperationCreationDate { get; set; }

    [Field(Nullable = false)]
    [Id("00000000-0000-0000-980C-11B48E253BB6")]
    public OperationKind OperationKind { get; set; }
}

[Id("C2E9F2F1-EA52-4792-0000-000000000000")]
[Serializable]
public partial class DocInventoryOperation : DocDepositaryOperation
{
    [Field(Nullable = true)]
    [Id("00000000-0000-0000-B1CB-12BD9D6A4C2E")]
    public PassiveAccount PassiveAccountDebit { get; set; }

    [Field(Nullable = true)]
    [Id("00000000-0000-0000-A20A-2028149568AA")]
    public PassiveAccount PassiveAccountCredit { get; set; }

    [Field(Nullable = true)]
    [Id("00000000-0000-0000-8117-4DC65DDF94D0")]
    public FinToolBase WrittenOffFinTool { get; set; }

    [Field(Nullable = true, Precision = 28, Scale = 8)]
    [Id("00000000-0000-0000-AF3B-977889FFB680")]
    public decimal? WrittenOffFinToolCount { get; set; }

    [Field(Nullable = true)]
    [Id("00000000-0000-0000-A0CA-ECE4926783B7")]
    public FinToolBase CreditedFinTool { get; set; }

    [Field(Nullable = true, Precision = 28, Scale = 8)]
    [Id("00000000-0000-0000-BD54-0A692164AA77")]
    public decimal? CreditedFinToolCount { get; set; }

    [Field(Nullable = true)]
    [Id("00000000-0000-0000-A06E-B1718DE6F23D")]
    public DateTime? OperationExecutionDate { get; set; }
}

}

SQLBatchCompleted(MSSQL 2012)
Original classes have more extended structure.


SELECT (SELECT COUNT_BIG ( *)
        FROM (SELECT [a] .[Id]
                  , [a] .[TypeId]
                  , [a] .[Owner_Id]
                  , [a] .[OperationNumber]
                  , [a] .[OperationCreationDate]
                  , [a] .[CancelDate]
                  , [a] .[OperationKind_Id]
                  , [a] .[IsServiceOperation]
                  , [a] .[Status_Id]
                  , [a] .[Creator_Id]
                  , [a] .[ForeignId]
                  , [a] .[FarSiteOperationDate]
                  , [a] .[NearSiteOperationDate]
                  , [a] .[PassiveAccountDebit_Id]
                  , [a] .[PassiveSectionDebit_Id]
                  , [a] .[WrittenOffFinTool_Id]
                  , [a] .[WrittenOffFinToolCount]
                  , [a] .[ActiveAccountCredit_Id]
                  , [a] .[ActiveSectionCredit_Id]
                  , [a] .[PassiveAccountCredit_Id]
                  , [a] .[PassiveSectionCredit_Id]
                  , [a] .[CreditedFinTool_Id]
                  , [a] .[CreditedFinToolCount]
                  , [a] .[ActiveAccountDebit_Id]
                  , [a] .[ActiveSectionDebit_Id]
                  , [a] .[OperationExecutionDate]
                  , [a] .[OperationRisk]
                  , [a] .[AggregateOperationRisk]
                  , [a] .[ChangeOfOwner]
                  , [a] .[IsExchange]
                  , [a] .[Suspended]
                  , COALESCE ([a] .[CreditedFinToolCount], [a] .[WrittenOffFinToolCount]) AS [c01umn]
              FROM (SELECT [b] .[Id]
                        , [b] .[TypeId]
                        , [b] .[Owner_Id]
                        , [b] .[OperationNumber]
                        , [b] .[OperationCreationDate]
                        , [b] .[CancelDate]
                        , [b] .[OperationKind_Id]
                        , [b] .[IsServiceOperation]
                        , [b] .[Status_Id]
                        , [b] .[Creator_Id]
                        , [c] .[ForeignId]
                        , [c] .[FarSiteOperationDate]
                        , [c] .[NearSiteOperationDate]
                        , [c] .[PassiveAccountDebit_Id]
                        , [c] .[PassiveSectionDebit_Id]
                        , [c] .[WrittenOffFinTool_Id]
                        , [c] .[WrittenOffFinToolCount]
                        , [c] .[ActiveAccountCredit_Id]
                        , [c] .[ActiveSectionCredit_Id]
                        , [c] .[PassiveAccountCredit_Id]
                        , [c] .[PassiveSectionCredit_Id]
                        , [c] .[CreditedFinTool_Id]
                        , [c] .[CreditedFinToolCount]
                        , [c] .[ActiveAccountDebit_Id]
                        , [c] .[ActiveSectionDebit_Id]
                        , [c] .[OperationExecutionDate]
                        , [c] .[OperationRisk]
                        , [c] .[AggregateOperationRisk]
                        , [c] .[ChangeOfOwner]
                        , [c] .[IsExchange]
                        , [c] .[Suspended]
                    FROM (SELECT [d] .[Id]
                              , [d] .[TypeId]
                              , [d] .[ForeignId]
                              , [d] .[FarSiteOperationDate]
                              , [d] .[NearSiteOperationDate]
                              , [d] .[PassiveAccountDebit_Id]
                              , [d] .[PassiveSectionDebit_Id]
                              , [d] .[WrittenOffFinTool_Id]
                              , [d] .[WrittenOffFinToolCount]
                              , [d] .[ActiveAccountCredit_Id]
                              , [d] .[ActiveSectionCredit_Id]
                              , [d] .[PassiveAccountCredit_Id]
                              , [d] .[PassiveSectionCredit_Id]
                              , [d] .[CreditedFinTool_Id]
                              , [d] .[CreditedFinToolCount]
                              , [d] .[ActiveAccountDebit_Id]
                              , [d] .[ActiveSectionDebit_Id]
                              , [d] .[OperationExecutionDate]
                              , [d] .[OperationRisk]
                              , [d] .[AggregateOperationRisk]
                              , [d] .[ChangeOfOwner]
                              , [d] .[IsExchange]
                              , [d] .[Suspended]
                          FROM [dbo] .[DocInventoryOperation] [d]) [c] INNER
                        JOIN (SELECT [e] .[Id]
                                  , [e] .[TypeId]
                                  , [e] .[Owner_Id]
                                  , [e] .[OperationNumber]
                                  , [e] .[OperationCreationDate]
                                  , [e] .[CancelDate]
                                  , [e] .[OperationKind_Id]
                                  , [e] .[IsServiceOperation]
                                  , [e] .[Status_Id]
                                  , [e] .[Creator_Id]
                              FROM [dbo] .[DocDepositaryOperation] [e]) [b] ON ([b] .[Id] = [c] .[Id])
                    WHERE ([b] .[OperationCreationDate] >= CAST ('2012-10-14T00:00:00.000' AS datetime2))) [a]) [f]
        WHERE ((((([f] .[PassiveAccountDebit_Id] = [g] .[PassiveAccountDebit_Id]) AND ([f] .[WrittenOffFinTool_Id] = [g] .[WrittenOffFinTool_Id])) AND ([f] .[PassiveAccountCredit_Id] = [g] .[PassiveAccountCredit_Id])) AND ([f] .[CreditedFinTool_Id] = [g] .[CreditedFinTool_Id])) AND ([f] .[c01umn] = [g] .[c01umn]))) AS [c01umn1]
FROM (SELECT [h] .[PassiveAccountDebit_Id]
          , [h] .[WrittenOffFinTool_Id]
          , [h] .[PassiveAccountCredit_Id]
          , [h] .[CreditedFinTool_Id]
          , [h] .[c01umn]
          , [i] .[Id] AS [#a.Id]
          , [i] .[TypeId] AS [#a.TypeId]
          , [i] .[AccountOwner_Id] AS [#a.AccountOwner_Id]
          , [i] .[Contract_Id] AS [#a.Contract_Id]
          , [i] .[Version] AS [#a.Version]
          , [i] .[Revision] AS [#a.Revision]
          , [i] .[ForeignId] AS [#a.ForeignId]
          , [i] .[AccountType_Id] AS [#a.AccountType_Id]
          , [i] .[Number] AS [#a.Number]
          , [i] .[Name] AS [#a.Name]
          , [i] .[Comment] AS [#a.Comment]
          , [i] .[OpenDate] AS [#a.OpenDate]
          , [i] .[CloseDate] AS [#a.CloseDate]
          , [i] .[Status_Id] AS [#a.Status_Id]
          , [i] .[Creator_Id] AS [#a.Creator_Id]
          , [j] .[ForStock] AS [#a.ForStock]
          , [j] .[ForBond] AS [#a.ForBond]
          , [j] .[ForPif] AS [#a.ForPif]
          , [j] .[Founder_Id] AS [#a.Founder_Id]
          , [j] .[SuspenseAccount_Id] AS [#a.SuspenseAccount_Id]
          , [j] .[Visa] AS [#a.Visa]
          , [j] .[SettlementAccount_Id] AS [#a.SettlementAccount_Id]
          , [j] .[DepositorTrustee_Id] AS [#a.DepositorTrustee_Id]
          , [j] .[InclusionDate] AS [#a.InclusionDate]
          , [j] .[PaymentMethod_Id] AS [#a.PaymentMethod_Id]
          , [j] .[FormIncomePayment_Id] AS [#a.FormIncomePayment_Id]
          , [j] .[FullDepositorName] AS [#a.FullDepositorName]
          , [j] .[ShortDepositorName] AS [#a.ShortDepositorName]
          , [j] .[Portfolio_Id] AS [#a.Portfolio_Id]
          , [j] .[RequireTrusteeConsent] AS [#a.RequireTrusteeConsent]
          , [j] .[DeliveryCorrespondenceType_Id] AS [#a.DeliveryCorrespondenceType_Id]
          , [j] .[ContactPerson] AS [#a.ContactPerson]
          , [j] .[LastProfileDate] AS [#a.LastProfileDate]
          , [j] .[SpecialDepositaryClient] AS [#a.SpecialDepositaryClient]
          , [j] .[KapitalGroup] AS [#a.KapitalGroup]
          , [j] .[KapitalManagmentCompany] AS [#a.KapitalManagmentCompany]
          , [k] .[Id] AS [#b.Id]
          , [k] .[TypeId] AS [#b.TypeId]
          , [k] .[AccountOwner_Id] AS [#b.AccountOwner_Id]
          , [k] .[Contract_Id] AS [#b.Contract_Id]
          , [k] .[Version] AS [#b.Version]
          , [k] .[Revision] AS [#b.Revision]
          , [k] .[ForeignId] AS [#b.ForeignId]
          , [k] .[AccountType_Id] AS [#b.AccountType_Id]
          , [k] .[Number] AS [#b.Number]
          , [k] .[Name] AS [#b.Name]
          , [k] .[Comment] AS [#b.Comment]
          , [k] .[OpenDate] AS [#b.OpenDate]
          , [k] .[CloseDate] AS [#b.CloseDate]
          , [k] .[Status_Id] AS [#b.Status_Id]
          , [k] .[Creator_Id] AS [#b.Creator_Id]
          , [l] .[ForStock] AS [#b.ForStock]
          , [l] .[ForBond] AS [#b.ForBond]
          , [l] .[ForPif] AS [#b.ForPif]
          , [l] .[Founder_Id] AS [#b.Founder_Id]
          , [l] .[SuspenseAccount_Id] AS [#b.SuspenseAccount_Id]
          , [l] .[Visa] AS [#b.Visa]
          , [l] .[SettlementAccount_Id] AS [#b.SettlementAccount_Id]
          , [l] .[DepositorTrustee_Id] AS [#b.DepositorTrustee_Id]
          , [l] .[InclusionDate] AS [#b.InclusionDate]
          , [l] .[PaymentMethod_Id] AS [#b.PaymentMethod_Id]
          , [l] .[FormIncomePayment_Id] AS [#b.FormIncomePayment_Id]
          , [l] .[FullDepositorName] AS [#b.FullDepositorName]
          , [l] .[ShortDepositorName] AS [#b.ShortDepositorName]
          , [l] .[Portfolio_Id] AS [#b.Portfolio_Id]
          , [l] .[RequireTrusteeConsent] AS [#b.RequireTrusteeConsent]
          , [l] .[DeliveryCorrespondenceType_Id] AS [#b.DeliveryCorrespondenceType_Id]
          , [l] .[ContactPerson] AS [#b.ContactPerson]
          , [l] .[LastProfileDate] AS [#b.LastProfileDate]
          , [l] .[SpecialDepositaryClient] AS [#b.SpecialDepositaryClient]
          , [l] .[KapitalGroup] AS [#b.KapitalGroup]
          , [l] .[KapitalManagmentCompany] AS [#b.KapitalManagmentCompany]
          , [m] .[Id] AS [#c.Id]
          , [m] .[TypeId] AS [#c.TypeId]
          , [m] .[Number] AS [#c.Number]
          , [m] .[Issuer_Id] AS [#c.Issuer_Id]
          , [m] .[ForeignId] AS [#c.ForeignId]
          , [m] .[Status_Id] AS [#c.Status_Id]
          , [m] .[ItemType_Id] AS [#c.ItemType_Id]
          , [m] .[CalculationLimited] AS [#c.CalculationLimited]
          , [m] .[ActiveAccount_Id] AS [#c.ActiveAccount_Id]
          , [m] .[FinToolType_Id] AS [#c.FinToolType_Id]
          , [m] .[Name] AS [#c.Name]
          , [m] .[ReleaseForm_Id] AS [#c.ReleaseForm_Id]
          , [m] .[ForInvestor] AS [#c.ForInvestor]
          , [m] .[AddServiceDateD] AS [#c.AddServiceDateD]
          , [m] .[RemoveServiceDateD] AS [#c.RemoveServiceDateD]
          , [m] .[AddServiceDateSd] AS [#c.AddServiceDateSd]
          , [m] .[RemoveServiceDateSd] AS [#c.RemoveServiceDateSd]
          , [m] .[Code] AS [#c.Code]
          , [m] .[MmvbCode] AS [#c.MmvbCode]
          , [m] .[NrdCode] AS [#c.NrdCode]
          , [m] .[RtsClassicalCode] AS [#c.RtsClassicalCode]
          , [m] .[RtsExchangeCode] AS [#c.RtsExchangeCode]
          , [m] .[DkkCode] AS [#c.DkkCode]
          , [m] .[IsinCode] AS [#c.IsinCode]
          , [m] .[Ticker] AS [#c.Ticker]
          , [m] .[CfiCode] AS [#c.CfiCode]
          , [m] .[FsfrCode] AS [#c.FsfrCode]
          , [m] .[NyseCode] AS [#c.NyseCode]
          , [m] .[LseCode] AS [#c.LseCode]
          , [m] .[Comment] AS [#c.Comment]
          , [m] .[PegasId] AS [#c.PegasId]
          , [m] .[Version] AS [#c.Version]
          , [m] .[Revision] AS [#c.Revision]
          , [n] .[Id] AS [#d.Id]
          , [n] .[TypeId] AS [#d.TypeId]
          , [n] .[Number] AS [#d.Number]
          , [n] .[Issuer_Id] AS [#d.Issuer_Id]
          , [n] .[ForeignId] AS [#d.ForeignId]
          , [n] .[Status_Id] AS [#d.Status_Id]
          , [n] .[ItemType_Id] AS [#d.ItemType_Id]
          , [n] .[CalculationLimited] AS [#d.CalculationLimited]
          , [n] .[ActiveAccount_Id] AS [#d.ActiveAccount_Id]
          , [n] .[FinToolType_Id] AS [#d.FinToolType_Id]
          , [n] .[Name] AS [#d.Name]
          , [n] .[ReleaseForm_Id] AS [#d.ReleaseForm_Id]
          , [n] .[ForInvestor] AS [#d.ForInvestor]
          , [n] .[AddServiceDateD] AS [#d.AddServiceDateD]
          , [n] .[RemoveServiceDateD] AS [#d.RemoveServiceDateD]
          , [n] .[AddServiceDateSd] AS [#d.AddServiceDateSd]
          , [n] .[RemoveServiceDateSd] AS [#d.RemoveServiceDateSd]
          , [n] .[Code] AS [#d.Code]
          , [n] .[MmvbCode] AS [#d.MmvbCode]
          , [n] .[NrdCode] AS [#d.NrdCode]
          , [n] .[RtsClassicalCode] AS [#d.RtsClassicalCode]
          , [n] .[RtsExchangeCode] AS [#d.RtsExchangeCode]
          , [n] .[DkkCode] AS [#d.DkkCode]
          , [n] .[IsinCode] AS [#d.IsinCode]
          , [n] .[Ticker] AS [#d.Ticker]
          , [n] .[CfiCode] AS [#d.CfiCode]
          , [n] .[FsfrCode] AS [#d.FsfrCode]
          , [n] .[NyseCode] AS [#d.NyseCode]
          , [n] .[LseCode] AS [#d.LseCode]
          , [n] .[Comment] AS [#d.Comment]
          , [n] .[PegasId] AS [#d.PegasId]
          , [n] .[Version] AS [#d.Version]
          , [n] .[Revision] AS [#d.Revision]
      FROM (SELECT [o] .[PassiveAccountDebit_Id]
                , [o] .[WrittenOffFinTool_Id]
                , [o] .[PassiveAccountCredit_Id]
                , [o] .[CreditedFinTool_Id]
                , [o] .[c01umn]
            FROM (SELECT [p] .[Id]
                      , [p] .[TypeId]
                      , [p] .[Owner_Id]
                      , [p] .[OperationNumber]
                      , [p] .[OperationCreationDate]
                      , [p] .[CancelDate]
                      , [p] .[OperationKind_Id]
                      , [p] .[IsServiceOperation]
                      , [p] .[Status_Id]
                      , [p] .[Creator_Id]
                      , [p] .[ForeignId]
                      , [p] .[FarSiteOperationDate]
                      , [p] .[NearSiteOperationDate]
                      , [p] .[PassiveAccountDebit_Id]
                      , [p] .[PassiveSectionDebit_Id]
                      , [p] .[WrittenOffFinTool_Id]
                      , [p] .[WrittenOffFinToolCount]
                      , [p] .[ActiveAccountCredit_Id]
                      , [p] .[ActiveSectionCredit_Id]
                      , [p] .[PassiveAccountCredit_Id]
                      , [p] .[PassiveSectionCredit_Id]
                      , [p] .[CreditedFinTool_Id]
                      , [p] .[CreditedFinToolCount]
                      , [p] .[ActiveAccountDebit_Id]
                      , [p] .[ActiveSectionDebit_Id]
                      , [p] .[OperationExecutionDate]
                      , [p] .[OperationRisk]
                      , [p] .[AggregateOperationRisk]
                      , [p] .[ChangeOfOwner]
                      , [p] .[IsExchange]
                      , [p] .[Suspended]
                      , COALESCE ([p] .[CreditedFinToolCount], [p] .[WrittenOffFinToolCount]) AS [c01umn]
                  FROM (SELECT [q] .[Id]
                            , [q] .[TypeId]
                            , [q] .[Owner_Id]
                            , [q] .[OperationNumber]
                            , [q] .[OperationCreationDate]
                            , [q] .[CancelDate]
                            , [q] .[OperationKind_Id]
                            , [q] .[IsServiceOperation]
                            , [q] .[Status_Id]
                            , [q] .[Creator_Id]
                            , [r] .[ForeignId]
                            , [r] .[FarSiteOperationDate]
                            , [r] .[NearSiteOperationDate]
                            , [r] .[PassiveAccountDebit_Id]
                            , [r] .[PassiveSectionDebit_Id]
                            , [r] .[WrittenOffFinTool_Id]
                            , [r] .[WrittenOffFinToolCount]
                            , [r] .[ActiveAccountCredit_Id]
                            , [r] .[ActiveSectionCredit_Id]
                            , [r] .[PassiveAccountCredit_Id]
                            , [r] .[PassiveSectionCredit_Id]
                            , [r] .[CreditedFinTool_Id]
                            , [r] .[CreditedFinToolCount]
                            , [r] .[ActiveAccountDebit_Id]
                            , [r] .[ActiveSectionDebit_Id]
                            , [r] .[OperationExecutionDate]
                            , [r] .[OperationRisk]
                            , [r] .[AggregateOperationRisk]
                            , [r] .[ChangeOfOwner]
                            , [r] .[IsExchange]
                            , [r] .[Suspended]
                        FROM (SELECT [s] .[Id]
                                  , [s] .[TypeId]
                                  , [s] .[ForeignId]
                                  , [s] .[FarSiteOperationDate]
                                  , [s] .[NearSiteOperationDate]
                                  , [s] .[PassiveAccountDebit_Id]
                                  , [s] .[PassiveSectionDebit_Id]
                                  , [s] .[WrittenOffFinTool_Id]
                                  , [s] .[WrittenOffFinToolCount]
                                  , [s] .[ActiveAccountCredit_Id]
                                  , [s] .[ActiveSectionCredit_Id]
                                  , [s] .[PassiveAccountCredit_Id]
                                  , [s] .[PassiveSectionCredit_Id]
                                  , [s] .[CreditedFinTool_Id]
                                  , [s] .[CreditedFinToolCount]
                                  , [s] .[ActiveAccountDebit_Id]
                                  , [s] .[ActiveSectionDebit_Id]
                                  , [s] .[OperationExecutionDate]
                                  , [s] .[OperationRisk]
                                  , [s] .[AggregateOperationRisk]
                                  , [s] .[ChangeOfOwner]
                                  , [s] .[IsExchange]
                                  , [s] .[Suspended]
                              FROM [dbo] .[DocInventoryOperation] [s]) [r] INNER
                            JOIN (SELECT [t] .[Id]
                                      , [t] .[TypeId]
                                      , [t] .[Owner_Id]
                                      , [t] .[OperationNumber]
                                      , [t] .[OperationCreationDate]
                                      , [t] .[CancelDate]
                                      , [t] .[OperationKind_Id]
                                      , [t] .[IsServiceOperation]
                                      , [t] .[Status_Id]
                                      , [t] .[Creator_Id]
                                  FROM [dbo] .[DocDepositaryOperation] [t]) [q] ON ([q] .[Id] = [r] .[Id])
                        WHERE ([q] .[OperationCreationDate] >= CAST ('2012-10-14T00:00:00.000' AS datetime2))) [p]) [o]
            GROUP BY [o] .[PassiveAccountDebit_Id], [o] .[WrittenOffFinTool_Id], [o] .[PassiveAccountCredit_Id], [o] .[CreditedFinTool_Id], [o] .[c01umn]) [h]
          LEFT OUTER JOIN (SELECT [u] .[Id]
                               , [u] .[TypeId]
                               , [u] .[ForStock]
                               , [u] .[ForBond]
                               , [u] .[ForPif]
                               , [u] .[Founder_Id]
                               , [u] .[SuspenseAccount_Id]
                               , [u] .[Visa]
                               , [u] .[SettlementAccount_Id]
                               , [u] .[DepositorTrustee_Id]
                               , [u] .[InclusionDate]
                               , [u] .[PaymentMethod_Id]
                               , [u] .[FormIncomePayment_Id]
                               , [u] .[FullDepositorName]
                               , [u] .[ShortDepositorName]
                               , [u] .[Portfolio_Id]
                               , [u] .[RequireTrusteeConsent]
                               , [u] .[DeliveryCorrespondenceType_Id]
                               , [u] .[ContactPerson]
                               , [u] .[LastProfileDate]
                               , [u] .[SpecialDepositaryClient]
                               , [u] .[KapitalGroup]
                               , [u] .[KapitalManagmentCompany]
                           FROM [dbo] .[PassiveAccount] [u]) [j] INNER
          JOIN (SELECT [v] .[Id]
                    , [v] .[TypeId]
                    , [v] .[AccountOwner_Id]
                    , [v] .[Contract_Id]
                    , [v] .[Version]
                    , [v] .[Revision]
                    , [v] .[ForeignId]
                    , [v] .[AccountType_Id]
                    , [v] .[Number]
                    , [v] .[Name]
                    , [v] .[Comment]
                    , [v] .[OpenDate]
                    , [v] .[CloseDate]
                    , [v] .[Status_Id]
                    , [v] .[Creator_Id]
                FROM [dbo] .[DepoAccountBase] [v]) [i] ON ([i] .[Id] = [j] .[Id]) ON ([h] .[PassiveAccountCredit_Id] = [i] .[Id])
          LEFT OUTER JOIN (SELECT [w] .[Id]
                               , [w] .[TypeId]
                               , [w] .[ForStock]
                               , [w] .[ForBond]
                               , [w] .[ForPif]
                               , [w] .[Founder_Id]
                               , [w] .[SuspenseAccount_Id]
                               , [w] .[Visa]
                               , [w] .[SettlementAccount_Id]
                               , [w] .[DepositorTrustee_Id]
                               , [w] .[InclusionDate]
                               , [w] .[PaymentMethod_Id]
                               , [w] .[FormIncomePayment_Id]
                               , [w] .[FullDepositorName]
                               , [w] .[ShortDepositorName]
                               , [w] .[Portfolio_Id]
                               , [w] .[RequireTrusteeConsent]
                               , [w] .[DeliveryCorrespondenceType_Id]
                               , [w] .[ContactPerson]
                               , [w] .[LastProfileDate]
                               , [w] .[SpecialDepositaryClient]
                               , [w] .[KapitalGroup]
                               , [w] .[KapitalManagmentCompany]
                           FROM [dbo] .[PassiveAccount] [w]) [l] INNER
          JOIN (SELECT [x] .[Id]
                    , [x] .[TypeId]
                    , [x] .[AccountOwner_Id]
                    , [x] .[Contract_Id]
                    , [x] .[Version]
                    , [x] .[Revision]
                    , [x] .[ForeignId]
                    , [x] .[AccountType_Id]
                    , [x] .[Number]
                    , [x] .[Name]
                    , [x] .[Comment]
                    , [x] .[OpenDate]
                    , [x] .[CloseDate]
                    , [x] .[Status_Id]
                    , [x] .[Creator_Id]
                FROM [dbo] .[DepoAccountBase] [x]) [k] ON ([k] .[Id] = [l] .[Id]) ON ([h] .[PassiveAccountDebit_Id] = [k] .[Id])
          LEFT OUTER JOIN [dbo] .[FinToolBase] [m] ON ([h] .[CreditedFinTool_Id] = [m] .[Id])
          LEFT OUTER JOIN [dbo] .[FinToolBase] [n] ON ([h] .[WrittenOffFinTool_Id] = [n] .[Id])) [g];

asked Nov 19 '12 at 05:38

abelkin's gravatar image

abelkin
25337

edited Nov 19 '12 at 06:49

Could you add SQL produced by this query?

(Nov 19 '12 at 06:10) Denis Krjuchkov Denis%20Krjuchkov's gravatar image

Sure. Added to the end of the question.

(Nov 19 '12 at 06:47) abelkin abelkin's gravatar image

2 Answers:

Hello abelkin,

I can confirm the issue.

You're grouping by the result of coalesce operator over entities. It's not clear how such query is supposed to be treated by LINQ translator.

To make things simple use grouping over key fields explicitly:

.GroupBy(z => new {
   DepoAccountId = z.PassiveAccountCredit!=null ? z.PassiveAccountCredit.Id : z.PassiveAccountDebit.Id,
   FinToolId = z.CreditedFinTool!=null ? z.CreditedFinTool.Id : z.WrittenOffFinTool.Id,
   FinToolCount = z.CreditedFinToolCount ?? z.WrittenOffFinToolCount
 })

answered Nov 22 '12 at 04:55

Denis%20Krjuchkov's gravatar image

Denis Krjuchkov
179325

Thanks, it began to work!

(Nov 27 '12 at 02:13) abelkin abelkin's gravatar image

5.0.18 Same behaviour

Since group by for coalesced entities translated wrong, DO should throw exception in that case and therefore demand explicite key in GroupBy expression

it.Link == null ? it.Link2 : it.Link 'Similar' expression already throw Unable to translate exception though message not comprehendible - At least one object must implement IComparable

(Oct 24 '19 at 02:43) Gushchin Anton Gushchin%20Anton's gravatar image

Just in case. Native LINQ without DO assembly works correctly.


void Main()
{
    var submited = Guid.Parse("dae12bf1-6329-4dd5-b10c-1f306ca01c4a");
    var ops = DocInventoryOperations
         .Where(z => z.DocDepositaryOperation.OperationCreationDate >= DateTime.Parse("14.10.2012")
                                                  && z.CreditedFinToolCount == 13 
&& (z.DocDepositaryOperation.OperationKind_Id == Guid.Parse("94902637-3945-4972-a19e-eaed4372851f") || z.DocDepositaryOperation.OperationKind_Id == Guid.Parse("c3710c3c-2a86-45f1-8234-955a73cead63")) && z.DocDepositaryOperation.Status_Id == submited && z.IsExchange == false);

var firstCheckOperationGroup = ops.GroupBy(z => new
                                                         {
                                                             DepoAccount = z.PassiveAccountCredit_Id ?? z.PassiveAccountDebit_Id,
                                                             FinTool = z.CreditedFinTool_Id ?? z.WrittenOffFinTool_Id,
                                                             Count = z.CreditedFinToolCount ?? z.WrittenOffFinToolCount
                                                         });

var suspiciousOpsGroups = firstCheckOperationGroup.Select(z => new { C = z.Count()});
suspiciousOpsGroups.Dump();

}

Produced SQL:

    exec sp_executesql N'SELECT COUNT(*) AS [C]
    FROM (
        SELECT COALESCE([t0].[PassiveAccountCredit_Id],[t0].[PassiveAccountDebit_Id]) AS [value], COALESCE([t0].[CreditedFinTool_Id],[t0].[WrittenOffFinTool_Id]) AS [value2], COALESCE([t0].[CreditedFinToolCount],[t0].[WrittenOffFinToolCount]) AS [value3], [t1].[OperationCreationDate], [t0].[CreditedFinToolCount], [t1].[OperationKind_Id], [t1].[Status_Id], [t0].[IsExchange]
        FROM [DocInventoryOperation] AS [t0]
        INNER JOIN [DocDepositaryOperation] AS [t1] ON [t1].[Id] = [t0].[Id]
        ) AS [t2]
    WHERE ([t2].[OperationCreationDate] >= @p0) AND ([t2].[CreditedFinToolCount] = @p1) AND (([t2].[OperationKind_Id] = @p2) OR ([t2].[OperationKind_Id] = @p3)) AND ([t2].[Status_Id] = @p4) AND (NOT ([t2].[IsExchange] = 1))
    GROUP BY [t2].[value], [t2].[value2], [t2].[value3]',N'@p0 datetime2(7),@p1 decimal(37,8),@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier',@p0='2012-10-14 00:00:00',@p1=13.00000000,@p2='94902637-3945-4972-A19E-EAED4372851F',@p3='C3710C3C-2A86-45F1-8234-955A73CEAD63',@p4='DAE12BF1-6329-4DD5-B10C-1F306CA01C4A'

answered Nov 19 '12 at 07:03

abelkin's gravatar image

abelkin
25337

edited Nov 19 '12 at 07:04

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

powered by OSQA