dataobjects-net
dataobjects-net copied to clipboard
Wrong SQL translation, result rows missing (INNER JOIN instead of LEFT JOIN)
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; }
}
}