DO 5.0.17

In some conditions (last query in example) DO add INNER JOIN on nullable field And if field value = null, row excluded from results

Example and SQL queries for every Count()

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(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())
            {
                var testEntity3 = new TestEntity3(s) { Name = "test" };

                var testEntity2 = new TestEntity2(s) { Name = "test", Link = testEntity3 };

                new TestEntity(s) { Link = testEntity2 };
                new TestEntity(s);

                t.Complete();
            }

            using (var s = d.OpenSession())
            using (s.Activate())
            using (s.OpenTransaction())
            {
                var item = Query.All<TestEntity3>().Single();

                var singleWhere = Query.All<TestEntity>()
                    .Where(it => true || it.Link.Link == item)
                    .Select(e => new { e.Id, Test = e.Link.Link.Name })
                    .Count();

                Assert.AreEqual(2, singleWhere);

                var doubleWhere = Query.All<TestEntity>()
                    .Where(it => true || it.Link.Link == item)
                    .Where(it => true || it.Link.Link == item)
                    .Select(e => new { e.Id, Test = e.Link.Link.Name })
                    .Count();

                Assert.AreEqual(2, doubleWhere);

                var singleWhereAndQuery = Query.All<TestEntity>()
                    .Where(it => true || it.Link.Link == item)
                    .Select(e => new
                    {
                        e.Id,
                        Test = Query.All<TestEntity4>()
                                   .SingleOrDefault(it => it.Name == e.Link.Link.Name)
                                   .Description
                               ?? e.Link.Link.Name
                    })
                    .Count();

                Assert.AreEqual(2, singleWhereAndQuery);

                var doubleWhereAndQuery = Query.All<TestEntity>()
                    .Where(it => true || it.Link.Link == item)
                    .Where(it => true || it.Link.Link == item)
                    .Select(e => new
                    {
                        e.Id,
                        Test = Query.All<TestEntity4>()
                                   .SingleOrDefault(it => it.Name == e.Link.Link.Name)
                                   .Description
                               ?? e.Link.Link.Name
                    })
                    .Count();

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

    [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; }
    }

    [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>
        /// Name
        /// </summary>
        [Field(Nullable = false)]
        public string Name { get; set; }

        /// <summary>
        /// Name
        /// </summary>
        [Field(Nullable = false)]
        public string Description { get; set; }
    }
}

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])
WHERE
  (
    (cast(1 as bit) <> 0)
    OR ([b].[Link.Id] = @p0_0)
  );

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])
WHERE
  (
    (
      (cast(1 as bit) <> 0)
      OR ([b].[Link.Id] = @p0_0)
    )
    AND (
      (cast(1 as bit) <> 0)
      OR ([b].[Link.Id] = @p0_1)
    )
  );

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.TestEntity3] [d] ON ([b].[Link.Id] = [d].[Id])
WHERE
  (
    (cast(1 as bit) <> 0)
    OR ([b].[Link.Id] = @p0_0)
  );

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])
WHERE
  (
    (
      (cast(1 as bit) <> 0)
      OR ([b].[Link.Id] = @p0_0)
    )
    AND (
      (cast(1 as bit) <> 0)
      OR ([b].[Link.Id] = @p0_1)
    )
  );

asked Jun 06 '19 at 06:29

Gushchin%20Anton's gravatar image

Gushchin Anton
11272729


One Answer:

Hello Anton,

We've fixed it in developing branch so it will be in upcoming version

answered Jul 22 '19 at 23:41

Alexey%20Kulakov's gravatar image

Alexey Kulakov
77225

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×42
×31

Asked: Jun 06 '19 at 06:29

Seen: 2,442 times

Last updated: Jul 22 '19 at 23:41

powered by OSQA