You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
var users = from u in db.GetTable<AM112U>()
from d in db.GetTable<AM114U>().Where(d => u.WINDOWSDOMAINID == d.ID)
where d.NORMALIZEDNAME == "fakedomain"
select u;
var query = from e in db.GetTable<AM113U>()
where users.Where(u => e.AM_WINDOWSUSERID == u.ID).Any()
select new { e.ID, e.HR_EMPLOYEEID, e.AM_WINDOWSUSERID };
Generates SQL:
SELECT e.ID, e.HR_EMPLOYEEID, e.AM_WINDOWSUSERID
FROM AM113U e
WHERE (
SELECT '.'
FROM AM112U u
INNER JOIN AM114U d ON u.WINDOWSDOMAINID = d.ID
WHERE d.NORMALIZEDNAME = 'fakedomain' AND e.AM_WINDOWSUSERID = u.ID
FETCH FIRST 1 ROWS ONLY
) IS NOT NULL
Which doesn't use WHERE EXISTS as expected and causes and error because of the FETCH FIRST 1 ROWS ONLY is unexpectedly added.
The expected SQL should be (which is what is generated when using SqlServer or Access DataProviders):
SELECT e.ID, e.HR_EMPLOYEEID, e.AM_WINDOWSUSERID
FROM AM113U e
WHERE EXISTS (
SELECT '.'
FROM AM112U u
INNER JOIN AM114U d ON u.WINDOWSDOMAINID = d.ID
WHERE d.NORMALIZEDNAME = 'fakedomain' AND e.AM_WINDOWSUSERID = u.ID
)
How can I use .Any() to produce a valid SQL statement using WHERE EXISTS?
The text was updated successfully, but these errors were encountered:
The following code:
Generates SQL:
Which doesn't use WHERE EXISTS as expected and causes and error because of the FETCH FIRST 1 ROWS ONLY is unexpectedly added.
The expected SQL should be (which is what is generated when using SqlServer or Access DataProviders):
How can I use .Any() to produce a valid SQL statement using WHERE EXISTS?
The text was updated successfully, but these errors were encountered: