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