Skip to content

Commit ab749cc

Browse files
ThatRendleThatRendle
committed
Changed Exists/Any to use DISTINCT 1 instead of COUNT(*)
1 parent f7e3008 commit ab749cc

8 files changed

Lines changed: 36 additions & 11 deletions

File tree

‎Simple.Data.Ado/QueryBuilder.cs‎

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -101,14 +101,21 @@ private string GetSelectClause(ObjectName tableName)
101101

102102
private string GetColumnsClause(Table table)
103103
{
104-
return _query.Columns.Count() == 1 && _query.Columns.Single() is CountSpecialReference
104+
return _query.Columns.Count() == 1 && _query.Columns.Single() is SpecialReference
105105
?
106-
"COUNT(*)"
106+
FormatSpecialReference((SpecialReference)_query.Columns.Single())
107107
:
108108
string.Join(",", GetColumnsToSelect(table)
109109
.Select(c => FormatColumnClause(c.Item1, c.Item2, c.Item3)));
110110
}
111111

112+
private static string FormatSpecialReference(SpecialReference reference)
113+
{
114+
if (reference.GetType() == typeof(CountSpecialReference)) return "COUNT(*)";
115+
if (reference.GetType() == typeof(ExistsSpecialReference)) return "DISTINCT 1";
116+
throw new InvalidOperationException("SpecialReference type not recognised.");
117+
}
118+
112119
private IEnumerable<Tuple<Table,Column,string>> GetColumnsToSelect(Table table)
113120
{
114121
if (_query.Columns.Any())

‎Simple.Data.BehaviourTest/DatabaseTest.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -123,7 +123,7 @@ public void TestInsertWithStaticTypeObject()
123123
_db.Users.Insert(user);
124124
GeneratedSqlIs("insert into [dbo].[Users] ([Name],[Password],[Age]) values (@p0,@p1,@p2)");
125125
Parameter(0).Is("Steve");
126-
Parameter(1).Is(null);
126+
Parameter(1).Is(DBNull.Value);
127127
Parameter(2).Is(50);
128128
}
129129

‎Simple.Data.BehaviourTest/QueryTest.cs‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -71,7 +71,7 @@ public void SpecifyingCountShouldSelectCount()
7171
}
7272

7373
[Test]
74-
public void SpecifyingExistsShouldSelectCount()
74+
public void SpecifyingExistsShouldSelectDistinct1()
7575
{
7676
try
7777
{
@@ -82,11 +82,11 @@ public void SpecifyingExistsShouldSelectCount()
8282
// This won't work on Mock provider, but the SQL should be generated OK
8383
}
8484

85-
GeneratedSqlIs("select count(*) from [dbo].[users]");
85+
GeneratedSqlIs("select distinct 1 from [dbo].[users]");
8686
}
8787

8888
[Test]
89-
public void SpecifyingAnyShouldSelectCount()
89+
public void SpecifyingAnyShouldSelectDistinct1()
9090
{
9191
try
9292
{
@@ -97,7 +97,7 @@ public void SpecifyingAnyShouldSelectCount()
9797
// This won't work on Mock provider, but the SQL should be generated OK
9898
}
9999

100-
GeneratedSqlIs("select count(*) from [dbo].[users]");
100+
GeneratedSqlIs("select distinct 1 from [dbo].[users]");
101101
}
102102
}
103103
}

‎Simple.Data.SqlTest/QueryTest.cs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,7 @@ public void ExistsWithValidCriteriaShouldReturnTrue()
4545
}
4646

4747
[Test]
48-
public void ExistsWithInvalidCriteriaShouldReturnTrue()
48+
public void ExistsWithInvalidCriteriaShouldReturnFalse()
4949
{
5050
var db = DatabaseHelper.Open();
5151
Assert.AreEqual(false, db.Users.Exists(db.Users.Age == -1));
@@ -59,7 +59,7 @@ public void ExistsByValidValueShouldReturnTrue()
5959
}
6060

6161
[Test]
62-
public void ExistsByInvalidValueShouldReturnTrue()
62+
public void ExistsByInvalidValueShouldReturnFalse()
6363
{
6464
var db = DatabaseHelper.Open();
6565
Assert.AreEqual(false, db.Users.ExistsByName("Peter Kay"));

‎Simple.Data/CountSpecialReference.cs‎

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,13 @@
55

66
namespace Simple.Data
77
{
8-
public class CountSpecialReference : DynamicReference
8+
public abstract class SpecialReference : DynamicReference
9+
{
10+
protected SpecialReference(string name) : base(name)
11+
{
12+
}
13+
}
14+
public class CountSpecialReference : SpecialReference
915
{
1016
public CountSpecialReference() : base("COUNT")
1117
{

‎Simple.Data/ExistsSpecialReference.cs‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,11 @@
1+
namespace Simple.Data
2+
{
3+
public class ExistsSpecialReference : SpecialReference
4+
{
5+
public ExistsSpecialReference()
6+
: base("EXISTS")
7+
{
8+
9+
}
10+
}
11+
}

‎Simple.Data/Simple.Data.csproj‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -82,6 +82,7 @@
8282
<Compile Include="CountSpecialReference.cs" />
8383
<Compile Include="Database.Open.cs" />
8484
<Compile Include="EnumerableEx.cs" />
85+
<Compile Include="ExistsSpecialReference.cs" />
8586
<Compile Include="Extensions\HomogenizeEx.cs" />
8687
<Compile Include="Extensions\ResultSetExtensions.cs" />
8788
<Compile Include="HomogenizedEqualityComparer.cs" />

‎Simple.Data/SimpleQuery.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -309,7 +309,7 @@ public int Count()
309309
/// <returns><c>true</c> if the query matches any record; otherwise, <c>false</c>.</returns>
310310
public bool Exists()
311311
{
312-
return (int)_adapter.RunQuery(this.Select(new CountSpecialReference())).Single().Single().Value > 0;
312+
return _adapter.RunQuery(this.Select(new ExistsSpecialReference())).Count() == 1;
313313
}
314314

315315
/// <summary>

0 commit comments

Comments
 (0)