Here is the simlified version of the query:
Code: Select all
string seriesId = "828301186000";
var seasons = from s in _entities.Seasons
join se in _entities.Series on s.SerieId equals se.SerieId
join st in _entities.SeasonsTitles on s.SeasonId equals st.SeasonId into stj
from st in stj.DefaultIfEmpty()
where se.PiProgramsCollectionId == seriesId
select new
{
Programmes = from pse in _entities.Seasons
join ps in _entities.Series on pse.SerieId equals ps.SerieId
join p in _entities.Programmes on pse.SeasonId equals p.SeasonId into pj
from p in pj.DefaultIfEmpty()
join tm in _entities.ProgrammesTitles on p.ProgrammeId equals tm.ProgrammeId into tmj
from tm in tmj.DefaultIfEmpty()
join ptm in _entities.ProgrammesTitles on p.ProgrammeId equals ptm.ProgrammeId into ptmj
from ptm in ptmj.DefaultIfEmpty()
where pse.SeasonId == s.SeasonId
select new
{
Title = tm.Title ?? ptm.Title,
}
};
Code: Select all
select new
{
Title1 = tm.Title,
Title2 = ptm.Title,
}
What is strange is that if I run this simple query:
Code: Select all
var titles = from pt1 in _entities.ProgrammesTitles
join pt2 in _entities.ProgrammesTitles on pt1.ProgrammeId equals pt2.ProgrammeId
where pt1.ProgrammeId < 1000
select new
{
Title = pt1.Title ?? pt2.Title
};
If you need generated SQL statements, I can also provide them.