public void SelectMany_with_DefaultIfEmpty_translates_into_left_outer_join()
        {
            using (var context = new ArubaContext())
            {
                var expectedSql =
@"SELECT 
[Extent1].[Id] AS [Id], 
[Extent2].[Id] AS [Id1], 
[Extent2].[Name] AS [Name], 
[Extent2].[Deleted] AS [Deleted], 
[Extent2].[TaskInfo_Passed] AS [TaskInfo_Passed], 
[Extent2].[TaskInfo_Failed] AS [TaskInfo_Failed], 
[Extent2].[TaskInfo_Investigates] AS [TaskInfo_Investigates], 
[Extent2].[TaskInfo_Improvements] AS [TaskInfo_Improvements], 
[Extent2].[ArubaRun_Id] AS [ArubaRun_Id]
FROM  [dbo].[ArubaRuns] AS [Extent1]
LEFT OUTER JOIN [dbo].[ArubaTasks] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ArubaRun_Id]";

                var query = context.Runs.SelectMany(c => c.Tasks.DefaultIfEmpty());
                QueryTestHelpers.VerifyDbQuery(query, expectedSql);

                // verify that owners that have empty collecion of bugs are projected as nulls
                var results = query.ToList();
                var ownersWithoutBugsCount = context.Runs.Count(o => !o.Tasks.Any());
                Assert.Equal(ownersWithoutBugsCount, results.Count(r => r == null));
            }
        }

        public void DefaultIfEmpty_with_non_null_default()
        {
            using (var context = new ArubaContext())
            {
                var expectedSql =
@"SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[Alias] AS [Alias], 
CASE WHEN ([Join1].[Id1] IS NULL) THEN 0 ELSE [Join1].[Id1] END AS [C1]
FROM  [dbo].[ArubaOwners] AS [Extent1]
LEFT OUTER JOIN  (SELECT [Extent2].[Id] AS [Id1], [Extent2].[ArubaOwner_Id] AS [ArubaOwner_Id]
	FROM  [dbo].[Bugs1] AS [Extent2]
	INNER JOIN [dbo].[Bugs2] AS [Extent3] ON [Extent2].[Id] = [Extent3].[Id] ) AS [Join1] ON ([Extent1].[Id] = [Join1].[ArubaOwner_Id]) AND (1 = [Join1].[Id1])";

                var query = context.Owners.SelectMany(c => c.Bugs.Where(b => b.Id == 1).Select(b => b.Id).DefaultIfEmpty(), (o, b) => new { o, b });
                QueryTestHelpers.VerifyDbQuery(query, expectedSql);

                // verify that all owners are projected
                // verify that the only bug that is non 0 has Id = 1
                var results = query.ToList();
                var ownersCount = context.Owners.Count();
                Assert.Equal(ownersCount, results.Select(r => r.o.Id).Distinct().Count());
                for (int i = 0; i < ownersCount; i++)
                {
                    var bugId = results[i].b;
                    Assert.True(bugId == 0 || bugId == 1);
                }
            }
        }

        public void GeometryPointsWithinPolygon()
        {
            var query = @"
select value 
    g.c32_geometry
from 
    ArubaContext.AllTypes as g
where 
    Edm.SpatialWithin(
        g.c32_geometry,
        GeometryFromText(""MULTILINESTRING ((10 20, 15 20, 15 25, 10 25, 10 20), (12 22, 13 22, 13 23, 12 23, 12 22))"", 32768))";

            // verifying that the points returned are within the specified polygon
            using (var db = new ArubaContext())
            using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, "STWithin"))
            {
                var shape = DbGeometry.MultiLineFromText(
                        "MULTILINESTRING ((10 20, 15 20, 15 25, 10 25, 10 20), (12 22, 13 22, 13 23, 12 23, 12 22))", 32768);
                VerifyValueCondition(reader, o =>
                    {
                        var g = (DbGeometry)o;
                        return DbSpatialServices.Default.Within(g, shape);
                    });
            }
        }

 public TruncationTests()
 {
     using (var context = new ArubaContext())
     {
         context.Database.Initialize(force: false);
     }
 }

 public void String_StartsWith_properly_translated_to_function()
 {
     using (var context = new ArubaContext())
     {
         var query = context.Owners.Select(o => o.LastName.StartsWith("Name"));
         Assert.Contains("LIKE N'NAME%'", query.ToString().ToUpperInvariant());
     }
 }

 public void DateTime_UtcNow_properly_translated_to_function()
 {
     using (var context = new ArubaContext())
     {
         var query = context.Owners.Select(o => DateTime.UtcNow);
         Assert.Contains("SYSUTCDATETIME", query.ToString().ToUpperInvariant());
     }
 }

 public void String_Length_properly_translated_to_function()
 {
     using (var context = new ArubaContext())
     {
         var query = context.Owners.Select(o => o.LastName.Length);
         Assert.Contains("LEN", query.ToString().ToUpperInvariant());
     }
 }

 public void String_Concat_translated_properly_to_plus_operator()
 {
     using (var context = new ArubaContext())
     {
         var query = context.Owners.Select(o => string.Concat(o.LastName, o.FirstName));
         Assert.True(query.ToString().Contains("+"));
     }
 }

 public void DateTimeOffest_Day_properly_translated_to_function()
 {
     using (var context = new ArubaContext())
     {
         var query = context.AllTypes.Select(a => a.c30_datetimeoffset.Day);
         Assert.Contains("DATEPART", query.ToString().ToUpperInvariant());
         Assert.Contains("DAY", query.ToString().ToUpperInvariant());
     }
 }

 public void Include_one_to_one_relationship()
 {
     using (var context = new ArubaContext())
     {
         ((IObjectContextAdapter)context).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
         var query = context.Owners.Include(o => o.OwnedRun);
         var results = query.ToList();
         var runsForOwners = context.Owners.Select(r => r.OwnedRun).ToList();
         Enumerable.SequenceEqual(runsForOwners, results.Select(r => r.OwnedRun)); 
     }
 }

            public void IndexOf_properly_translated_to_function()
            {
                using (var context = new ArubaContext())
                {
                    var query = context.Owners.Select(o => o.LastName.IndexOf("N"));

                    // translated to charindex() - 1
                    Assert.True(query.ToString().ToLowerInvariant().Contains("charindex"));
                    Assert.Contains("CHARINDEX", query.ToString().ToUpperInvariant());
                    Assert.True(query.ToString().Contains("- 1"));
                }
            }

            public void IsNullOrEmpty_translated_properly_to_expression()
            {
                using (var context = new ArubaContext())
                {
                    var expectedSql =
@"SELECT 
    CASE WHEN (([Extent1].[LastName] IS NULL) OR (( CAST(LEN([Extent1].[LastName]) AS int)) = 0)) THEN cast(1 as bit) WHEN ( NOT (([Extent1].[LastName] IS NULL) OR (( CAST(LEN([Extent1].[LastName]) AS int)) = 0))) THEN cast(0 as bit) END AS [C1]
    FROM [dbo].[ArubaOwners] AS [Extent1]";

                    var query = context.Owners.Select(o => string.IsNullOrEmpty(o.LastName));
                    QueryTestHelpers.VerifyDbQuery(query, expectedSql);
                }
            }

 public void Can_materialize_null_complex_type()
 {
     using (var context = new ArubaContext())
     {
         var query = context.Runs.Select(r => r.Tasks.Where(t => t.Id < 0).Select(t => t.TaskInfo).FirstOrDefault());
         var results = query.ToList();
         Assert.IsType<List<ArubaTaskInfo>>(results);
         foreach (var result in results)
         {
             Assert.Null(result);
         }
     }
 }

        public void OrderBy_ThenBy_lifted_above_type_filter()
        {
            using (var context = new ArubaContext())
            {
                var query = context.Configs.OrderByDescending(p => p.Arch).ThenBy(p => p.Id).OfType<ArubaMachineConfig>();
                var baseline = context.Configs.OfType<ArubaMachineConfig>().OrderByDescending(p => p.Arch).ThenBy(p => p.Id);
                Assert.Equal(baseline.ToString(), query.ToString());

                var results = query.ToList();
                var expected = context.Configs.ToList().OrderByDescending(p => p.Arch).ThenBy(p => p.Id).OfType<ArubaMachineConfig>().ToList();
                QueryTestHelpers.VerifyQueryResult(expected, results, (o, i) => o.Id == i.Id);
            }
        }

        public void OrderBy_ThenBy_Skip_lifted_above_projection()
        {
            using (var context = new ArubaContext())
            {
                var query = context.Owners.OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5).Select(p => new { p.FirstName, p.Id, Foo = p.Id * 5 });
                var baseline = context.Owners.Select(p => new { p.FirstName, p.Id, Foo = p.Id * 5 }).OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5);
                Assert.Equal(baseline.ToString(), query.ToString());

                var results = query.ToList();
                var expected = context.Owners.ToList().OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Skip(5).Select(p => new { p.FirstName, p.Id, Foo = p.Id * 5 }).ToList();
                QueryTestHelpers.VerifyQueryResult(expected, results, (o, i) => o.Id == i.Id);
            }
        }

 public void As_on_enum_not_supported()
 {
     using (var context = new ArubaContext())
     {
         Assert.Throws<NotSupportedException>(
             () => context.AllTypes.Select(a => a.c33_enum as ArubaEnum?).ToList()).ValidateMessage(
             typeof(DbContext).Assembly(),
             "ELinq_UnsupportedIsOrAs",
             null, 
             "TypeAs", 
             "System.Data.Entity.TestModels.ArubaModel.ArubaEnum",
             "System.Nullable`1[[System.Data.Entity.TestModels.ArubaModel.ArubaEnum, EntityFramework.FunctionalTests, Version=0.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a]]");
     }
 }

        public void OrderBy_ThenBy_lifted_above_filter_without_clr_null_semantics()
        {
            using (var context = new ArubaContext())
            {
                ((IObjectContextAdapter)context).ObjectContext.ContextOptions.UseCSharpNullComparisonBehavior = false;

                var query = context.Owners.OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Where(p => p.Id % 2 == 0);
                var baseline = context.Owners.Where(p => p.Id % 2 == 0).OrderByDescending(p => p.FirstName).ThenBy(p => p.Id);
                Assert.Equal(baseline.ToString(), query.ToString());

                var results = query.ToList();
                var expected = context.Owners.ToList().OrderByDescending(p => p.FirstName).ThenBy(p => p.Id).Where(p => p.Id % 2 == 0).ToList();
                QueryTestHelpers.VerifyQueryResult(expected, results, (o, i) => o.Id == i.Id);
            }
        }

        public void GeographyDistanceFromMultiPolygon()
        {
            var query = @"
select Edm.Distance(g.c31_geography,
    CAST(Edm.GeographyFromText(""MULTIPOLYGON (((-136.34518219919187 -45.444057174306, 100.08107983924452 0.029396673640468753, -12.771444237628261 0.029396673640468753, -136.34518219919187 -45.444057174306), (-136.34518219919187 -59.574853846584816, -6.6232329320655019 -12.718185214660565, 93.93286853368177 -12.718185214660565, -136.34518219919187 -59.574853846584816)))"", 4326) AS Edm.Geography))
from ArubaContext.AllTypes as g 
where (Edm.Distance(g.c31_geography,
    CAST(Edm.GeographyFromText(""MULTIPOLYGON (((-136.34518219919187 -45.444057174306, 100.08107983924452 0.029396673640468753, -12.771444237628261 0.029396673640468753, -136.34518219919187 -45.444057174306), (-136.34518219919187 -59.574853846584816, -6.6232329320655019 -12.718185214660565, 93.93286853368177 -12.718185214660565, -136.34518219919187 -59.574853846584816)))"", 4326) AS Edm.Geography)) <= 600000)";

            // verifying that all of the results are less than or equal to 600000 as specified in the query
            using (var db = new ArubaContext())
            using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, "STDistance"))
            {
                VerifyValueCondition(reader, a => (double)a <= 600000);
            }
        }

        public void GroupBy_is_optimized_when_projecting_expression_containing_group_key()
        {
            var expectedSql =
@"SELECT 
[Extent1].[Id] * 2 AS [C1]
FROM [dbo].[ArubaOwners] AS [Extent1]";

            using (var context = new ArubaContext())
            {
                var query = context.Owners.GroupBy(o => o.Id).Select(g => g.Key * 2);
                QueryTestHelpers.VerifyDbQuery(query, expectedSql);

                var results = query.ToList();
                var expected = context.Owners.ToList().GroupBy(o => o.Id).Select(g => g.Key * 2).ToList();
                QueryTestHelpers.VerifyQueryResult(expected, results, (o, i) => o == i);
            }
        }

 public void Include_on_many_to_many_relationship()
 {
     using (var context = new ArubaContext())
     {
         ((IObjectContextAdapter)context).ObjectContext.ContextOptions.LazyLoadingEnabled = false;
         var query = context.Configs.Include(c => c.Failures);
         var results = query.ToList();
         var failuresForConfigs = context.Configs.Select(r => r.Failures).ToList();
         for (var i = 0; i < results.Count; i++)
         {
             Assert.Equal(failuresForConfigs[i].Count, results[i].Failures.Count);
             var expectedFailures = failuresForConfigs[i].Select(t => t.Id).ToList();
             var actualFailures = results[i].Failures.Select(t => t.Id).ToList();
             Assert.True(Enumerable.SequenceEqual(expectedFailures, actualFailures));
         }
     }
 }