Join sub query table to table function with outer apply using Entity Framework

Question

I created a SQL Server query that gets the correct results by getting a distinct list of case id's and joins them to the fnGetRelatedCases table function. However, I am unable to create an Entity Framework query that does the same.

SQL Server:

 select 
     cse.*
 from 
     (select distinct 
          CaseID
      from 
          ELFJuv.fnGetCalendar()
      where 
          Department = @Department 
          and HearingDateOnly = @FilterDate) as C
 outer apply 
     ELFJuv.fnGetRelatedCases(C.CaseID) as CSE
 where 
     CSE.CaseID is not null

The query below almost works but it does not join by a distinct list of case id's.

Entity Framework:

IEnumerable<fnGetRelatedCases_Result> cases = 
(from calendar
 in _ctx.fnGetCalendar()
 where calendar.Department.Equals(department) &&
 calendar.HearingDateOnly == filterDate
 from fullCaseDetail in _ctx.fnGetRelatedCases(calendar.CaseID)
 select fullCaseDetail).ToList();

I need to pass a distinct list of case id's into the fnGetRelatedCases table function but I am unable to join correctly since all the case id's are being joined.

score 0 · Answer 1 · answered Jan 19 '16 at 19:38

I figured out how to solve the problem.

(from calendar in _ctx.fnGetCalendar()
.Where(d => d.HearingDateOnly == filterDate && d.Department == department)
.Select(x => new { x.CaseID })
.Distinct()
from fullCaseDetail in _ctx.fnGetRelatedCases(calendar.CaseID)
select fullCaseDetail).ToList();

score 0 · Answer 2 · answered Jan 19 '16 at 19:45

Here you go

var cases = 
   (from caseID in (from calendar in _ctx.fnGetCalendar()
               where calendar.Department.Equals(department)
                   && calendar.HearingDateOnly == filterDate
               select calendar.CaseID).Distinct()
    from fullCaseDetail in _ctx.fnGetRelatedCases(caseID)
    select fullCaseDetail).ToList();

which is equivalent to

// Subquery
var subquery =
    (from caseID in (from calendar in _ctx.fnGetCalendar()
     where calendar.Department.Equals(department)
         && calendar.HearingDateOnly == filterDate
         select calendar.CaseID).Distinct();
// Query
var query = 
    from caseID in subquery
    from fullCaseDetail in _ctx.fnGetRelatedCases(caseID)
    select fullCaseDetail;
// Result
var cases = query.ToList();

Thanks! I like your approach better. – user3638771 Jan 23 '16 at 17:18 — user3638771, Jan 23 '16 at 17:18

Join sub query table to table function with outer apply using Entity Framework

2 Answers2