|
0
|
1 using System;
|
|
|
2 using System.Linq;
|
|
|
3
|
|
|
4 using BLToolkit.Mapping;
|
|
|
5
|
|
|
6 using NUnit.Framework;
|
|
|
7
|
|
|
8 namespace Data.Linq.UserTests
|
|
|
9 {
|
|
|
10 [TestFixture]
|
|
|
11 public class GroupBySubqueryTest : TestBase
|
|
|
12 {
|
|
|
13 class Table1
|
|
|
14 {
|
|
|
15 public long Field1 { get; set; }
|
|
|
16 public int Field2 { get; set; }
|
|
|
17
|
|
|
18 [Nullable]
|
|
|
19 public int? Field3 { get; set; }
|
|
|
20
|
|
|
21 [Association(ThisKey = "Field1", OtherKey = "Field1", CanBeNull = false)]
|
|
|
22 public Table3 Ref1 { get; set; }
|
|
|
23
|
|
|
24 [Association(ThisKey = "Field3", OtherKey = "Field3", CanBeNull = true)]
|
|
|
25 public Table5 Ref2 { get; set; }
|
|
|
26
|
|
|
27 [Association(ThisKey = "Field2", OtherKey = "Field2", CanBeNull = true)]
|
|
|
28 public Table2 Ref3 { get; set; }
|
|
|
29 }
|
|
|
30
|
|
|
31 class Table2
|
|
|
32 {
|
|
|
33 public int Field2 { get; set; }
|
|
|
34 public string Field4 { get; set; }
|
|
|
35 }
|
|
|
36
|
|
|
37 class Table3
|
|
|
38 {
|
|
|
39 public int Field5 { get; set; }
|
|
|
40 public long Field1 { get; set; }
|
|
|
41
|
|
|
42 [AssociationAttribute(ThisKey = "Field5", OtherKey = "Field5", CanBeNull = false)]
|
|
|
43 public Table4 Ref4 { get; set; }
|
|
|
44 }
|
|
|
45
|
|
|
46 class Table4
|
|
|
47 {
|
|
|
48 public int Field5 { get; set; }
|
|
|
49 public int Field6 { get; set; }
|
|
|
50 }
|
|
|
51
|
|
|
52 public class Table5
|
|
|
53 {
|
|
|
54 [Nullable]
|
|
|
55 public int? Field3 { get; set; }
|
|
|
56 public int Field7 { get; set; }
|
|
|
57
|
|
|
58 [Association(ThisKey = "Field7", OtherKey = "Field7", CanBeNull = true)]
|
|
|
59 public Table6 Ref5 { get; set; }
|
|
|
60 }
|
|
|
61
|
|
|
62 public class Table6
|
|
|
63 {
|
|
|
64 public int Field7 { get; set; }
|
|
|
65 public string Field8 { get; set; }
|
|
|
66 }
|
|
|
67
|
|
|
68 [Test]
|
|
|
69 public void Test()
|
|
|
70 {
|
|
|
71 using (var db = new TestDbManager())
|
|
|
72 {
|
|
|
73 var q = (
|
|
|
74 from t1 in db.GetTable<Table1>()
|
|
|
75 where t1.Field3 != null
|
|
|
76 select new
|
|
|
77 {
|
|
|
78 t1.Ref1.Ref4.Field6, t1.Ref3.Field4,
|
|
|
79 Field1 = t1.Ref2.Ref5.Field8 ?? string.Empty
|
|
|
80 }
|
|
|
81 ).Distinct();
|
|
|
82
|
|
|
83 var sql1 = q.ToString();
|
|
|
84
|
|
|
85 var q2 =
|
|
|
86 from t3 in q
|
|
|
87 group t3 by new { t3.Field6, t3.Field4 }
|
|
|
88 into g
|
|
|
89 where g.Count() > 1
|
|
|
90 select new { g.Key.Field6, EngineeringCircuitNumber = g.Key.Field4, Count = g.Count() };
|
|
|
91
|
|
|
92 var sql2 = q2.ToString();
|
|
|
93
|
|
|
94 var idx = sql2.IndexOf("DISTINCT");
|
|
|
95
|
|
|
96 Assert.That(idx, Is.GreaterThanOrEqualTo(0));
|
|
|
97
|
|
|
98 idx = sql2.IndexOf("Field8", idx);
|
|
|
99
|
|
|
100 Assert.That(idx, Is.GreaterThanOrEqualTo(0));
|
|
|
101 }
|
|
|
102 }
|
|
|
103 }
|
|
|
104 }
|
