private void JoinViaIn(SqlStatement statement, SqlSelect select) { var table = GetStatementTable(statement); var where = GetStatementWhere(statement); JoinedTableRef = table; var indexMapping = PrimaryIndexes[0]; var columns = new List <ColumnInfo>(); foreach (var columnInfo in indexMapping.PrimaryIndex.KeyColumns.Keys) { var s = (SqlSelect)select.Clone(); foreach (var column in columns) { var ex = SqlDml.Equals(s.From.Columns[column.Name], table.Columns[column.Name]); s.Where = s.Where.IsNullReference() ? ex : SqlDml.And(s.Where, ex); } var existingColumns = s.Columns.ToChainedBuffer(); s.Columns.Clear(); var columnToAdd = existingColumns.First(c => c.Name.Equals(columnInfo.Name, StringComparison.Ordinal)); s.Columns.Add(columnToAdd); var @in = SqlDml.In(SqlDml.TableColumn(table, columnInfo.Name), s); where = where.IsNullReference() ? @in : SqlDml.And(where, @in); columns.Add(columnInfo); } SetStatementWhere(statement, where); }
public void SqlSelectCloneTest() { SqlTableRef tr1 = SqlDml.TableRef(table1); SqlTableRef tr2 = SqlDml.TableRef(table2); SqlSelect s = SqlDml.Select(); s.Distinct = true; s.Limit = 3; s.Columns.Add(tr1["ID"]); s.Columns.Add(tr1["ID"], "ID2"); s.Columns.Add(tr1["ID"] + tr1["ID"], "SUM2"); s.Columns.Add(tr2.Asterisk); s.From = tr1.InnerJoin(tr2, tr1["ID"] == tr2["ID"]); s.Where = SqlDml.Like(tr1["Name"], "Marat"); s.Hints.Add(SqlDml.FastFirstRowsHint(10)); SqlSelect sClone = (SqlSelect)s.Clone(); Assert.AreNotEqual(s, sClone); Assert.AreNotEqual(s.Columns, sClone.Columns); for (int i = 0, l = s.Columns.Count; i < l; i++) { Assert.AreNotEqual(s.Columns[i], sClone.Columns[i]); } for (int i = 0, l = s.GroupBy.Count; i < l; i++) { Assert.AreNotEqual(s.Columns[i], sClone.Columns[i]); } Assert.AreEqual(s.Distinct, sClone.Distinct); if (s.From == null) { Assert.AreEqual(s.From, sClone.From); } else { Assert.AreNotEqual(s.From, sClone.From); Assert.AreEqual(s.From.NodeType, sClone.From.NodeType); } if (!s.Having.IsNullReference()) { Assert.AreNotEqual(s.Having, sClone.Having); Assert.AreEqual(s.Having.NodeType, sClone.Having.NodeType); } Assert.AreEqual(s.NodeType, sClone.NodeType); Assert.IsFalse(s.OrderBy == sClone.OrderBy); Assert.AreEqual(s.OrderBy.Count, sClone.OrderBy.Count); for (int i = 0, l = s.OrderBy.Count; i < l; i++) { Assert.AreNotEqual(s.OrderBy[i], sClone.OrderBy[i]); Assert.AreEqual(s.OrderBy[i].Ascending, sClone.OrderBy[i].Ascending); Assert.AreNotEqual(s.OrderBy[i].Expression, sClone.OrderBy[i].Expression); Assert.AreEqual(s.OrderBy[i].Position, sClone.OrderBy[i].Position); } Assert.AreEqual(s.Limit, sClone.Limit); if (s.Where != null) { Assert.AreNotEqual(s.Where, sClone.Where); Assert.AreEqual(s.Where.NodeType, sClone.Where.NodeType); } s.Where &= tr1[0] > 1200 || tr2[1] != "Marat"; s.OrderBy.Add(tr1["ID"], false); s.OrderBy.Add(2); sClone = (SqlSelect)s.Clone(); Assert.AreNotEqual(s, sClone); Assert.AreNotEqual(s.Columns, sClone.Columns); for (int i = 0, l = s.Columns.Count; i < l; i++) { Assert.AreNotEqual(s.Columns[i], sClone.Columns[i]); } for (int i = 0, l = s.GroupBy.Count; i < l; i++) { Assert.AreNotEqual(s.Columns[i], sClone.Columns[i]); } Assert.AreEqual(s.Distinct, sClone.Distinct); if (s.From == null) { Assert.AreEqual(s.From, sClone.From); } else { Assert.AreNotEqual(s.From, sClone.From); Assert.AreEqual(s.From.NodeType, sClone.From.NodeType); } if (s.Having != null) { Assert.AreNotEqual(s.Having, sClone.Having); Assert.AreEqual(s.Having.NodeType, sClone.Having.NodeType); } Assert.AreEqual(s.NodeType, sClone.NodeType); Assert.AreNotEqual(s.OrderBy, sClone.OrderBy); Assert.AreEqual(s.OrderBy.Count, sClone.OrderBy.Count); for (int i = 0, l = s.OrderBy.Count; i < l; i++) { Assert.AreNotEqual(s.OrderBy[i], sClone.OrderBy[i]); Assert.AreEqual(s.OrderBy[i].Ascending, sClone.OrderBy[i].Ascending); if (s.OrderBy[i].Expression.IsNullReference()) { Assert.AreEqual(s.OrderBy[i].Expression, sClone.OrderBy[i].Expression); } else { Assert.AreNotEqual(s.OrderBy[i].Expression, sClone.OrderBy[i].Expression); } Assert.AreEqual(s.OrderBy[i].Position, sClone.OrderBy[i].Position); } Assert.AreEqual(s.Limit, sClone.Limit); if (s.Where != null) { Assert.AreNotEqual(s.Where, sClone.Where); Assert.AreEqual(s.Where.NodeType, sClone.Where.NodeType); } Assert.AreEqual(s.Hints.Count, sClone.Hints.Count); SqlSelect s2 = SqlDml.Select(); SqlQueryRef t = SqlDml.QueryRef(s, "SUBSELECT"); s2.From = t; s2.Columns.Add(t.Asterisk); SqlSelect s2Clone = (SqlSelect)s2.Clone(); Assert.AreNotEqual(s2, s2Clone); Assert.AreEqual(s2.Columns.Count, s2Clone.Columns.Count); }