title: Executing a SQL Server stored procedure with c# linq without a DBSet
body: <p>I am working on a project at the moment and for this part that I am working on I am required to call a stored procedure that returns a <code>TicketNumber</code> string in the format <code>TodaysDate.Number</code> (e.g. <code>11032019.0001</code>). </p>
  
  <p>Now this is where the problem is. The procedure works and I can call this by running a SQL statement on the database however nothing seems to have worked when trying to run this with C# in the controller.</p>
  
  <p>I currently have this:</p>
  
  <pre><code>var NewOrderIdOut = new SqlParameter("@NewOrderIdOut", "");
  var result = await db.Tickets.SqlQuery("[dbo].[usp_NewOrderId] @NewOrderIdOut", NewOrderIdOut).SingleAsync();
  </code></pre>
  
  <p>Stored procedure is as follows:</p>
  
  <pre><code>CREATE PROCEDURE usp_NewOrderId 
      (@NewOrderIdOut CHAR(13) OUTPUT ) 
  AS 
  BEGIN
      IF EXISTS (SELECT 1 FROM OrderNumberGenerator 
                 WHERE CreatedDate &lt;&gt; CAST(GETDATE() AS DATE))
          TRUNCATE TABLE OrderNumberGenerator   -- restart the counter everyday :)
  
      INSERT INTO OrderNumberGenerator 
      DEFAULT VALUES
  
      SELECT @NewOrderIdOut =
             CONVERT(CHAR(8), GETDATE(), 112) +
             RIGHT( '.000' + CAST(SCOPE_IDENTITY() AS VARCHAR(4)), 4)  
  END
  </code></pre>
  
  <p>I get this error:</p>
  
  <blockquote>
    <p>The data reader is incompatible with the specified 'PSARMM.Models.Ticket'. A member of the type, 'TicketId', does not have a corresponding column in the data reader with the same name.</p>
  </blockquote>
  
  <p>Only problem is the procedure doesn't return all the parameters that are in the model just the single ticketNumber string so an error gets thrown.</p>
  
  <p>Another problem, I can't use <code>db.ExecuteSqlCommand</code> because I can't install the <code>Microsoft.EntityFrameworkCore</code> because there are some incompatible files according to nuget.</p>
  
  <p>I could create a new table just used to temporarily store this new <code>ticketNumber</code> I am creating but felt there is probably a better way so am reaching out to see if you all have any genius ideas :D</p>
  
  <p>As for direction of the parameter, I have no idea.</p>
  
  <p>Cheers</p>
  
  <p>EDIT: I ended up scrapping this idea and instead used a trigger within the table itself to call the procedure and put the result into the new row in the correct column. And it worked like a charm.</p>