Entity Framework and CROSS/OUTER APPLY

cross-apply entity-framework outer-apply sql sql-server

Question

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?

1
10
6/12/2019 12:00:45 PM

Accepted Answer

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.

14
5/11/2013 8:58:57 PM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow