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 Materializing_empty_list_throws()
{
using (var context = new ArubaContext())
{
var query = context.Runs.Select(r => new List<int> { });
Assert.Throws<NotSupportedException>(() => query.ToList()).
ValidateMessage(
typeof(DbContext).Assembly,
"ELinq_UnsupportedEnumerableType",
null,
"System.Collections.Generic.List`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]");
}
}
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 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 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 Can_materialize_list_inside_anonymous_type()
{
using (var context = new ArubaContext())
{
var query = context.Runs.Where(r => r.Id == 1).Select(r => new { r.Id, List = new List<string> { r.Name, "a", "b" } });
var results = query.ToList();
var runId = context.Runs.Select(r => r.Id).First(r => r == 1);
var runName = context.Runs.Where(r => r.Id == 1).Select(r => r.Name).First();
Assert.Equal(1, results.Count);
Assert.Equal(runId, results[0].Id);
Assert.Equal(3, results[0].List.Count);
Assert.Equal(runName, results[0].List[0]);
Assert.Equal("a", results[0].List[1]);
Assert.Equal("b", results[0].List[2]);
}
}
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));
}
}
}
public void Materialize_array_of_entity_properties_throws()
{
using (var context = new ArubaContext())
{
var query = context.Runs.Where(r => r.Id == 1).Select(r => new int[] { r.Id, r.RunOwner.Id });
var innerException = Assert.Throws<TargetInvocationException>(() => query.ToList())
.InnerException;
Assert.IsType<InvalidOperationException>(innerException);
innerException.ValidateMessage(
typeof(DbContext).Assembly,
"ObjectQuery_UnableToMaterializeArray",
null,
"System.Int32[]",
"System.Collections.Generic.List`1[System.Int32]");
}
}
public void Can_materialize_list_of_entity_properties()
{
using (var context = new ArubaContext())
{
var query = context.Runs.Where(r => r.Id == 1).Select(r => new List<int> { r.Id, r.RunOwner.Id });
// materializing results
var results = query.ToList();
var runId = context.Runs.Select(r => r.Id).First(r => r == 1);
var ownerId = context.Runs.Select(r => r.RunOwner.Id).First(r => r == 1);
Assert.Equal(1, results.Count);
Assert.Equal(2, results[0].Count);
Assert.Equal(runId, results[0][0]);
Assert.Equal(ownerId, results[0][1]);
}
}
public void GeometryDistanceFrom()
{
var query = @"
select Edm.Distance(g.c32_geometry,CAST(
Edm.GeometryFromText(""MULTIPOINT ((10 20), EMPTY)"", 32768)
AS Edm.Geometry))
from ArubaContext.AllTypes as g
where (Edm.Distance(g.c32_geometry,CAST(
Edm.GeometryFromText(""MULTIPOINT ((10 20), EMPTY)"", 32768)
AS Edm.Geometry)) <= 5)";
// verifying that all of the results returned are <= 5
using (var db = new ArubaContext())
using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, "STDistance"))
{
VerifyValueCondition(reader, a => (double) a <= 5);
}
}
public void Funcletize_ICollection_count_when_passed_as_parameter()
{
var localList = new List<int> { 1, 2, 3 };
using (var context = new ArubaContext())
{
var expectedSql =
@"SELECT
@p__linq__0 AS [C1]
FROM [dbo].[ArubaTasks] AS [Extent1]";
var query = context.Tasks.Select(t => localList.Count);
QueryTestHelpers.VerifyDbQuery(query, expectedSql);
// verify that correct value gets projected
var results = query.ToList();
Assert.True(results.All(r => r == 3));
}
}
public void Funcletize_byte_array()
{
using (var context = new ArubaContext())
{
var expectedSql =
@"SELECT
[Extent1].[Id] AS [Id]
FROM [dbo].[ArubaTasks] AS [Extent1]
WHERE 0x010203 = 0x010203";
var query = context.Tasks.Where(c => new byte[] { 1, 2, 3 } == new byte[] { 1, 2, 3 }).Select(c => c.Id);
QueryTestHelpers.VerifyDbQuery(query, expectedSql);
// verify that filter is a no-op
var results = query.ToList();
var expectedCount = context.Tasks.Count();
Assert.Equal(expectedCount, results.Count);
}
}
public void No_casts_when_comparing_short_enum_property_and_constant_with_equal_flipped()
{
using (var context = new ArubaContext())
{
context.Configuration.UseDatabaseNullSemantics = true;
const string expectedSql =
@"SELECT
[Extent1].[c38_shortenum] AS [c38_shortenum]
FROM [dbo].[ArubaAllTypes] AS [Extent1]
WHERE 2 = [Extent1].[c38_shortenum]";
var query = context.AllTypes.Where(a => ArubaShortEnum.ShortEnumValue2 == a.c38_shortenum).Select(a => a.c38_shortenum);
QueryTestHelpers.VerifyDbQuery(query, expectedSql);
Assert.True(query.ToList().All(r=> r == ArubaShortEnum.ShortEnumValue2));
}
}
public void Funcletize_decimal_constructors()
{
using (var context = new ArubaContext())
{
var expectedSql =
@"SELECT
1 AS [C1],
1.5 AS [C2],
2.5 AS [C3],
cast(5 as decimal(18)) AS [C4],
cast(7 as decimal(18)) AS [C5],
cast(9 as decimal(18)) AS [C6],
cast(11 as decimal(18)) AS [C7],
0.0013 AS [C8]
FROM [dbo].[ArubaTasks] AS [Extent1]";
var query = context.Tasks.Select(c => new
{
a = new Decimal(1.5),
b = new Decimal((float)2.5),
c = new Decimal(5),
e = new Decimal((long)7),
f = new Decimal((uint)9),
g = new Decimal((ulong)11),
h = new Decimal(13, 0, 0, false, 4),
});
QueryTestHelpers.VerifyDbQuery(query, expectedSql);
var results = query.ToList();
foreach (var result in results)
{
Assert.Equal((decimal)1.5, result.a);
Assert.Equal((decimal)2.5, result.b);
Assert.Equal(5, result.c);
Assert.Equal(7, result.e);
Assert.Equal(9, result.f);
Assert.Equal(11, result.g);
Assert.Equal((decimal)0.0013, result.h);
}
}
}
public void GroupBy_is_optimized_when_projecting_group_key()
{
var expectedSql =
@"SELECT
[Distinct1].[FirstName] AS [FirstName]
FROM ( SELECT DISTINCT
[Extent1].[FirstName] AS [FirstName]
FROM [dbo].[ArubaOwners] AS [Extent1]
) AS [Distinct1]";
using (var context = new ArubaContext())
{
var query = context.Owners.GroupBy(o => o.FirstName).Select(g => g.Key);
QueryTestHelpers.VerifyDbQuery(query, expectedSql);
var results = query.ToList();
var expected = context.Owners.ToList().GroupBy(o => o.FirstName).Select(g => g.Key).ToList();
QueryTestHelpers.VerifyQueryResult(expected, results, (o, i) => o == i);
}
}
private void InitializeAndPersistPeople(ArubaContext context)
{
var grandFather = new ArubaPerson
{
Name = "GrandFather",
Children = new List <ArubaPerson>(),
};
var mother = new ArubaPerson
{
Name = "Mother",
Children = new List <ArubaPerson>(),
Parents = new List <ArubaPerson> {
grandFather
},
};
grandFather.Children.Add(mother);
var father = new ArubaPerson
{
Name = "Father",
Children = new List <ArubaPerson>(),
};
mother.Partner = father;
var child = new ArubaPerson
{
Name = "Child",
Parents = new List <ArubaPerson> {
mother, father
}
};
mother.Children.Add(child);
father.Children.Add(child);
var childOfSingleMother = new ArubaPerson
{
Name = "Child",
};
var singleMother = new ArubaPerson
{
Name = "Single Mother",
Children = new List <ArubaPerson> {
childOfSingleMother
},
};
childOfSingleMother.Parents = new List <ArubaPerson> {
singleMother
};
var childOfDivorcedParents = new ArubaPerson
{
Name = "Child",
};
var divorcedFather = new ArubaPerson
{
Name = "Divorced Father",
Children = new List <ArubaPerson> {
childOfDivorcedParents
},
};
var divorcedMother = new ArubaPerson
{
Name = "Divorced Mother",
Children = new List <ArubaPerson> {
childOfDivorcedParents
},
};
childOfDivorcedParents.Parents = new List <ArubaPerson> {
divorcedFather, divorcedMother
};
var bachelor = new ArubaPerson
{
Name = "Bachelor",
};
context.People.Add(bachelor);
context.People.Add(divorcedFather);
context.People.Add(divorcedMother);
context.People.Add(singleMother);
context.People.Add(grandFather);
context.SaveChanges();
}
public void Seed(ArubaContext context)
{
var allTypes = InitializeAllTypes();
var bugs = InitializeBugs();
var configs = InitializeConfigs();
var failures = InitializeFailures();
var owners = InitializeOwners();
var runs = InitializeRuns();
var tasks = InitializeTasks();
InitializeAndPersistPeople(context);
for (var i = 0; i < EntitiesCount; i++)
{
bugs[i].Failure = failures[i % 4];
failures[i % 4].Bugs.Add(bugs[i]);
}
for (var i = 0; i < EntitiesCount; i++)
{
for (var j = 0; j < i % 3; j++)
{
configs[i].Failures.Add(failures[(i + j) % EntitiesCount]);
}
}
for (var i = 0; i < EntitiesCount; i++)
{
for (var j = 0; j < i % 2; j++)
{
failures[i].Configs.Add(configs[(i + j) % EntitiesCount]);
}
}
for (var i = 0; i < EntitiesCount; i++)
{
owners[i].OwnedRun = runs[i];
for (var j = 0; j < 5; j++)
{
owners[i].Bugs.Add(bugs[(i + j) % EntitiesCount]);
}
}
for (var i = 0; i < EntitiesCount; i++)
{
runs[i].RunOwner = owners[i];
for (var j = 0; j < 3; j++)
{
runs[i].Tasks.Add(tasks[(i + j) % EntitiesCount]);
}
}
for (int i = 0; i < EntitiesCount; i++)
{
context.AllTypes.Add(allTypes[i]);
context.Bugs.Add(bugs[i]);
context.Configs.Add(configs[i]);
context.Failures.Add(failures[i]);
context.Owners.Add(owners[i]);
context.Runs.Add(runs[i]);
context.Tasks.Add(tasks[i]);
}
context.SaveChanges();
}