DO 5.0.18
Similar to http://support.x-tensive.com/question/6718/wrong-sql-translation
But without any Where expression
using System.Linq;
using NUnit.Framework;
using Xtensive.Orm;
using Xtensive.Orm.Configuration;
internal class Program
{
private static void Main(string[] args)
{
var dc = new DomainConfiguration("sqlserver",
"Data Source=.; Initial Catalog=DO40-Tests; Integrated Security=True;");
dc.Types.Register(typeof(TestEntity));
dc.Types.Register(typeof(TestEntity2));
dc.Types.Register(typeof(TestEntity2Impl));
dc.Types.Register(typeof(TestEntity3));
dc.Types.Register(typeof(TestEntity4));
dc.UpgradeMode = DomainUpgradeMode.Recreate;
using (var d = Domain.Build(dc))
{
using (var s = d.OpenSession())
using (s.Activate())
using (var t = s.OpenTransaction())
{
new TestEntity(s);
new TestEntity(s);
t.Complete();
}
using (var s = d.OpenSession())
using (s.Activate())
using (s.OpenTransaction())
{
var select = Query.All<TestEntity>()
.Select(a => new
{
Test = Query.All<TestEntity4>()
.SingleOrDefault(it => it.Text1 == a.Link.Link.Name)
.Text2
?? a.Link.Link.Name
})
.Count();
Assert.AreEqual(2, select);
var selectWithOrderById = Query.All<TestEntity>()
.OrderBy(a => a.Link.Id)
.Select(a => new
{
Test = Query.All<TestEntity4>()
.SingleOrDefault(it => it.Text1 == a.Link.Link.Name)
.Text2
?? a.Link.Link.Name
})
.Count();
Assert.AreEqual(2, selectWithOrderById);
var selectWithOrderByNameAddCast = Query.All<TestEntity>()
.OrderBy(a => a.Link.Name)
.Select(a => new
{
Cast = (a.Link as TestEntity2Impl).Id,
Test = Query.All<TestEntity4>()
.SingleOrDefault(it => it.Text1 == a.Link.Link.Name)
.Text2
?? a.Link.Link.Name
})
.Count();
Assert.AreEqual(2, selectWithOrderByNameAddCast);
var selectWithOrderByName = Query.All<TestEntity>()
.OrderBy(a => a.Link.Name)
.Select(a => new
{
Test = Query.All<TestEntity4>()
.SingleOrDefault(it => it.Text1 == a.Link.Link.Name)
.Text2
?? a.Link.Link.Name
})
.Count();
Assert.AreEqual(2, selectWithOrderByName);
}
}
}
[HierarchyRoot]
public class TestEntity : Entity
{
/// <summary>Initializes a new instance of this class.</summary>
/// <param name="session">The session.</param>
public TestEntity(Session session) : base(session)
{
}
[Key]
[Field(Nullable = false)]
public int Id { get; set; }
/// <summary>
/// Name
/// </summary>
[Field]
public TestEntity2 Link { get; set; }
}
[HierarchyRoot]
public class TestEntity2 : Entity
{
/// <summary>Initializes a new instance of this class.</summary>
/// <param name="session">The session.</param>
public TestEntity2(Session session) : base(session)
{
}
[Key]
[Field(Nullable = false)]
public int Id { get; set; }
/// <summary>
/// Name
/// </summary>
[Field(Nullable = false)]
public string Name { get; set; }
/// <summary>
/// Link
/// </summary>
[Field(Nullable = false)]
public TestEntity3 Link { get; set; }
}
[HierarchyRoot]
public class TestEntity3 : Entity
{
/// <summary>Initializes a new instance of this class.</summary>
/// <param name="session">The session.</param>
public TestEntity3(Session session) : base(session)
{
}
[Key]
[Field(Nullable = false)]
public int Id { get; set; }
/// <summary>
/// Name
/// </summary>
[Field(Nullable = false)]
public string Name { get; set; }
}
public class TestEntity2Impl : TestEntity2
{
/// <summary>Initializes a new instance of this class.</summary>
/// <param name="session">The session.</param>
public TestEntity2Impl(Session session) : base(session)
{
}
/// <summary>
/// Name
/// </summary>
[Field(Nullable = false)]
public string Name2 { get; set; }
}
[HierarchyRoot]
public class TestEntity4 : Entity
{
/// <summary>Initializes a new instance of this class.</summary>
/// <param name="session">The session.</param>
public TestEntity4(Session session) : base(session)
{
}
[Key]
[Field(Nullable = false)]
public int Id { get; set; }
/// <summary>
/// Text1
/// </summary>
[Field(Nullable = false)]
public string Text1 { get; set; }
/// <summary>
/// Text2
/// </summary>
[Field(Nullable = false)]
public string Text2 { get; set; }
}
}
SQL queries
SELECT COUNT_BIG(*) AS [c01umn] FROM [dbo].[Program.TestEntity] [a] LEFT OUTER JOIN [dbo].[Program.TestEntity2] [b] ON ([a].[Link.Id] = [b].[Id]) LEFT OUTER JOIN [dbo].[Program.TestEntity3] [c] ON ([b].[Link.Id] = [c].[Id]) LEFT OUTER JOIN [dbo].[Program.TestEntity2] [d] ON ([a].[Link.Id] = [d].[Id]) LEFT OUTER JOIN [dbo].[Program.TestEntity3] [e] ON ([d].[Link.Id] = [e].[Id]);
go
SELECT COUNT_BIG(*) AS [c01umn] FROM [dbo].[Program.TestEntity] [a] LEFT OUTER JOIN [dbo].[Program.TestEntity2] [b] ON ([a].[Link.Id] = [b].[Id]) LEFT OUTER JOIN [dbo].[Program.TestEntity3] [c] ON ([b].[Link.Id] = [c].[Id]) LEFT OUTER JOIN [dbo].[Program.TestEntity2] [d] ON ([a].[Link.Id] = [d].[Id]) LEFT OUTER JOIN [dbo].[Program.TestEntity3] [e] ON ([d].[Link.Id] = [e].[Id]);
go
SELECT COUNT_BIG(*) AS [c01umn] FROM [dbo].[Program.TestEntity] [a] LEFT OUTER JOIN [dbo].[Program.TestEntity2] [b] ON ([a].[Link.Id] = [b].[Id]) LEFT OUTER JOIN [dbo].[Program.TestEntity2Impl] [c] INNER JOIN [dbo].[Program.TestEntity2] [d] ON ([d].[Id] = [c].[Id]) ON ([a].[Link.Id] = [d].[Id]) LEFT OUTER JOIN [dbo].[Program.TestEntity3] [e] ON ([b].[Link.Id] = [e].[Id]) LEFT OUTER JOIN [dbo].[Program.TestEntity3] [f] ON ([b].[Link.Id] = [f].[Id]);
go
SELECT COUNT_BIG(*) AS [c01umn] FROM [dbo].[Program.TestEntity] [a] LEFT OUTER JOIN [dbo].[Program.TestEntity2] [b] ON ([a].[Link.Id] = [b].[Id]) INNER JOIN [dbo].[Program.TestEntity3] [c] ON ([b].[Link.Id] = [c].[Id]) LEFT OUTER JOIN [dbo].[Program.TestEntity3] [d] ON ([b].[Link.Id] = [d].[Id]);
go
asked
Oct 03 '19 at 01:52
Gushchin Anton
11●27●27●29