dataobjects-net icon indicating copy to clipboard operation
dataobjects-net copied to clipboard

Wrong SQL translation, result rows missing (INNER JOIN instead of LEFT JOIN)

Open letarak opened this issue 2 years ago • 0 comments

Version 7.0.3 Similar to https://support.x-tensive.com/question/6718/wrong-sql-translation If query try select required link from entity, then INNER JOIN applied But if entity was got by FirstOrDefault inside query, then it is possible to get null and INNER JOIN excludes this rows from result

Example

using System.Data.SqlClient;
using NUnit.Framework;
using Xtensive.Orm;
using Xtensive.Orm.Configuration;

internal class Program
{
    private static void Main(string[] args)
    {
        var currentConnection =
            new SqlConnectionStringBuilder("Data Source=.; Initial Catalog=DO-Tests; Integrated Security=True;");

        var dc = new DomainConfiguration("sqlserver", currentConnection.ToString());

        dc.Types.Register(typeof(TestEntity));
        dc.Types.Register(typeof(TestEntity2));
        dc.Types.Register(typeof(TestEntity3));

        dc.UpgradeMode = DomainUpgradeMode.Recreate;

        using (var d = Domain.Build(dc))
        {
            using (var s = d.OpenSession())
            using (var t = s.OpenTransaction())
            {
                _ = new TestEntity(s) { Name = "1" };
                _ = new TestEntity(s) { Name = "2" };

                t.Complete();
            }

            using (var s = d.OpenSession())
            using (s.Activate())
            using (s.OpenTransaction())
            {
                var nameCount = Query.All<TestEntity>()
                    .Select(e => new
                    {
                        e.Id,
                        Name = Query.All<TestEntity2>()
                            .FirstOrDefault(it => it.Owner == e)
                            .Name
                    })
                    .Count();

                Assert.AreEqual(2, nameCount);
                
                var nullableCount = Query.All<TestEntity>()
                    .Select(e => new
                    {
                        e.Id,
                        NullableLink = Query.All<TestEntity2>()
                            .FirstOrDefault(it => it.Owner == e)
                            .NullableLink
                    })
                    .Count();

                Assert.AreEqual(2, nullableCount);

                var linkCount = Query.All<TestEntity>()
                    .Select(e => new
                    {
                        e.Id,
                        Link = Query.All<TestEntity2>()
                            .FirstOrDefault(it => it.Owner == e)
                            .Link
                    })
                    .Count();

                Assert.AreEqual(2, linkCount);
            }
        }
    }

    [HierarchyRoot]
    public class TestEntity : Entity
    {
        public TestEntity(Session session) : base(session)
        {
        }

        [Key] [Field(Nullable = false)] public int Id { get; set; }

        [Field(Nullable = false)] public string Name { get; set; }
    }

    [HierarchyRoot]
    public class TestEntity2 : Entity
    {
        public TestEntity2(Session session) : base(session)
        {
        }

        [Key] [Field(Nullable = false)] public int Id { get; set; }

        [Field(Nullable = false)] public string Name { get; set; }

        [Field(Nullable = false)] public TestEntity Owner { get; set; }

        [Field] public TestEntity3 NullableLink { get; set; }

        [Field(Nullable = false)] public TestEntity3 Link { get; set; }
    }

    [HierarchyRoot]
    public class TestEntity3 : Entity
    {
        public TestEntity3(Session session) : base(session)
        {
        }

        [Key] [Field(Nullable = false)] public int Id { get; set; }

        [Field(Nullable = false)] public string Name { get; set; }
    }
}

letarak avatar Oct 11 '22 06:10 letarak