Another question. Your old versions used to generate sub-queries and your new versions generate OUTER APPLY clauses. I liked sub-queries better. Is there a way to choose old behavior?

Try to add AsSubquery() call, maybe it will help

Hey guys, any update on this?
We want to upgrade our code from old version (2.9.6) for that we need a couple of bugs resolved - this one and also 4163 and 4167.

Will try to check it in next couple of weeks. Don't have much time currently

Try the following query as workaround:

var data = (
    from pe in db.PERSON
    from cc in db.CITY
      .Where(cc => cc.CITYCODE == pe.CITYCODE)
      .Take(1)
      .DefaultIfEmpty()
    select new
    {
        CURRENCYCALCULATEDSYMBOL = cc.CITYNAME,
    }).Distinct().ToList();

Try the following query as workaround:

var data = (
    from pe in db.PERSON
    from cc in db.CITY
      .Where(cc => cc.CITYCODE == pe.CITYCODE)
      .Take(1)
      .DefaultIfEmpty()
    select new
    {
        CURRENCYCALCULATEDSYMBOL = cc.CITYNAME,
    }).Distinct().ToList();

Thanks for that.
However problem is not in writing new queries.
We have (big!) codebase that we want to upgrade to new version.
We need to be sure that our existing code still works after upgrade.

Change happended in 3.0.0-preview.2

Old sql

SELECT DISTINCT
        (
                SELECT
                        cc_1.CITYNAME
                FROM
                        CITY cc_1
                WHERE
                        ROWNUM <= 1 AND (cc_1.CITYCODE IS NULL AND pe.CITYCODE IS NULL OR cc_1.CITYCODE = pe.CITYCODE)
        ),
        (
                SELECT
                        cc_2.CITYNAME
                FROM
                        CITY cc_2
                WHERE
                        ROWNUM <= 1 AND (cc_2.CITYCODE IS NULL AND pe.CITYCODE IS NULL OR cc_2.CITYCODE = pe.CITYCODE)
        )
FROM
        PERSON pe

Probably by this PR #2087

Actually both queries (old version and new) look strange. Providers without APPLY support generate more sane SQL:

SELECT DISTINCT
        (
                SELECT
                        cc.CITYNAME
                FROM
                        CITY cc
                WHERE
                        (cc.CITYCODE = pe.CITYCODE OR cc.CITYCODE IS NULL AND pe.CITYCODE IS NULL) AND
                        ROWNUM <= :take
        )
FROM
        PERSON pe

Honestly, I'm not sure why you added support for APPLY.
It seems to me that the same can be achieved with simple JOIN.
Better to have one approach than two in my opinion.

Wondering what was your motivation?
Wondering also, are there options to generate JOIN instead of APPLY?

While APPLY is not being necessary in this specific case, we still need it for more complex queries.
You can set PreferApply option to false to not force APPLY when we can use joins

Hey guys!
Any update on this issue by any chance?
We raised this one and a couple others (4163, 4167, 4172) and they stop us from upgrading to latest version.

Hi guys!
This bug was open for more than 3 month.
Wondering if this project is still supported?
Is there any way we can contribute?