title: Calling a SQL Server stored procedure with linq service through c#
body: <p>I am new to Linq and trying to convert this SQL Server stored procedure into Linq, I am building a Silverlight business app and need to call on this procedure to return a grid of results.  </p>
  
  <p>I have multiple parameters that the users can use to search for particular pieces. They narrow down their search through the UI and when they hit the search button, the code behind takes all the arguments and sends it to my Linq service, which then needs to call on the stored procedure. </p>
  
  <p>Here is the stored procedure.</p>
  
  <pre class="lang-sql prettyprint-override"><code>ALTER PROCEDURE dbo.spSearchResults
  @PieceType      nvarchar(6) =  '',
  @FileType       nvarchar(3) = '',
  @Market     nvarchar(6) = '',
  @PieceNumber        nvarchar(6) = '',
  @Header1    nvarchar(50) = '',
  @Header2    nvarchar(50) = '',
  @Header3    nvarchar(50) = '',
  @Header4    nvarchar(50) = '',
  @JobNumber      nvarchar(50)=' ',
  @bShowInActive  BIT = 0,
  @UDAC1      nvarchar(50) = '',
  @UDAC2      nvarchar(50) = '',
  @UDAC3      nvarchar(50) = '',
  @UDAC4      nvarchar(50) = ''   
  AS
  BEGIN
  SET NOCOUNT ON
  
  SELECT J.* 
  FROM Job J
    LEFT JOIN JobHeading H1 (NOLOCK) ON J.[JobNumber] =  H1.[JobID]
    LEFT JOIN JobHeading H2 (NOLOCK) ON J.[JobNumber] =  H2.[JobID]
    LEFT JOIN JobHeading H3 (NOLOCK) ON J.[JobNumber] =  H3.[JobID]
    LEFT JOIN JobHeading H4 (NOLOCK) ON J.[JobNumber] =  H4.[JobID]
    LEFT JOIN JobUDAC udac1 (NOLOCK) ON J.[JobNumber] = udac1.[JobID]
    LEFT JOIN JobUDAC udac2 (NOLOCK) ON J.[JobNumber] = udac2.[JobID]
    LEFT JOIN JobUDAC udac3 (NOLOCK) ON J.[JobNumber] = udac3.[JobID]
    LEFT JOIN JobUDAC udac4 (NOLOCK) ON J.[JobNumber] = udac4.[JobID]
  WHERE ((@PieceType = '') OR (PieceType = @PieceType))
    AND ((@FileType = '') OR (FileType = @FileType))
    AND ((@Market = '') OR (Market = @Market))
    AND ((@PieceNumber = '') OR (PieceNumber = @PieceNumber))
    AND ((@JobNumber = '') OR (JobNumber = @JobNumber))
    AND (J.IsActive=1 OR @bShowInActive = 1)
    AND (((@Header1 = '' AND @Header2 = '' AND @Header3 = '' AND @Header4 = '') OR 
          H1.HeadingRowID = @Header1)
      OR (--@Header2=0 OR 
          H2.HeadingRowID = @Header2 )
      OR (--@Header3=0 OR 
          H3.HeadingRowID = @Header3)
      OR (--@Header4=0 OR 
          H4.HeadingRowID = @Header4))
    AND (((@UDAC1 = '' AND @UDAC2 = '' AND @UDAC3 = '' AND @UDAC4 = '') OR 
          udac1.UDACRowID = @UDAC1)
      OR (--@Header2=0 OR 
          udac2.UDACRowID = @UDAC2 )
      OR (--@Header3=0 OR 
          udac3.UDACRowID = @UDAC3)
      OR (--@Header4=0 OR 
          udac4.UDACRowID = @UDAC4))
  </code></pre>
  
  <p>In Linq I found that there are certain conversions to do and this is my attempt. </p>
  
  <pre><code>var query = from j in Job
                      join JobHeading H1 in Job on headingRowID1 equals H1
                      join JobHeading H2 in Job on headingRowID2 equals H2
                      join JobHeading H3 in Job on headingRowID3 equals H3
                      join JobHeading H4 in Job on headingRowID4 equals H4
                      join JobUDAC udac1 in Job on udacRowID1 equals udac1
                      join JobUDAC udac2 in Job on udacRowID2 equals udac2
                      join JobUDAC udac3 in Job on udacRowID3 equals udac3
                      join JobUDAC udac4 in Job on udacRowID4 equals udac4
                      join PieceType in db on piece equals PieceType
                      join JobFileType in db on filetype equals JobFileType
                      join Book in db on market equals Book
                      join PieceNumber in db on pieceNumber equals PieceNumber
                      join JobNumber in db on jobNumber equals JobNumber
                      join Job in db on FindJobs equals db
                      where ((piece = string.Empty) || (PieceType = piece))
                        &amp;&amp; ((filetype = string.Empty) || (JobFileType = filetype))
                        &amp;&amp; ((market = string.Empty) || (Book = market))
                        &amp;&amp; ((pieceNumber = string.Empty) || (PieceNumber = pieceNumber))
                        &amp;&amp; ((jobNumber = string.Empty) || (JobNumber = jobNumber))
                        &amp;&amp; (showInActive = true)
                        &amp;&amp; ((((headingRowID1 = string.Empty) + (headingRowID2 = string.Empty) + (headingRowID3 = string.Empty) + (headingRowID4 = string.Empty)) ||
                              H1.HeadingRowID = headingRowID1)
                          || (H2.HeadingRowID = headingRowID2)
                          || (H3.HeadingRowID = headingRowID3)
                          || (H4.HeadingRowID = headingRowID4))
                        &amp;&amp; ((((udacRowID1 = string.Empty) + (udacRowID2 = string.Empty) + (udacRowID3 = string.Empty) + (udacRowID4 = string.Empty)) ||
                              udac1.UDACRowID = udacRowID1)
                          || (udac2.UDACRowID = udacRowID2)
                          || (udac3.UDACRowID = udacRowID3)
                          || (udac4.UDACRowID = udacRowID4))
                      select j.Job;
          return query;
  </code></pre>
  
  <p>However, the beginning 'Job' has an error, and says 'could not find an implementation ... 'join' not found' Can anyone help translate? Or offer a better way to call the stored procedure with the code behind? Thanks</p>