Question

Example contains 3 collections. Collection with name 'badResult' contains 1 element. It is incorrectly. Correct result is 3.

Example:

using System; using System.Linq;

using Xtensive.Orm;
using Xtensive.Orm.Configuration;

public static class Program
{
    private static void Main(string[] args)
    {
        var dc = new DomainConfiguration("sqlserver://localhost/DO40-Tests");
        dc.Types.Register(typeof(Employee));
        dc.Types.Register(typeof(EmployeeWithCar));
        dc.Types.Register(typeof(Car));

        dc.UpgradeMode = DomainUpgradeMode.Recreate;
        var sessionConfiguration = new SessionConfiguration(SessionOptions.AutoActivation | SessionOptions.ServerProfile);

        using (var domain = Domain.Build(dc))
        {
            using (var session = domain.OpenSession(sessionConfiguration))
            using (session.Activate())
            using (var t = session.OpenTransaction())
            {
                var car = new Car();

                new EmployeeWithCar { Car = car };
                new Employee();
                new Employee();

                t.Complete();
            }

            using (var session = domain.OpenSession(sessionConfiguration))
            using (var t = session.OpenTransaction())
            {
                var q = Session.Current.Query;

                var badResult = q.All<Employee>()
                    .LeftJoin(
                        q.All<EmployeeWithCar>(),
                        e => e.Id,
                        ewc => ewc.Id,
                        (e, ewc) => new
                        {
                            e.Id,
                            ewc.Car
                        });

                var goodResult = q.All<Employee>()
                    .LeftJoin(
                        q.All<EmployeeWithCar>(),
                        e => e.Id,
                        ewc => ewc.Id,
                        (e, ewc) => new
                        {
                            e.Id,
                            Car = ewc.Car.Id
                        });

                var wordaround = q.All<Employee>()
                    .LeftJoin(
                        q.All<EmployeeWithCar>(),
                        e => e.Id,
                        ewc => ewc.Id,
                        (e, ewc) => new
                        {
                            e.Id,
                            CarId = ewc.Car.Id
                        })
                        .LeftJoin(
                            q.All<Car>(),
                            e => e.CarId,
                            c => c.Id,
                            (e, c) => new
                            {
                                e.Id,
                                Car = c
                            });

                Console.WriteLine("Bad result = '{0}',  goodResult = '{1}', workaround = '{2}'", badResult.Count(), goodResult.Count(), wordaround.Count());
                //Bad result = '1',  goodResult = '3', workaround = '3'

                Console.ReadLine();
            }
        }
    }
}

[Serializable]
[HierarchyRoot]
public class Employee : Entity
{
    [Key]
    [Field(Nullable = false)]
    public Guid Id { get; private set; }
}

[Serializable]
public class EmployeeWithCar : Employee
{
    [Field(Nullable = false)]
    public Car Car { get; set; }
}

[Serializable]
[HierarchyRoot]
public class Car : Entity
{
    [Key]
    [Field(Nullable = false)]
    public Guid Id { get; private set; }
}

Answer 1

Hello A Volkov.

I'll try to explain what happening in this three cases.

Good Work case: In this case ewc.Car.Id it just a column and your query translates to pure left join statement. Query translates to


SELECT COUNT_BIG(*) AS [c01umn] FROM [dbo].[Employee] [a] LEFT OUTER JOIN [dbo].[EmployeeWithCar] [b] ON ([a].[Id] = [b].[Id]);

So you can receive null in field Car = ewc.Car.Id, And DO just take it and do nothing with it because it understand that is nature of left join.

Workaround case: Query translates to


SELECT COUNT_BIG(*) AS [c01umn] FROM [dbo].[Employee] [a] LEFT OUTER JOIN [dbo].[EmployeeWithCar] [b] ON ([a].[Id] = [b].[Id]) LEFT OUTER JOIN [dbo].[Car] [c] ON ([b].[Car.Id] = [c].[Id]);

In this case you select CarId = ewc.Car.Id in first left join (see Good Work case) and put result to next left join. CarId field is nullable field. If CarId is null then Car in second Left join is null. As in previous case DO does nothing because it understand that is left join and fields can be null.

Bad Work Case: Query translates to


SELECT COUNT_BIG(*) as [c01umn] FROM [dbo].[Employee] [a] LEFT OUTER JOIN [dbo].[EmployeeWithCar] [b] ON ([a].[Id] = [b].[Id]) INNER JOIN [dbo].[Car] [c] ON ([b].[Car.Id] = [c].[Id]);

In this case you select Car= ewc.Car.This is not simple column, this is row in different table and DO must create another join. Besides, this field marked by [Field(Nullable = false)] which tells DO that this field must be not null. And DO try to guarantee that all rows in result of query contain not null Car object. So we have one row in result of query execution.

So if you need to select all three rows than you need manually write second left join as in workaround case.

Answer 2

I think this is very similar to navigating from Base entity with first nullable=true, and after nullable=false field.

for example, if you add class

[HierarchyRoot]
public class SomeEntity : Entity
{
    [Key]
    [Field(Nullable = false)]
    public Guid Id { get; private set; }

    [Field(Nullable = true)]
    public EmployeeWithCar EWC { get; set; }
}

and query:

q.All<SomeEntity>().Select(a => new { a.Id, a.EWC.Car }).Count();

it produces SQL:

SELECT  COUNT_BIG(*) AS [c01umn]
FROM    [dbo].[SomeEntity] [a]
        LEFT OUTER JOIN [dbo].[EmployeeWithCar] [b] ON ( [a].[EWC.Id] = [b].[Id] )
        LEFT OUTER JOIN [dbo].[Car] [c] ON ( [b].[Car.Id] = [c].[Id] );

Subscription:

Related questions