Fixed object-name-related JOINing issue ThatRendle#157

ThatRendle · ThatRendle · commit 02162bedc178 · 2012-02-23T13:52:21.000Z
diff --git a/Simple.Data.Ado/QueryBuilder.cs b/Simple.Data.Ado/QueryBuilder.cs
@@ -206,11 +206,19 @@ private void HandleJoins()
                                      ? joiner.GetJoinClauses(_tableName, GetObjectReferences(_columns).Where(o => !joinClauses.Any(j => o.GetOwner().Equals(j.Table))), JoinType.Outer)
                                      : Enumerable.Empty<string>();
 
-            var joins = string.Join(" ", fromTable.Concat(fromJoins)
-                                             .Concat(fromCriteria)
-                                             .Concat(fromHavingCriteria)
-                                             .Concat(fromColumnList)
-                                             .Distinct());
+            var joinList = fromTable.Concat(fromJoins).Concat(fromCriteria).Concat(fromHavingCriteria).Concat(fromColumnList).Select(s => s.Trim()).Distinct().ToList();
+
+            var leftJoinList = joinList.Where(s => s.StartsWith("LEFT ", StringComparison.OrdinalIgnoreCase)).ToList();
+
+            foreach (var leftJoin in leftJoinList)
+            {
+                if (joinList.Any(s => s.Equals(leftJoin.Substring(5), StringComparison.OrdinalIgnoreCase)))
+                {
+                    joinList.Remove(leftJoin);
+                }
+            }
+
+            var joins = string.Join(" ", joinList);
 
             if (!string.IsNullOrWhiteSpace(joins))
             {
diff --git a/Simple.Data.BehaviourTest/Query/WithTest.cs b/Simple.Data.BehaviourTest/Query/WithTest.cs
@@ -172,5 +172,23 @@ public void MultipleWithClauseJustDoesEverythingYouWouldHope()
 
             GeneratedSqlIs(expectedSql);
         }
+
+        /// <summary>
+        /// Test for issue #157
+        /// </summary>
+        [Test]
+        public void CriteriaReferencesShouldNotBeDuplicatedInSql()
+        {
+            const string expectedSql = "select [dbo].[employee].[id],[dbo].[employee].[name]," +
+                "[dbo].[employee].[managerid],[dbo].[employee].[departmentid]," +
+                "[dbo].[department].[id] as [__with1__department__id],[dbo].[department].[name] as [__with1__department__name]" +
+                " from [dbo].[employee] join [dbo].[department] on ([dbo].[department].[id] = [dbo].[employee].[departmentid])" +
+                " where [dbo].[department].[name] = @p1";
+
+            var q = _db.Employees.FindAll(_db.Employees.Department.Name == "Dev").WithDepartment();
+            EatException(() => q.ToList());
+
+            GeneratedSqlIs(expectedSql);
+        }
     }
 }
diff --git a/Simple.Data.SqlTest/QueryTest.cs b/Simple.Data.SqlTest/QueryTest.cs
@@ -331,6 +331,18 @@ public void WithClauseShouldPreselectDetailTableAsCollection()
             Assert.AreEqual(1, orders.Count);
         }
 
+        [Test]
+        public void WithClauseWithJoinCriteriaShouldPreselectDetailTableAsCollection()
+        {
+            var db = DatabaseHelper.Open();
+            var result = db.Customers.FindAll(db.Customers.Order.OrderId == 1).WithOrders().FirstOrDefault() as IDictionary<string, object>;
+            Assert.IsNotNull(result);
+            Assert.Contains("Orders", (ICollection)result.Keys);
+            var orders = result["Orders"] as IList<IDictionary<string, object>>;
+            Assert.IsNotNull(orders);
+            Assert.AreEqual(1, orders.Count);
+        }
+
         [Test]
         public void WithClauseShouldPreselectMasterTableAsDictionary()
         {
