Entity Framework and CROSS/OUTER APPLY
Something like this would generate an outer apply:
var ListLocation = from d in dc.Department
select new DepartmentViewModel()
{
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
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.