Can this LINQ lambda expression be written any better? For the sake of knowledge, I wanted to convert a SQL query to a LINQ query expression. Here is the original SQL:

SELECT CT.COURSE_NODE_ID AS CNID, CT.NODE_TEXT
FROM    COURSE_RELATED_VERSIONS AS CRV INNER JOIN
        COURSE_TREE AS CT ON CRV.COURSE_NODE_ID = CT.COURSE_NODE_ID
WHERE (CRV.COURSE_ID = '38890') AND (CRV.COURSE_PARENT_NODE_ID = '-1')


Basically, just grabbing two fields from a table based on certain criteria (keys match, course ID matches criteria, parent node not equal -1). Here's the LINQ query expression that I came up with, using LINQPad:

from ct in COURSE_TREEs
join crv in COURSE_RELATED_VERSIONS on ct.COURSE_NODE_ID equals crv.COURSE_NODE_ID
where crv.COURSE_ID == 38890 &amp;&amp; crv.COURSE_PARENT_NODE_ID == -1
select new {ct.COURSE_NODE_ID, ct.NODE_TEXT}


Not too bad, still understandable to my SQL-centric brain. But, for grins, I wondered what this query expression would look like as a lambda expression. For the life of me, I could not figure out the syntax. So, I used the lambda tool in LINQPad to see what my LINQ query expression would look like. Here it is:

COURSE_TREEs
.Join (
   COURSE_RELATED_VERSIONS, 
   ct =&gt; ct.COURSE_NODE_ID, 
   crv =&gt; crv.COURSE_NODE_ID, 
   (ct, crv) =&gt; 
      new  
      {
         ct = ct, 
         crv = crv
      }
)
.Where (temp0 =&gt; ((temp0.crv.COURSE_ID == 38890) &amp;&amp; (temp0.crv.COURSE_PARENT_NODE_ID ==      -1)))
.Select (
   temp0 =&gt; 
      new  
      {
         COURSE_NODE_ID = temp0.ct.COURSE_NODE_ID, 
         NODE_TEXT = temp0.ct.NODE_TEXT
      }
)


Whoah! Not what I figured the lambda expression query would look like. So, I'm studying the output from LINQPad on how my query expression looks as a lambda expression, and I'm wondering if it can be written any better? I'm still learning the ropes of LINQ (and lambda expressions), but I can't help but feel that the resulting lambda expression here is too complex! Am I wrong? Is it possible to write a lambda expression that produces the same output as the original SQL and query expression, but not be needlessly complex? Perhaps 'complex' is subjective, since it may only appear complex to my SQL brain. I just feel like the lambda expression generated in LINQPad can be written better... I just don't know how.
LINQ isn't as rigid about the order of clauses as SQL is (this applies to both syntaxes). This means that if you switch your Join around, you can put your Where before your Join.
If you have Select right after Join, you can combine the two together.
When the property you're using in an anonymous object creation expression has the same name as the one you're assigning it to, you can omit the name.


This means that your code could be simplified to this:

COURSE_RELATED_VERSIONS
.Where (crv =&gt; crv.COURSE_ID == 38890 &amp;&amp; crv.COURSE_PARENT_NODE_ID == -1)
.Join (
   COURSE_TREEs, 
   crv =&gt; crv.COURSE_NODE_ID,
   ct =&gt; ct.COURSE_NODE_ID,
   (crv, ct) =&gt; new { ct.COURSE_NODE_ID, ct.NODE_TEXT }
)


But in general, if you're doing something more complicated, you need to follow the pattern in your question. Which is exactly why the query syntax exists: it can make complicated queries much simpler.

Also, I would suggest that your tweak your mapping so that the names follow usual .Net naming conventions (e.g. CourseRelatedVersion or CourseNodeId).