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) {}
}

asked May 28 '15 at 02:10

pil0t's gravatar image

pil0t
207535763

Query could be simplified to

                var result = from r in Query.All<TpPriceCalc>()
                             group r by r.Account into gr
                             select new 
                             {
                                 gr.Key,
                                 Cnt = gr.Count()
                             };
(May 28 '15 at 02:15) pil0t pil0t's gravatar image

One Answer:

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.

answered Jun 10 '15 at 06:31

Alexey%20Kulakov's gravatar image

Alexey Kulakov
45215

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:

×120
×22
×2

Asked: May 28 '15 at 02:10

Seen: 3,075 times

Last updated: Jun 10 '15 at 06:31

powered by OSQA