[Association] with QueryExpressionMethod does not honor CanBeNull property

[shameleo]

Describe your issue

If CanBeNull is false, I would expect generated SQL to use INNER JOIN or CROSS APPLY. But it uses LEFT JOIN or OUTER APPLY instead.

Steps to reproduce

    public class Client
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class Service
    {
        public int Id { get; set; }
        public int? IdClient { get; set; }

        //[Association(ThisKey = nameof(IdClient), OtherKey = nameof(l2db_bug.Client.Id), CanBeNull = false)]
        //[Association(ExpressionPredicate = nameof(Client_ExprPr), CanBeNull = false)]
        [Association(QueryExpressionMethod = nameof(Client_QExpr), CanBeNull = false)]
        public Client Client { get; set; }

        // works fine
        //static Expression<Func<Service, Client, bool>> Client_ExprPr =>
        //    (s, c) => s.IdClient == s.Id;

        // always generates left join or outer apply (if query is more complicated)
        static Expression<Func<Service, IDataContext, IQueryable<Client>>> Client_QExpr =>
            (s, db) => db.GetTable<Client>().Where(c => c.Id == s.IdClient);
    }

var q_clientNames = db.GetTable<Service>().Select(s => s.Client.Name);

Environment details

Linq To DB version: 5.4.0

Database (with version): SQL Server 2008

ADO.NET Provider (with version): Microsoft.Data.SqlClient 4.1.0

.NET Version: .NET Core 2.1

[sdanyliv]

It is not a bug, we have special code which ignores CanBeNull for complex associations. Does it make performance isssues for your queries?

[jods4]

This looks like a dupe of #3658, which was supposedly fixed in 5.0.0.
Did the fix not cover all types of associations?

[shameleo]

It is not a bug, we have special code which ignores CanBeNull for complex associations.

For what reason?
Also, QueryableAssociationTests.cs and some other tests use QueryExpressionMethod and CanBeNull combination.

Does it make performance isssues for your queries?

No, it doesn't. At least for now. It's more about correctness. I don't know what is the point of CanBeNull attribute, but I understand it as not like hint (CanBeNull = false, so I promise you that every Service references to Client"), but like forced requirement (CanBeNull = false, so I if any Service does not references to Client - skip it). Of course I can add where ... is not null, but it's not great workaround, as if association is changed later (like I just did), all queries using it should be fixed.

This looks like a dupe of #3658, which was supposedly fixed in 5.0.0.

I tested with SqlServer and Sqlite. Queries was slighly different, but I doubt it matters. CanBeNull with QueryExpressionMethod was ignored in both cases. CanBeNull with ExpressionPredicate was ignored in Sqlite, but not in SqlServer. It's weird.