why does LINQ To SQL result in SQL like this?

When LINQ translates the below syntax to SQL, the (inner) where clause gets moved to the outer-most query. That's super-unfriendly to the database. I wrote this like Hibernate's HQL (is this appropriate?), and I've written SQL for many moons.

Can anyone help explain what gives, or point me in the way of a resolution?

        var rc = (
            from dv in (
                from dv_j in (
                    from x in adc.JobManagement
                    join j in adc.Job on x.JobId equals j.JobId
                    join js in adc.JobStatus on j.StatusId equals js.JobStatusId
                    join cm in adc.ClientManagement on j.ClientId equals cm.ClientId
                    join o in adc.User on cm.UserId equals o.UserId
                    join jm in adc.JobManagement on j.JobId equals jm.JobId
                    where
                        (x.UserId == aid || cm.UserId == aid)
                        && (j.StatusDate == null || j.StatusDate >= getFromDate())
                        && (jm.ManagementRoleCode == MR_MANAGER)
                    select new
                    {
                        j.JobId,
                        Job = j.InternalName == null ? j.ExternalName : j.InternalName,
                        JobStatusDate = j.StatusDate,
                        JobStatus = js.Code,
                        Owner = o.Username,
                        Role = jm.ManagementRoleCode
                    })
                join s in adc.Submission on dv_j.JobId equals s.JobId into dv_s
                from s in dv_s.DefaultIfEmpty()
                select new
                {
                    dv_j.JobId,
                    dv_j.Job,
                    dv_j.JobStatusDate,
                    dv_j.JobStatus,
                    dv_j.Owner,
                    dv_j.Role,
                    s.SubmissionId,
                    s.CandidateId,
                    s.SubmissionDate,
                    StatusDate = s.StatusDate,
                    StatusId = s.StatusId
                })
            join c in adc.Candidate on dv.CandidateId equals c.CandidateId into dv_c
            join ss in adc.SubmissionStatus on dv.StatusId equals ss.SubmissionStatusId into dv_ss
            from c in dv_c.DefaultIfEmpty()
            from ss in dv_ss.DefaultIfEmpty()
            orderby
                dv.StatusId == null ? dv.StatusDate : dv.JobStatusDate descending,
                dv.Job,
                c.LastName,
                c.NickName,
                c.FirstName
            select new Projects
            {
                Id = dv.JobId,
                Project = dv.Job,
                Submitted = dv.SubmissionDate,
                Candidate = Form开发者_Go百科atIndividual(c.LastName, c.FirstName, c.NickName),
                Status = dv.StatusId == null ? ss.Code : dv.JobStatus,
                StatusDate = dv.StatusId == null ? dv.StatusDate : dv.JobStatusDate,
                Role = dv.Role,
                Owner = dv.Owner
            });

---

Try breaking down the one statement into two. This would work as it cannot move the where to a place that doesn't exist yet. This does make multiple round trips to the database, but it is better than having most of several large tables being joined then culled. I would try this:

var inMemoryTable = (
    from x in adc.JobManagement
    join j in adc.Job on x.JobId equals j.JobId
    join js in adc.JobStatus on j.StatusId equals js.JobStatusId
    join cm in adc.ClientManagement on j.ClientId equals cm.ClientId
    join o in adc.User on cm.UserId equals o.UserId
    join jm in adc.JobManagement on j.JobId equals jm.JobId
    where
        (x.UserId == aid || cm.UserId == aid)
        && (j.StatusDate == null || j.StatusDate >= getFromDate())
        && (jm.ManagementRoleCode == MR_MANAGER)
    select new
    {
        j.JobId,
        Job = j.InternalName == null ? j.ExternalName : j.InternalName,
        JobStatusDate = j.StatusDate,
        JobStatus = js.Code,
        Owner = o.Username,
        Role = jm.ManagementRoleCode
    });

var rc = (
    from dv in (
        from dv_j in inMemoryTable
        join s in adc.Submission on dv_j.JobId equals s.JobId into dv_s
        from s in dv_s.DefaultIfEmpty()
        select new
        {
            dv_j.JobId,
            dv_j.Job,
            dv_j.JobStatusDate,
            dv_j.JobStatus,
            dv_j.Owner,
            dv_j.Role,
            s.SubmissionId,
            s.CandidateId,
            s.SubmissionDate,
            StatusDate = s.StatusDate,
            StatusId = s.StatusId
        })
    join c in adc.Candidate on dv.CandidateId equals c.CandidateId into dv_c
    join ss in adc.SubmissionStatus on dv.StatusId equals ss.SubmissionStatusId into dv_ss
    from c in dv_c.DefaultIfEmpty()
    from ss in dv_ss.DefaultIfEmpty()
    orderby
        dv.StatusId == null ? dv.StatusDate : dv.JobStatusDate descending,
        dv.Job,
        c.LastName,
        c.NickName,
        c.FirstName
    select new Projects
    {
        Id = dv.JobId,
        Project = dv.Job,
        Submitted = dv.SubmissionDate,
        Candidate = FormatIndividual(c.LastName, c.FirstName, c.NickName),
        Status = dv.StatusId == null ? ss.Code : dv.JobStatus,
        StatusDate = dv.StatusId == null ? dv.StatusDate : dv.JobStatusDate,
        Role = dv.Role,
        Owner = dv.Owner
    });