Entity Framework And Cross/outer Apply

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 scenar

Solution 1:

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)
selectnew { 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.

Solution 2:

Something like this would generate an outer apply:

var ListLocation = from d in dc.Department                               
                         selectnewDepartmentViewModel()
                         {
                            LocationID = d.LocationID,
                            ManagerName = d.Managers.FirstOrDefault(p => p.ManagerId == id).Name

                          };

If it doesn't work you can always pass your own query if you're using EF:

var q2 = context.Departments.SqlQuery("Select ...");

Remember though that your provider has to be SQL Server 2005 and higher, EF does not support OUTER APPLY for Oracle