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 Anton
11●27●27●29