Linq2db: SQL Server table hints in join end up with StackOverflowException

[DmitriiGaidukov]

Hi, guys! I was looking for a way for including WITH(..) MS SQL table hints into resulted SQL and it seems such thing is already implemented https://github.com/linq2db/t4models/issues/7

Unfortunately I’ve run into an issue trying to apply it to multiple tables in the same query. I’m using the

With([NotNull] this ITable table, [NotNull, SqlQueryDependent] string args)

method from LinqExtensions class.

Here is the test example. I have a couple of table entity classes:

```c#
[Table(Name = "Person", Schema = "dbo", Database = "TestDb")]
public class PersonEntity
{
[Column]
[PrimaryKey]
[Identity]
public int Id { get; set; }

    [Column]
    public string Name { get; set; }
}


[Table(Name = "Adress", Schema = "dbo", Database = "TestDb")]
public class AdressEntity
{
    [Column]
    [PrimaryKey]
    public int Id { get; set; }

    [Column]
    public int PersonId { get; set; }
}

And two almost the same queries, which differ only in using  «a.PersonId» non-PK column in the first query instead of «a.Id» PK column in the second one.

```c#
var query1 = from p in context.GetTable<PersonEntity>().With("READUNCOMMITTED")
                         join a in context.GetTable<AdressEntity>().With("READUNCOMMITTED")
                         on p.Id equals **a.PersonId** //non-PK column
                         select p;


var query2 = from p in context.GetTable<PersonEntity>().With("READUNCOMMITTED")
                         join a in context.GetTable<AdressEntity>().With("READUNCOMMITTED")
                         on p.Id equals **a.Id** //PK column
                         select p; 

Query1 successfully produce sql just as expected:

-- SqlServer SqlServer.2008
SELECT
    [p].[Id],
    [p].[Name]
FROM
    [TestDb].[dbo].[Person] [p] WITH (READUNCOMMITTED)
        INNER JOIN [TestDb].[dbo].[Adress] [a] WITH (READUNCOMMITTED) ON [p].[Id] = [a].[PersonId]

But the sql generation for Query2 (which is joining both tables by their PK) falls with the System.Collections.Generic.KeyNotFoundException

Ps. I understand it is an contrived example in the sence it is not often that there is a need to join tables by their PKs on both sides. But without With() the query is built ok. And I just want to make sure that I use this feature correctly...

[sdanyliv]

It is reproducible on current sources? Try to disable JoinOptimization in Configuration.
I’ve found today regression. Refactoring is still in progres.

[DmitriiGaidukov]

https://github.com/sdanyliv yeah, I've just reproduced It both on the current sources and the newest stable version from nuget. But your advice of disabling the optimization has hepled me out. Thanks! Linq2db issued a nice sql with all my hints after this line of code

Configuration.Linq.OptimizeJoins = false;

My only worry if it is going to have any side effects. I've just never come across this option before...

[sdanyliv]

My only worrying is it should not have any side effects? I've just never come across with option before...

Don’t worry. You have just disabled additional optimization algorithm. I’ll prepare fix for that.

[sdanyliv]

Fixed by #953