public void InitialTest()
        {
            //var paths = new List<Path>()
            //{
            //    new Path()
            //    {
            //        id = 199,
            //        path = "$.Audit.CreatedOn",
            //    },
            //    new Path()
            //    {
            //        id = 150,
            //        path ="$.Name",
            //    }

            //    // ...etc.
            //};
            var paths = new Dictionary <string, int>
            {
                { "$.Audit.CreatedOn", 199 },
                { "$.Name", 150 },
                { "$.Id", 155 },
                // ...etc.
            };

            var docType    = new DocumentType(typeof(Employee));
            var queryLines = new List <SearchQuery.Line>()
            {
                new SearchQuery.Line(
                    ContentPath.Parse($"$.{nameof(Employee.Name)}")
                    , SearchQuery.Op.Equals, ContentFactory.Default.CreateFrom("John Smith")),
                new SearchQuery.Line(
                    ContentPath.Parse($"$.{nameof(Employee.Id)}")
                    , SearchQuery.Op.Equals, ContentFactory.Default.CreateFrom(1)),
            };
            var query              = new SearchQuery(docType, queryLines, ContentPath.Parse($"$.{nameof(Employee.Id)}"), false, 0, 20);
            var actual             = SqlResolver.ResolveSqlText(query, (s) => paths[s], "[search].[Docs]", "[search].[DocTokens]");
            var expectedWithSpaces = $@"SELECT [B].* FROM (SELECT DISTINCT filtered.DocumentId, Sorted.ValueAsAny FROM 
                ( SELECT f1.DocumentId FROM 
                    (    
                    SELECT DocumentId FROM [search].[DocTokens]  WITH (NOLOCK)
                    WHERE ([ValueAsAny]='John Smith' AND [PathId]=150) 
                     UNION ALL  
                    SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK)
                    WHERE ([ValueAsAny]='011.000000' AND [PathId]=155) 
                    ) as f1 
			        group by f1.DocumentId HAVING count(*) >= {query.QueryLines.Length}               
                ) as filtered 
                LEFT JOIN (SELECT DocumentId,ValueAsAny FROM [search].[DocTokens] WITH (NOLOCK)
                WHERE [PathId] = {paths["$.Id"]}) as Sorted
                ON Sorted.DocumentId = filtered.DocumentId) AS [A] 
                INNER JOIN (SELECT * FROM [search].[Docs] WITH (NOLOCK)
                WHERE [SourceType] = '{query.DocumentType.Name}') AS [B] ON [A].DocumentId = [B].Id
                ORDER BY [A].ValueAsAny
                OFFSET {query.Offset} ROWS
                FETCH NEXT {query.Limit} ROWS ONLY";

            Assert.AreEqual(Regex.Replace(expectedWithSpaces, @"\s+", string.Empty), Regex.Replace(actual, @"\s+", string.Empty));
        }
        public void NotNull()
        {
            var paths = new Dictionary <string, int>
            {
                { "$.Audit.CreatedOn", 199 },
                { "$.Name", 150 },
                { "$.Id", 155 },
                { "$.StartDate", 156 }
                // ...etc.
            };
            var docType    = new DocumentType(typeof(Employee));
            var queryLines = new List <SearchQuery.Line>()
            {
                new SearchQuery.Line(
                    ContentPath.Parse($"$.{nameof(Employee.StartDate)}")
                    , SearchQuery.Op.NotEquals, ContentFactory.Default.CreateFrom(null)),
                new SearchQuery.Line(
                    ContentPath.Parse($"$.{nameof(Employee.Name)}")
                    , SearchQuery.Op.GreaterThanOrEquals, ContentFactory.Default.CreateFrom(new DateTime(2019, 08, 01))),

                new SearchQuery.Line(
                    ContentPath.Parse($"$.{nameof(Employee.Name)}")
                    , SearchQuery.Op.LessThanOrEquals, ContentFactory.Default.CreateFrom(new DateTime(2019, 11, 27)))
            };
            var query  = new SearchQuery(docType, queryLines, ContentPath.Parse($"$.{nameof(Employee.Id)}"), true, 0, 20);
            var actual = SqlResolver.ResolveSqlText(query, (s) => paths[s], "[search].[Docs]", "[search].[DocTokens]");

            var expectedWithSpaces = $@"SELECT [B].* FROM (SELECT DISTINCT filtered.DocumentId, Sorted.ValueAsAny FROM 
                (
                     SELECT f1.DocumentId FROM 
                    (    
                        SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK) 
                        WHERE ([ValueAsAny] IS NOT NULL AND [PathId]=156)
                        UNION ALL
                        SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK) 
                            WHERE ([ValueAsAny]>='2019-08-01T00:00:00.0000000' AND [PathId]=150)
                        UNION ALL
                        SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK) 
                        WHERE ([ValueAsAny]<='2019-11-27T00:00:00.0000000' AND [PathId]=150)
                    ) as f1 
			        group by f1.DocumentId HAVING count(*) >= {query.QueryLines.Length} 
                     
                ) as filtered 
                LEFT JOIN (SELECT DocumentId,ValueAsAny FROM [search].[DocTokens] WITH (NOLOCK)
                WHERE [PathId] = {paths["$.Id"]}) as Sorted
                ON Sorted.DocumentId = filtered.DocumentId
                ) AS [A] 
                INNER JOIN (SELECT * FROM [search].[Docs] WITH (NOLOCK)
                WHERE [SourceType] = '{query.DocumentType}') AS [B] ON [A].DocumentId = [B].Id
                ORDER BY [A].ValueAsAny DESC 
                OFFSET {query.Offset} ROWS
                FETCH NEXT {query.Limit} ROWS ONLY";

            Assert.AreEqual(Regex.Replace(expectedWithSpaces, @"\s+", string.Empty), Regex.Replace(actual, @"\s+", string.Empty));
        }
        public void AnyOf()
        {
            var paths = new Dictionary <string, int>
            {
                { "$.Audit.CreatedOn", 199 },
                { "$.Name", 150 },
                { "$.Id", 155 },
                { "$.Phones", 156 },
                // ...etc.
            };
            var docType    = new DocumentType(typeof(Employee));
            var queryLines = new List <SearchQuery.Line>()
            {
                new SearchQuery.Line(
                    ContentPath.Parse($"$.{nameof(Employee.Phones)}")
                    , SearchQuery.Op.AnyOf, ContentFactory.Default.CreateFrom(new string[] { "0001ddd", "0002ddd", "0003ddd" })),

                new SearchQuery.Line(
                    ContentPath.Parse($"$.{nameof(Employee.Name)}")
                    , SearchQuery.Op.AnyOf, ContentFactory.Default.CreateFrom(new int[] { 1, 2, 3 }))
            };
            var query = new SearchQuery(docType, queryLines, ContentPath.Parse($"$.{nameof(Employee.Id)}"), true, 0, 20);

            var actual             = SqlResolver.ResolveSqlText(query, (s) => paths[s], "[search].[Docs]", "[search].[DocTokens]");
            var expectedWithSpaces = $@"SELECT [B].* FROM (SELECT DISTINCT filtered.DocumentId, Sorted.ValueAsAny FROM 
                (
                    SELECT f1.DocumentId FROM 
                    (    
                        SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK) 
                        WHERE ([ValueAsAny] IN ('0001ddd','0002ddd','0003ddd') AND [PathId]=156) 
                        UNION ALL
                        SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK)
                        WHERE ([ValueAsAny] IN ('011.000000','012.000000','013.000000') AND [PathId]=150) 
                    ) as f1 
			        group by f1.DocumentId HAVING count(*) >= {query.QueryLines.Length}  
                ) as filtered 
                LEFT JOIN (SELECT DocumentId,ValueAsAny FROM [search].[DocTokens] WITH (NOLOCK)
                WHERE [PathId] = 155) as Sorted
                ON Sorted.DocumentId = filtered.DocumentId
               ) AS [A] 
                INNER JOIN (SELECT * FROM [search].[Docs] WITH (NOLOCK)
                WHERE [SourceType] = '{query.DocumentType.Name}') AS [B] ON [A].DocumentId = [B].Id
                ORDER BY [A].ValueAsAny DESC
                OFFSET {query.Offset} ROWS
                FETCH NEXT {query.Limit} ROWS ONLY";

            Assert.AreEqual(Regex.Replace(expectedWithSpaces, @"\s+", string.Empty), Regex.Replace(actual, @"\s+", string.Empty));
        }
        public void EmptyQuery()
        {
            var paths = new Dictionary <string, int>
            {
                { "$.Audit.CreatedOn", 199 },
                { "$.Id", 155 },


                // ...etc.
            };
            var docType    = new DocumentType(typeof(Employee));
            var queryLines = new List <SearchQuery.Line>();

            var query  = new SearchQuery(docType, queryLines, ContentPath.Parse($"$.{nameof(Employee.Id)}"), true, 0, 20);
            var actual = SqlResolver.ResolveSqlText(query, (s) => paths[s], "[search].[Docs]", "[search].[DocTokens]");

            var expectedWithSpaces = $@"SELECT [B].* FROM (SELECT DISTINCT filtered.DocumentId, Sorted.ValueAsAny FROM 
                (
                     SELECT f1.DocumentId FROM 
                        (    
                            SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK) Group by [DocumentId]

                        ) as f1 
			        group by f1.DocumentId HAVING count(*) >= {query.QueryLines.Length} 
                ) as filtered 
                LEFT JOIN (SELECT DocumentId,ValueAsAny FROM [search].[DocTokens] WITH (NOLOCK)
                WHERE [PathId] = {paths["$.Id"]}) as Sorted
                ON Sorted.DocumentId = filtered.DocumentId
                ) AS [A] 
                INNER JOIN (SELECT * FROM [search].[Docs] WITH (NOLOCK)
                WHERE [SourceType] = '{query.DocumentType}') AS [B] ON [A].DocumentId = [B].Id 
                ORDER BY [A].ValueAsAny DESC
                OFFSET 0 ROWS
                FETCH NEXT 20 ROWS ONLY";

            Assert.AreEqual(Regex.Replace(expectedWithSpaces, @"\s+", string.Empty), Regex.Replace(actual, @"\s+", string.Empty));
        }