Menu
I want to create some test cases for Entity Framework queries that surely generate SQL commands that contain CROSS APPLY or OUTER APPLY operators.
Could someone show typical scenarios where these kind of SQL queries appear?
370
So you might conclude, the CROSS APPLY is equivalent to an INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with an implicit join condition of 1=1 whereas the OUTER APPLY is equivalent to a LEFT OUTER JOIN.
OUTER APPLY resembles LEFT JOIN, but has an ability to join table-evaluated functions with SQL Tables. OUTER APPLY's final output contains all records from the left-side table or table-evaluated function, even if they don't match with the records in the right-side table or table-valued function.
The OUTER APPLY operator returns all the rows from the left table expression regardless of its match with the right table expression. For those rows for which there are no corresponding matches in the right table expression, it returns NULL values in columns of the right table expression.
Using the OUTER APPLY OperatorIf you still want the row from the outer table to be included in the result set, you should use the OUTER APPLY operator. The OUTER APPLY operator returns all rows from the outer table, whether or not the function returns data for a specific row.
In LINQ 2 SQL this always results in an APPLY:
from t1 in tab1
from t2 in tab2.Where(t2 => t2.SomeCol == t1.SomeCol).Take(1)
select new { t1, t2 }
In EF this will either fail, or also result in an APPLY (I don't know which one). This is a correlated join which requires an APPLY on the SQL side.
187
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
