Entity Framework - how to query over multiple tables and choose columns to display
<p>I have a database with multiple tables and some stored procedures that query if to get specific data from various tables in order to display desired information.
Find one example of a stored procedure bellow:</p>

<pre><code>SELECT DISTINCT             
    ROW_NUMBER() OVER (ORDER BY dbo.[table1].ReportedDate DESC) AS rowNumber,
    dbo.[table1].[id],
    dbo.[table1].caseReference,
    dbo.[table2].organisationName AS customerName,
    dbo.[table3].assetRegistration,
    dbo.[table4].surname
FROM dbo.[table1] WITH (NOLOCK)
LEFT JOIN   dbo.[table2] with (NOLOCK)
ON          dbo.[table2].JobId = dbo.[table1].[id]
LEFT JOIN   dbo.[table3] WITH (NOLOCK)
ON          dbo.[table3].id = dbo.[table2].[JobServiceId]
LEFT JOIN   dbo.[table4] WITH (NOLOCK)
ON          dbo.[table4].[jobID] = dbo.[table1].[id]
WHERE (table1.caseReference LIKE @caseReference+'%')
</code></pre>

<p>I want to move from using such stored procedures to a more code based approach using entity framework. How can I recreate a query like the one above using Linq query over the dbContext classes mapped to the database?</p>

<p>I am mostly having problems in figuring out how to choose the data I want to be returned from each table and how to put it all together.</p>