Converted "Linq query to Sql query" doesn't make sense to me

Question

I converted a linq query to sql using LinqPad 4. But i am so much confused to the converted sql query. I have a job table that is related to AppliedJob. AppliedJob is related to JobOffer. JobOffer is related to Contract. Contract table has a field CompletedDate that is set to Null initially when a job contract starts. If a job completed ten the field is updated with the current date. I want to get those job list which have CompletedDate !=Null (if found in Contract table). That means a contract related to a job is not completed yet or not found in Contract table. Not found means any contract is not started with the job. My Linq:

     from j in Jobs 

  join jobContract in
                               (
                                   from appliedJob in AppliedJobs.DefaultIfEmpty()
                                   from offer in appliedJob.JobOffers.DefaultIfEmpty() 
                                   from contract in Contracts.DefaultIfEmpty()
                                   select new { appliedJob, offer, contract }
                                   ).DefaultIfEmpty()
                           on j.JobID equals jobContract.appliedJob.JobID into jobContracts
                           where jobContracts.Any(jobContract => jobContract.contract.CompletedDate != null)
                           select j.JobTitle

My Sql query that Linqpad made:

   SELECT [t0].[JobTitle]
FROM [Job] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT NULL AS [EMPTY]
        ) AS [t1]
    LEFT OUTER JOIN ((
            SELECT NULL AS [EMPTY]
            ) AS [t2]
        LEFT OUTER JOIN ([AppliedJob] AS [t3]
            LEFT OUTER JOIN [JobOffer] AS [t4] ON [t4].[AppliedJobID] = [t3].[AppliedJobID]
            LEFT OUTER JOIN [Contract] AS [t5] ON 1=1 ) ON 1=1 ) ON 1=1 
    WHERE ([t5].[CompletedDate] IS NOT NULL) AND ([t0].[JobID] = [t3].[JobID])
    )

My question is that why it makes so many SELECT NULL AS [EMPTY] and LEFT OUTER JOIN in the query? Can i make a simple and understandable query from this? OR is it ok?

are you familiar with SQL do you have a DBA handy where you can have them explain to you what it is you don't understand..Also if you are changing something that you do not understand.. it's best that you don't change it especially if you do not understand sub queries.. — MethodMan, May 02 '17 at 16:32
The LINQ query looks weird - `AppliedJobs.DefaultIfEmpty()`, `Contracts.DefaultIfEmpty()` means no relation, cartesian product. — Ivan Stoev, May 02 '17 at 16:39
then read up online about Basic SQL JOINS Tutorials.. why do people not know how to utilize the free tools that are at their disposal..? — MethodMan, May 02 '17 at 16:39
Contrary to @MethodMan advice, forget about joins and learn to use your model navigation properties. — Ivan Stoev, May 02 '17 at 16:40
@MethodMan if you don't have anything helpful to say, you really should say nothing... — maxbeaudoin, May 02 '17 at 16:44
So you say i should avoid DefaultIfEmpty(), right? @Ivan Stoev — Nur Uddin, May 02 '17 at 17:04
All I'm saying is that you should relate your data using the navigation properties - instead of `AppliedJobs`, there must be some relation from `Job` (`j` variable). Same for `Contracts` - it should be related to `offer` variable. It's hard to tell exactly w/o having your model classes and navigation properties. — Ivan Stoev, May 02 '17 at 17:09
How can i write sql query to get my desired result(given my post) ? @Ivan Stoev — Nur Uddin, May 02 '17 at 17:29
@maxbeaudoin doing a google search is a very helpful suggestion btw.. too many people come here first looking for simple answers that they can find on their own if they just take a second or two to use a google. — MethodMan, May 02 '17 at 17:50

score 3 · Answer 1 · edited May 23 '17 at 12:34

3

DefaultIfEmpty() translates to left outer join. See LEFT OUTER JOIN in LINQ

There are so many "NULL as [Empty]" because NULL != NULL in SQL. See Why does NULL = NULL evaluate to false in SQL server

edited May 23 '17 at 12:34

Community

1
1

answered May 02 '17 at 16:33

Fran

6,440
1
23
35

If not mistaken in SQL, null = null if ANSI_NULLS is 'off'. – Bill Roberts May 02 '17 at 16:41
1

yes, but linq can't count on that being set or supported across all databases so it has to translate NULL to something it knows. – Fran May 02 '17 at 16:43
If i delete deafultIfEmptly() then convert sql in linqpad, will it run without error in sql erver? @Fran – Nur Uddin May 02 '17 at 17:08
you haven't defined your relationships in your question? how could anyone answer that question. Listen to Ivan and work back from contract and set your ef mappings using navigation properties so that you can easily traverse the relationships. – Fran May 02 '17 at 17:15
Actually i don't want to use LINQ anymore! I will use sql Proc in my web app. Can you answer how i can do that for my desired question give at first of my post? @Fran – Nur Uddin May 02 '17 at 17:27

score 0 · Answer 2 · answered May 02 '17 at 16:44

It's been a while since I've touched C# and LINQ, but this is my take.

The reason for the multiple left outer joins and nulls is because you have several (deferred?) calls to DefaultIfEmpty().

No pun intended, but what is the default return value of Enumerable.DefaultIfEmpty()? It is null. And they are all evaluated and gathered before you get to the point of evaluating the join criteria in the LINQ code snippet.

And that code snippet represents the non-null right side of equation. And the whole thing can return an empty set.

So a compatible SQL statement must create a left outer join between an empty set recursively all the way down to the actual SQL join criteria.

It's almost algebraic. Try to understand what both the LINQ and SQL statements are down. Work them both out, backwards from the end all the way to the beginning of each, and you'll see the equivalence.

score 0 · Answer 3 · answered May 02 '17 at 16:53

The reason for all the SELECT NULL AS [EMPTY]s is that these subqueries are not being utilized to return data, only to verify that there is data there. In other words, the LINQ code is optimizing the query to not actually bring in any column data, since it's completely unnecessary for the purposes of these subqueries.

Converted "Linq query to Sql query" doesn't make sense to me

3 Answers3