Entity Framework and CROSS/OUTER APPLY

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?

Suzettesuzi

Answer 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)
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.

Albertson

Thank you! This gives me a CROSS APPLY query. Also if I use Take(2).DefaultIfEmpty() then an OUTER APPLY query is created. – Suzettesuzi

Answer 2

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

Atiana