Question

Example query:

            using (var s = domain.OpenSession())
            using (s.Activate())
            using (var t = s.OpenTransaction())
            {
                var acc = new Account(Guid.NewGuid());
                var ftc = new FinToolKind(Guid.NewGuid());
                var r1 = new TpPriceCalc(Guid.NewGuid()) { Account = acc, Owner = null, Date = new DateTime(2012, 10, 10), Qty = 1 };
                var r1a = new TpPriceCalc(Guid.NewGuid()) { Account = acc, Owner = null, Date = new DateTime(2012, 10, 10), Qty = 1 };
                var r2 = new TpPriceCalc(Guid.NewGuid()) { Account = null, Owner = ftc, Date = new DateTime(2014, 10, 10), Qty = 1 };
                var r2a = new TpPriceCalc(Guid.NewGuid()) { Account = null, Owner = ftc, Date = new DateTime(2014, 10, 10), Qty = 1 };
                var r3 = new TpPriceCalc(Guid.NewGuid()) { Account = acc, Owner = ftc, Date = new DateTime(2015, 10, 10), Qty = 1 };
                var r3a = new TpPriceCalc(Guid.NewGuid()) { Account = acc, Owner = ftc, Date = new DateTime(2015, 10, 10), Qty = 1 };
                var r4 = new TpPriceCalc(Guid.NewGuid()) { Account = null, Owner = null, Date = new DateTime(2011, 10, 10), Qty = 1 };
                var r4a = new TpPriceCalc(Guid.NewGuid()) { Account = null, Owner = null, Date = new DateTime(2011, 10, 10), Qty = 1 };
                t.Complete();
            }

            using (var s = domain.OpenSession())
            using (s.Activate())
            using (var t = s.OpenTransaction())
            {
                var result = from r in Query.All<TpPriceCalc>()
                             group r by new {r.Account, r.Owner,r.Check} into gr
                             select new 
                             {
                                 gr.Key.Account,
                                 gr.Key.Owner,
                                 gr.Key.Check,
                                 Sum = gr.Sum(a=>a.Qty),
                                 MaxDate = gr.Max(a=>a.Date),
                                 Cnt = gr.Count()
                             };

                var rr = result.ToArray();
            }

In the result there are only one item with Cnt equals two, and all others are equals to 0 and all MaxDate and Sum also default(T) values.

Model for this query:

public abstract class EntityBase : Entity
{
    protected EntityBase(Guid id) : base(id) { }
    [Field, Key] public Guid Id { get; private set; }
}

[HierarchyRoot]
public class TpPriceCalc : EntityBase
{
    public TpPriceCalc(Guid id) : base(id) { }
    [Field] public bool Check { get; set; }
    [Field] public Account Account { get; set; }
    [Field] public FinToolKind Owner { get; set; }
    [Field] public DateTime Date { get; set; }
    [Field] public decimal Qty { get; set; }
}

[HierarchyRoot]
public class Account : EntityBase
{
    public Account(Guid id) : base(id) { }
}

[HierarchyRoot]
public class FinToolKind : EntityBase
{
    public FinToolKind(Guid id) : base(id) {}
}

Answer 1

Hello pil0t

When you use reference fields as a grouping fields then DataObjects.Net select all info to materialize Keys of types Account and Owner to get them later. First query translates to


SELECT  [a].[Check], 
        [b].[Id] AS [#a.Id], 
        100 AS [#a.TypeId], 
        [c].[Id] AS [#b.Id], 
        101 AS [#b.TypeId], 
        (
          SELECT SUM([d].[Qty]) 
          FROM [dbo].[TpPriceCalc] [d] 
          WHERE (
                  ([d].[Check] = [a].[Check]) AND 
                  ([d].[Account.Id] = [a].[Account.Id]) AND 
                  ([d].[Owner.Id] = [a].[Owner.Id])
                )
        ) AS [c01umn], 
        (
          SELECT  MAX([e].[Date]) 
          FROM [dbo].[TpPriceCalc] [e] 
          WHERE (
                  ([e].[Check] = [a].[Check]) AND 
                  ([e].[Account.Id] = [a].[Account.Id]) AND 
                  ([e].[Owner.Id] = [a].[Owner.Id])
                )
        ) AS [c01umn1], 
        (
          SELECT COUNT_BIG(*) 
          FROM [dbo].[TpPriceCalc] [f] 
          WHERE (
                  ([f].[Check] = [a].[Check]) AND 
                  ([f].[Account.Id] = [a].[Account.Id]) AND 
                  ([f].[Owner.Id] = [a].[Owner.Id])
                )
        ) AS [c01umn2] 
FROM  (
        SELECT  [g].[Check] AS [Check], 
                [g].[Account.Id] AS [Account.Id], 
                [g].[Owner.Id] AS [Owner.Id] 
        FROM [dbo].[TpPriceCalc] [g] 
        GROUP BY [g].[Check], [g].[Account.Id], [g].[Owner.Id]
      ) [a] 
LEFT OUTER JOIN [dbo].[Account] [b] ON ([a].[Account.Id] = [b].[Id]) 
LEFT OUTER JOIN [dbo].[FinToolKind] [c] ON ([a].[Owner.Id] = [c].[Id]);

The problem here is that in results of group by may be null values and conditions like


  ([d].[Check] = [a].[Check]) AND 
  ([d].[Account.Id] = [a].[Account.Id]) AND 
  ([d].[Owner.Id] = [a].[Owner.Id])

ignore null values of [Account.Id] and [Owner.Id] in MS Sql Server because of NULL equals NULL and NULL not-equals NULL aren't true.

You can rewrite you query like


var groupingQuery = from r in Query.All<tppricecalc>()
                    group r by new {
                                     Account = r.Account.Id, 
                                     Owner = r.Owner.Id, 
                                     // or
                                     // Account = r.Account.Key
                                     // Owner = r.Account.Key
                                     r.Check 
                                   } 
                    into gr
                    select new {
                       gr.Key.Account,
                       gr.Key.Owner,
                       gr.Key.Check,
                       Sum = gr.Sum(a => a.Qty),
                       MaxDate = gr.Max(a => a.Date),
                       Cnt = gr.Count()
                     };

and it will be translated to


SELECT  [a].[Check] AS [Check], 
        [a].[Account.Id] AS [Account.Id], 
        [a].[Owner.Id] AS [Owner.Id], 
        SUM([a].[Qty]) AS [c01umn], 
        MAX([a].[Date]) AS [c01umn1], 
        COUNT_BIG(*) AS [c01umn2] 
FROM [dbo].[TpPriceCalc] [a] 
GROUP BY [a].[Check], [a].[Account.Id], [a].[Owner.Id];

Results will be correct. But there are two things - you need to query Accounts and Owners by its IDs manually and you will get default values of Key fields instead of null (in your case Guid.Empty) because of Key fields can't be null.

Second query from comment is similar to first.

I've just added this case to task list but I can't say concrete version in which it will be fixed.

Subscription:

Related questions