Problem with stored procedure in LINQ-to-SQL

Question

Hi all, I'm having a never-ending problem with trying to call a stored procedure from a controller - if I could I'd add a bounty to this as it's taken way too much time already and I don't know what else to do (but I don't have the points). Based on my research it seems it's a known bug, but not of the workarounds have worked for me so I'll do my best to explain in hopes of a solution:

1- I create a stored procedure that looks something like this:

CREATE PROCEDURE [dbo].[db_name] 
    @start_dt datetime,
    @end_dt datetime

AS
BEGIN 

SET FMTONLY OFF;

SELECT [date],
    COUNT(visits) as Visits,
    SUM(CASE entrance WHEN '1' THEN 1 ELSE 0 END) AS ENT1,
    SUM(CASE entrance WHEN '2' THEN 1 ELSE 0 END) AS ENT2,
    SUM(CASE entrance WHEN '3' THEN 1 ELSE 0 END) AS ENT3,
    SUM(CASE entrance WHEN '4' THEN 1 ELSE 0 END) AS ENT4,
    SUM(CASE entrance WHEN '5' THEN 1 ELSE 0 END) AS ENT5
FROM some_view
WHERE [date] between @start_dt and @end_dt
group by [date]

END

2 - Then I created a LINQ to SQL entity object manually which has a property for each of the above returned columns (Date, Visits, ENT1, ENT2...ENT5).

3 - I tried dragging the stored proc as a function onto the said entity object in VS designer - but it would not allow me to as it says the "returned schema does not match the target class". So I just dragged it elsewhere and a function is created (I even changed the return type to the object but didn't work, see below).

*NOTE: I believe Visual Studio thinks the stored procedure returns an Int - not the table of values expected. * The suggested fix for this type of problem was to make a dummy stored procedure that only has a simple select statement and replace it after dragging it to the object but that didn't work either

4 - I tried everything I could think of in the controller, such as trying to return a IMultipleResults when calling the stored proc and converting it to the model object without any success.

The main issue seems to be that not rows are returned, just an int.

Thanks in advance for ANY help! I'm new to MVC so please feel free to tell me the whole way I'm going about it is off if you think it is.

UPDATE, here's the generated XML of the model:

<?xml version="1.0" encoding="utf-8"?>
<Database Name="DB_Name" Class="nameDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007"&gt;
  <Connection Mode="WebSettings" ConnectionString="Data Source=some_data_source;Initial Catalog=some_db;Integrated Security=True" SettingsObjectName="System.Configuration.ConfigurationManager.ConnectionStrings" SettingsPropertyName="name_ConnectionString" Provider="System.Data.SqlClient" />
  <Table Name="" Member="Visits">
    <Type Name="Visit">
      <Column Name="Date" Type="System.DateTime" CanBeNull="false" />
      <Column Name="Visits" Type="System.Int32" CanBeNull="false" />
      <Column Name="ENT1" Type="System.Int32" CanBeNull="false" />
      <Column Name="ENT2" Type="System.Int32" CanBeNull="false" />
      <Column Name="ENT3" Type="System.Int32" CanBeNull="false" />
      <Column Name="ENT4" Type="System.Int32" CanBeNull="false" />
      <Column Name="ENT5" Type="System.Int32" CanBeNull="false" />
    </Type>
  </Table>
  <Function Name="dbo.sp_proc_name" Method="sp_proc_name">
    <Parameter Name="start_dt" Type="System.DateTime" DbType="DateTime" />
    <Parameter Name="end_dt" Type="System.DateTime" DbType="DateTime" />
    <ElementType Name="sp_proc_nameResult">
      <Column Name="date" Type="System.String" DbType="VarChar(30)" CanBeNull="true" />
      <Column Name="Visits" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="ENT1" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="ENT2" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="ENT3" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="ENT4" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="ENT5" Type="System.Int32" DbType="Int" CanBeNull="true" />
    </ElementType>
  </Function>
</Database>

UPDATE 2 When I run the stored procedure in Visual studio the output is a set of rows followed by:

No rows affected.
(129 row(s) returned)
@RETURN_VALUE = 0

I'm guessing it's probably the last line that's the culprit.

Answer 1

Random guess...

I reckon SET FMTONLY OFF; is preventing correct parsing of the stored proc. I can't recall exactly why but I know some clients have issues with it (like Reporting Services and temporary tables)

And I've never used it in many years of SQL code monkeying...

Edit: Why no SET NOCOUNT ON?

This is returned as a 2nd recordset (Ok, tiny) that may cause it. Please see my question about this too.. it can mess up nHibernate for example.

Answer 2

No law states that one must use Linq2Sql or EF or nHibernate or SubSonic or whatever with MVC. So why not just write a little static function to call the proc and get the results back as a simple object graph rather than fussing with a whole ORM for a pretty direct RPC scenario.