C# SqlQuery.ToString方法代码示例

        public void DistinctAddsKeyword()
        {
            var query = new SqlQuery()
                .Distinct(true)
                .Select("TestColumn")
                .From("TestTable");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT DISTINCT TestColumn FROM TestTable"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void FromMultipleCallsDoesCrossJoin()
        {
            var query = new SqlQuery()
                .From("TestTable1")
                .From("TestTable2")
                .Select("TestColumn");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT TestColumn FROM TestTable1, TestTable2"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void DistinctCanBeTurnedOff()
        {
            var query = new SqlQuery()
                .Distinct(true)
                .Select("TestColumn")
                .From("TestTable")
                .Distinct(false);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT TestColumn FROM TestTable"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void FromMixedOverloadsMultipleCallsDoesCrossJoin()
        {
            var query = new SqlQuery()
                .From("TestTable1", new Alias("x1"))
                .From(new Alias("TestTable2", "x2"))
                .From("TestTable3")
                .Select("TestColumn");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT TestColumn FROM TestTable1 x1, TestTable2 x2, TestTable3"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void DoesAutomaticJoinProperlyWithAliasedFields()
        {
            var x = RowMappingTests.ComplexRow.Fields.As("x");

            var query = new SqlQuery()
                    .From(x)
                    .Select(x.CountryName);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT x_c.Name AS [CountryName] FROM ComplexTable x " +
                    "LEFT JOIN TheCountryTable x_c ON (x_c.TheCountryID = x.CountryID)"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void DoesAutomaticJoinProperlyWithAliasedAndJoinedFieldsAndSelect()
        {
            var x = RowMappingTests.ComplexRow.Fields.As("x");
            var y = RowMappingTests.ComplexRow.Fields.As("y");

            var query = new SqlQuery()
                    .From(x)
                    .LeftJoin(y, y.ID == x.ID)
                    .Select(x.CountryName, "CountryNameX")
                    .Select(y.CountryName, "CountryNameY");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT " +
                        "x_c.Name AS [CountryNameX], " +
                        "y_c.Name AS [CountryNameY] " +
                    "FROM ComplexTable x " +
                    "LEFT JOIN ComplexTable y ON (y.ComplexID = x.ComplexID) " +
                    "LEFT JOIN TheCountryTable x_c ON (x_c.TheCountryID = x.CountryID) " +
                    "LEFT JOIN TheCountryTable y_c ON (y_c.TheCountryID = y.CountryID)"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void DoesAutomaticJoinProperlyWithViewRowIfAllNamesAreSelectedInJoinOrder()
        {
            var fld = ViewRow.Fields;

            var query = new SqlQuery()
                    .From(fld)
                    .Select(fld.District)
                    .Select(fld.City)
                    .Select(fld.Country);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT " +
                        "d.Name AS [District], " +
                        "c.Name AS [City], " +
                        "o.Name AS [Country] " +
                    "FROM ViewTable T0 " +
                    "LEFT JOIN Districts d ON (d.DistrictID = T0.DistrictID) " +
                    "LEFT JOIN Cities c ON (c.CityID = d.CityID) " +
                    "LEFT JOIN Countries o ON (o.CountryID = c.CountryID) "),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void OrderByFirstMovesExpressionToStartIfAlreadyInStatement()
        {
            var query = new SqlQuery()
                .From("TestTable")
                .Select("a")
                .OrderBy("a")
                .OrderBy("b")
                .OrderBy("c")
                .OrderByFirst("b");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT a FROM TestTable ORDER BY b, a, c"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void OrderByFirstHandlesDescWhileMovingExpressionToFirst()
        {
            var query1 = new SqlQuery()
                .From("TestTable")
                .Select("a")
                .OrderBy("a")
                .OrderBy("b", desc: true)
                .OrderBy("c")
                .OrderByFirst("b");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT a FROM TestTable ORDER BY b, a, c"),
                TestSqlHelper.Normalize(
                    query1.ToString()));

            var query2 = new SqlQuery()
                .From("TestTable")
                .Select("a")
                .OrderBy("a")
                .OrderBy("b")
                .OrderBy("c")
                .OrderByFirst("b", desc: true);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT a FROM TestTable ORDER BY b DESC, a, c"),
                TestSqlHelper.Normalize(
                    query2.ToString()));
        }

        public void DoesAutomaticJoinProperlyWithViewRowIfAreSelectedInJoinOrderAliased()
        {
            var vw = ViewRow.Fields.As("vw");

            var query = new SqlQuery()
                    .From(vw)
                    .Select(vw.District)
                    .Select(vw.City)
                    .Select(vw.Country);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT " +
                        "vw_d.Name AS [District], " +
                        "vw_c.Name AS [City], " +
                        "vw_o.Name AS [Country] " +
                    "FROM ViewTable vw " +
                    "LEFT JOIN Districts vw_d ON (vw_d.DistrictID = vw.DistrictID) " +
                    "LEFT JOIN Cities vw_c ON (vw_c.CityID = vw_d.CityID) " +
                    "LEFT JOIN Countries vw_o ON (vw_o.CountryID = vw_c.CountryID) "),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void TakeUsesCorrectSyntaxForSqliteDialect()
        {
            var query = new SqlQuery()
                .Dialect(SqliteDialect.Instance)
                .Select("c")
                .From("t")
                .Take(10);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT c FROM t LIMIT 10"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void SubQueryCanBeUsedAsCriteriaUsingWith()
        {
            var query = new SqlQuery()
                .From("ParentTable")
                .Select("ParentColumn")
                .With(me => me.Where(new Criteria(me.SubQuery()
                    .From("SubTable")
                    .Take(1)
                    .Select("SubColumn")) >= 1));

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT ParentColumn FROM ParentTable WHERE " +
                        "((SELECT TOP 1 SubColumn FROM SubTable) >= @p1)"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void DoesAutomaticJoinsIfFromRowUsed()
        {
            var fld = RowMappingTests.ComplexRow.Fields;

            var query = new SqlQuery()
                    .From(new RowMappingTests.ComplexRow())
                    .Select(fld.CountryName);

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT c.Name AS [CountryName] FROM ComplexTable T0 " +
                    "LEFT JOIN TheCountryTable c ON (c.TheCountryID = T0.CountryID)"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void OrderByWithExpressionWorks()
        {
            var query = new SqlQuery()
                .Select("TestColumn")
                .From("TestTable")
                .OrderBy("TestColumn")
                .OrderBy("TestColumn2");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT TestColumn FROM TestTable ORDER BY TestColumn, TestColumn2"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }

        public void FromWithTableNameAndAliasWorks()
        {
            var query = new SqlQuery()
                .From("TestTable", new Alias("TestAlias"))
                .Select("TestColumn");

            Assert.Equal(
                TestSqlHelper.Normalize(
                    "SELECT TestColumn FROM TestTable TestAlias"),
                TestSqlHelper.Normalize(
                    query.ToString()));
        }