public void Overload_resolution_for_function_taking_entity_reference_as_argument()
{
var query1 = "ProductModel.F_In_Ref(REF(anyelement(ProductContainer.Products)))";
var expectedSql1 = "SELECT 'Ref(Product)' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]";
QueryTestHelpers.VerifyQuery(query1, workspace, expectedSql1);
var query2 =
"ProductModel.F_In_Ref(REF(anyelement(select value treat(p as ProductModel.DiscontinuedProduct) from ProductContainer.Products as p)))";
var expectedSql2 = "SELECT 'Ref(DiscontinuedProduct)' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]";
QueryTestHelpers.VerifyQuery(query2, workspace, expectedSql2);
var query3 =
"ProductModel.F_In_Ref2(REF(anyelement(select value treat(p as ProductModel.DiscontinuedProduct) from ProductContainer.Products as p)))";
var expectedSql3 = "SELECT 'Ref(Product)' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]";
QueryTestHelpers.VerifyQuery(query3, workspace, expectedSql3);
}
public void Joins_are_eliminated_and_expression_is_simplified()
{
using (var context = new MyContext())
{
var query = context.Bases.OfType <MyDerived2>().Where(e => e.Derived1.Derived2.Name == "Foo");
QueryTestHelpers.VerifyQuery(
query,
@"SELECT
[Extent1].[Id] AS [Id],
'0X0X' AS [C1],
[Extent1].[Name] AS [Name],
CAST(NULL AS varchar(1)) AS [C2],
[Extent1].[Derived1_Id] AS [Derived1_Id]
FROM [dbo].[Derived2] AS [Extent1]
INNER JOIN [dbo].[Derived2] AS [Extent2] ON ([Extent2].[Derived1_Id] = [Extent1].[Derived1_Id]) AND ([Extent1].[Derived1_Id] IS NOT NULL)
WHERE ([Extent2].[Derived1_Id] IS NOT NULL) AND (N'Foo' = [Extent2].[Name])");
}
}
public void Unnecessary_joins_are_eliminated_test2()
{
using (var ctx = new Entities())
{
var query
= ctx.QueryTestEntities
.OrderBy(x => x.Id)
.Select(
x => new
{
// QueryTestEntity
x.QueryData,
// TestEntity
x.TestEntity.Code,
x.TestEntity.Name,
x.TestEntity.Description,
// TestProperty
x.TestEntity.TestProperty.PropertyCode,
x.TestEntity.TestProperty.PropertyName,
x.TestEntity.TestProperty.PropertyDescription
})
.Take(10);
QueryTestHelpers.VerifyQuery(
query,
@"SELECT TOP (10)
[Extent1].[Id] AS [Id],
[Extent1].[QueryData] AS [QueryData],
[Extent2].[Code] AS [Code],
[Extent2].[Name] AS [Name],
[Extent2].[Description] AS [Description],
[Join2].[PropertyCode] AS [PropertyCode],
[Join2].[PropertyName] AS [PropertyName],
[Join2].[PropertyDescription] AS [PropertyDescription]
FROM [dbo].[QueryTestEntities] AS [Extent1]
INNER JOIN [dbo].[TestEntities] AS [Extent2] ON [Extent1].[TestEntityId] = [Extent2].[Id]
LEFT OUTER JOIN (SELECT [Extent3].[PropertyCode] AS [PropertyCode], [Extent3].[PropertyName] AS [PropertyName], [Extent3].[PropertyDescription] AS [PropertyDescription], [Extent4].[Id] AS [Id1]
FROM [dbo].[TestProperties] AS [Extent3]
LEFT OUTER JOIN [dbo].[TestEntities] AS [Extent4] ON [Extent3].[Id] = [Extent4].[Id] ) AS [Join2] ON [Join2].[Id1] = [Extent1].[TestEntityId]
ORDER BY [Extent1].[Id] ASC");
}
}
public void Joins_are_eliminated_and_expression_is_simplified()
{
using (var context = new MyContext())
{
var query = context.As.Include(a => a.B.C.D.E);
QueryTestHelpers.VerifyQuery(
query,
@"SELECT
[Extent1].[Id] AS [Id],
[Extent2].[Id] AS [Id1],
[Extent3].[Id] AS [Id2],
[Extent4].[Id] AS [Id3],
[Extent4].[E_Id] AS [E_Id]
FROM [dbo].[A] AS [Extent1]
INNER JOIN [dbo].[B] AS [Extent2] ON [Extent1].[B_Id] = [Extent2].[Id]
INNER JOIN [dbo].[C] AS [Extent3] ON [Extent2].[C_Id] = [Extent3].[Id]
INNER JOIN [dbo].[D] AS [Extent4] ON [Extent3].[D_Id] = [Extent4].[Id]");
}
}
public static void Making_use_of_variable_multiple_times_doesnt_cause_redundant_joins()
{
const string expectedSqlProducts =
@"SELECT
[Extent1].[Name] AS [Name]
FROM [dbo].[Products] AS [Extent1]
LEFT OUTER JOIN [dbo].[ProductModels] AS [Extent2] ON [Extent1].[ProductModelID] = [Extent2].[ProductModelID]
WHERE ([Extent2].[ModifiedDate] >= @p__linq__0) AND ([Extent2].[ModifiedDate] <= @p__linq__1)";
const string expectedSqlStrings =
@"SELECT
[Extent1].[Name] AS [Name]
FROM [dbo].[Strings] AS [Extent1]
INNER JOIN [dbo].[StringInstruments] AS [Extent2] ON [Extent1].[StringInstrumentId] = [Extent2].[StringInstrumentId]
WHERE ([Extent2].[ProductionDate] >= @p__linq__0) AND ([Extent2].[ProductionDate] <= @p__linq__1)";
Database.SetInitializer <Codeplex199Context>(null);
using (var context = new Codeplex199Context())
{
context.Configuration.UseDatabaseNullSemantics = true;
context.Configuration.LazyLoadingEnabled = false;
var MinDate = new DateTime(2011, 02, 03);
var MaxDate = new DateTime(2011, 03, 04);
var query = context.Products
.Where(p => p.ProductModel.ModifiedDate >= MinDate &&
p.ProductModel.ModifiedDate <= MaxDate)
.Select(p => p.Name);
QueryTestHelpers.VerifyQuery(query, expectedSqlProducts);
query = context.Strings
.Where(s => s.StringInstrument.ProductionDate >= MinDate &&
s.StringInstrument.ProductionDate <= MaxDate)
.Select(s => s.Name);
QueryTestHelpers.VerifyQuery(query, expectedSqlStrings);
}
}
public void Inline_function_MaxInt()
{
var query =
@"Function MaxInt(i Collection(Int32)) as (max(i))
select gkey, MaxInt(groupPartition(a))
FROM {1,1,2,2,2} as a
Group By a as gkey";
var expectedSql =
@"SELECT
[GroupBy1].[K1] AS [C1],
[GroupBy1].[A1] AS [C2]
FROM ( SELECT
[UnionAll4].[C1] AS [K1],
MAX([UnionAll4].[C1]) AS [A1]
FROM (SELECT
1 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
UNION ALL
SELECT
1 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable2]
UNION ALL
SELECT
2 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable3]
UNION ALL
SELECT
2 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable4]
UNION ALL
SELECT
2 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable5]) AS [UnionAll4]
GROUP BY [UnionAll4].[C1]
) AS [GroupBy1]";
QueryTestHelpers.VerifyQuery(query, _workspace, expectedSql);
}
public void Generated_SQL_for_query_with_sort_in_virtual_extent_honors_OrderBy()
{
using (var ctx = new MyContext())
{
var query
= from u in ctx.Users
where u.Id == 4817
let loggedActions
= u.LoggedActions
.OrderByDescending(o => o.CreatedOn)
.Where(w => w.TypeReference == "Login")
select new
{
Name = u.FirstName + " " + u.LastName,
loggedActions.FirstOrDefault().CreatedOn,
};
QueryTestHelpers.VerifyQuery(
query,
@"SELECT
[Project2].[Id] AS [Id],
CASE WHEN ([Project2].[FirstName] IS NULL) THEN N'' ELSE [Project2].[FirstName] END + N' ' + CASE WHEN ([Project2].[LastName] IS NULL) THEN N'' ELSE [Project2].[LastName] END AS [C1],
[Project2].[C1] AS [C2]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
(SELECT TOP (1) [Project1].[CreatedOn] AS [CreatedOn]
FROM ( SELECT
[Extent2].[CreatedOn] AS [CreatedOn]
FROM [dbo].[LoggedActions] AS [Extent2]
WHERE ([Extent1].[Id] = [Extent2].[User_Id]) AND (N'Login' = [Extent2].[TypeReference])
) AS [Project1]
ORDER BY [Project1].[CreatedOn] DESC) AS [C1]
FROM [dbo].[Users] AS [Extent1]
WHERE 4817 = [Extent1].[Id]
) AS [Project2]");
}
}
[Fact] // Codeplex #655
public static void LeftOuterJoin_duplicates_are_eliminated()
{
const string expectedSql =
@"SELECT
[Extent1].[DocumentDetailId] AS [DocumentDetailId],
[Extent1].[DocumentId] AS [DocumentId]
FROM [dbo].[DocumentDetails] AS [Extent1]
LEFT OUTER JOIN [dbo].[Documents] AS [Extent2] ON [Extent1].[DocumentId] = [Extent2].[DocumentId]
LEFT OUTER JOIN [dbo].[Customers] AS [Extent3] ON [Extent2].[CustomerId] = [Extent3].[CustomerId]
WHERE [Extent3].[PersonId] IN (1,2)";
Database.SetInitializer <Codeplex655Context>(null);
using (var context = new Codeplex655Context())
{
context.Configuration.UseDatabaseNullSemantics = true;
var query = context.DocumentDetails.Where(x => x.Document.Customer.PersonId == 1 || x.Document.Customer.PersonId == 2);
QueryTestHelpers.VerifyQuery(query, expectedSql);
}
}
[Fact] // Codeplex #960
public static void LeftOuterJoin_is_not_turned_into_inner_join_if_nullable_foreign_key()
{
const string expectedSql =
@"SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Text] AS [Text],
[Extent3].[Name] AS [Name]
FROM [dbo].[Posts] AS [Extent1]
LEFT OUTER JOIN [dbo].[Posts] AS [Extent2] ON [Extent1].[ParentPostId] = [Extent2].[Id]
LEFT OUTER JOIN [dbo].[Blogs] AS [Extent3] ON [Extent2].[BlogId] = [Extent3].[Id]
WHERE 1 = [Extent1].[Id]";
Database.SetInitializer <Codeplex960Context>(null);
using (var context = new Codeplex960Context())
{
context.Configuration.UseDatabaseNullSemantics = true;
var query = from p in context.Posts where p.Id == 1 select new { p.Id, p.Text, p.ParentPost.Blog.Name };
QueryTestHelpers.VerifyQuery(query, expectedSql);
}
}
public void Unnecessary_joins_are_eliminated_test1()
{
using (var ctx = new Entities())
{
var query
= ctx.QueryDerivedEntities
.OrderBy(x => x.Id)
.Select(x => new
{
// QueryDerivedEntity
x.QueryData,
// DerivedEntityA
CodeA = x.DerivedEntityA.Code,
NameA = x.DerivedEntityA.Name,
DescriptionA = x.DerivedEntityA.Description,
// DerivedEntityB
CodeB = x.DerivedEntityB == null ? null : x.DerivedEntityB.Code,
NameB = x.DerivedEntityB == null ? null : x.DerivedEntityB.Name,
DescriptionB = x.DerivedEntityB == null ? null : x.DerivedEntityB.Description,
// DerivedEntityC
CodeC = x.DerivedEntityC.Code,
NameC = x.DerivedEntityC.Name,
DescriptionC = x.DerivedEntityC.Description,
AdditionalDataC = x.DerivedEntityC == null ? null : x.DerivedEntityC.AdditionalData,
// DerivedEntityD
CodeD = x.DerivedEntityD == null ? null : x.DerivedEntityD.Code,
NameD = x.DerivedEntityD == null ? null : x.DerivedEntityD.Name,
DescriptionD = x.DerivedEntityD == null ? null : x.DerivedEntityD.Description,
AdditionalDataD = x.DerivedEntityD == null ? null : x.DerivedEntityD.AdditionalData
})
.Take(10);
QueryTestHelpers.VerifyQuery(
query,
@"SELECT TOP (10)
[Project1].[Id] AS [Id],
[Project1].[QueryData] AS [QueryData],
[Project1].[Code] AS [Code],
[Project1].[Name] AS [Name],
[Project1].[Description] AS [Description],
[Project1].[C1] AS [C1],
[Project1].[C2] AS [C2],
[Project1].[C3] AS [C3],
[Project1].[Code1] AS [Code1],
[Project1].[Name1] AS [Name1],
[Project1].[Description1] AS [Description1],
[Project1].[C4] AS [C4],
[Project1].[C5] AS [C5],
[Project1].[C6] AS [C6],
[Project1].[C7] AS [C7],
[Project1].[C8] AS [C8]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[QueryData] AS [QueryData],
[Extent2].[Code] AS [Code],
[Extent2].[Name] AS [Name],
[Extent2].[Description] AS [Description],
[Join3].[Code] AS [Code1],
[Join3].[Name] AS [Name1],
[Join3].[Description] AS [Description1],
CASE WHEN ([Extent3].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Extent3].[Code] END AS [C1],
CASE WHEN ([Extent3].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Extent3].[Name] END AS [C2],
CASE WHEN ([Extent3].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Extent3].[Description] END AS [C3],
CASE WHEN ([Join3].[Id1] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Join3].[AdditionalData] END AS [C4],
CASE WHEN ([Join5].[Id2] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Join5].[Code] END AS [C5],
CASE WHEN ([Join5].[Id2] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Join5].[Name] END AS [C6],
CASE WHEN ([Join5].[Id2] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Join5].[Description] END AS [C7],
CASE WHEN ([Join5].[Id2] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE [Join5].[AdditionalData] END AS [C8]
FROM [dbo].[QueryDerivedEntities] AS [Extent1]
LEFT OUTER JOIN [dbo].[BaseEntities] AS [Extent2] ON ([Extent2].[Discriminator] = N'DerivedEntityA') AND ([Extent1].[DerivedEntityAId] = [Extent2].[Id])
LEFT OUTER JOIN [dbo].[BaseEntities] AS [Extent3] ON ([Extent3].[Discriminator] = N'DerivedEntityB') AND ([Extent1].[DerivedEntityBId] = [Extent3].[Id])
LEFT OUTER JOIN (SELECT [Extent4].[Id] AS [Id1], [Extent4].[AdditionalData] AS [AdditionalData], [Extent5].[Code] AS [Code], [Extent5].[Name] AS [Name], [Extent5].[Description] AS [Description], [Extent5].[Discriminator] AS [Discriminator]
FROM [dbo].[AbstractEntities] AS [Extent4]
INNER JOIN [dbo].[BaseEntities] AS [Extent5] ON [Extent4].[Id] = [Extent5].[Id] ) AS [Join3] ON ([Join3].[Discriminator] = N'DerivedEntityC') AND ([Extent1].[DerivedEntityCId] = [Join3].[Id1])
LEFT OUTER JOIN (SELECT [Extent6].[Id] AS [Id2], [Extent6].[AdditionalData] AS [AdditionalData], [Extent7].[Code] AS [Code], [Extent7].[Name] AS [Name], [Extent7].[Description] AS [Description], [Extent7].[Discriminator] AS [Discriminator]
FROM [dbo].[AbstractEntities] AS [Extent6]
INNER JOIN [dbo].[BaseEntities] AS [Extent7] ON [Extent6].[Id] = [Extent7].[Id] ) AS [Join5] ON ([Join5].[Discriminator] = N'DerivedEntityD') AND ([Extent1].[DerivedEntityDId] = [Join5].[Id2])
) AS [Project1]
ORDER BY [Project1].[Id] ASC");
}
}
