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

asked Sep 03 '14 at 09:26

A%20Volkov's gravatar image

A Volkov
17224


2 Answers:

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.

answered Sep 04 '14 at 04:10

Alexey%20Kulakov's gravatar image

Alexey Kulakov
63215

We decided that it is truly incorrect behavior.And we will fix it.

Thanks you, A Volkov, for your report.

(Sep 08 '14 at 02:36) Alexey Kulakov Alexey%20Kulakov's gravatar image

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] );

answered Sep 04 '14 at 05:27

pil0t's gravatar image

pil0t
207575763

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

powered by OSQA