Linq to sql stored proc not returning results

Question

I have a Linq to SQL DBML

Contained within the DBML is a stored proc

When the proc is executed via Microsoft SQL Server Managment tool it return 60 rows.

Bu the DBML doesnt think that it returns any results.

Has anyone had this issue before or know how to resolve it?

Sp

EDIT Code:

[Function(Name="dbo.WQNT_PeekViewNextZone")] 
public int WQNT_PeekViewNextZone([Parameter(Name="Slot", DbType="Int")] System.Nullable<int> slot) 
{ 
  IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), slot); 
  return ((int)(result.ReturnValue)); 
}

this is the code that Linq is generating the stored proc inserts into a temp table then selects * from the table at the end of the proc

Answer 1

When you add a stored proc to the design, if you don't drag it onto an entity, it creates it the default way, which is to return the integer result of the proc. If you've done any select statements in that procedure, you need to drop the procedure on top of an entity in the designer, instead of into the method pane, this will allow the designer to generate the appropriate method that returns an IQueryable of your entity type, instead of int.

Answer 2

Either the result of your stored procedure should exactly match an existing table of your LINQ model or you should add both the stored procedure and the resulting element type to your dbml file. Usually the designer generates this for you if you drop your stored procedure anywhere in the designer (not on a table).

You should take a look at the dbml file to see what is happening. As an example, here's a part of a dbml file in my current project:

<Function Name="uspGetDepartments">
  <ElementType Name="uspGetDepartmentsResult">
    <Column Name="ParentDepartmentId" Type="System.Int32" DbType="Int"
            CanBeNull="true" />
    <Column Name="DepartmentId" Type="System.Int32" DbType="Int" CanBeNull="true" />
    <Column Name="DepartmentName" Type="System.String" DbType="NVarChar(200)"
            CanBeNull="true" />
  </ElementType>
</Function>

This means I have a stored procedure named uspGetDepartments that returns objects of type uspGetDepartmentsResult. Taking a look at the corresponding designer.cs file, I see:

[global::System.Data.Linq.Mapping.FunctionAttribute()]
public ISingleResult<uspGetDepartmentsResult> uspGetDepartments()
{
    IExecuteResult result = this.ExecuteMethodCall(
        this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
    return ((ISingleResult<uspGetDepartmentsResult>)(result.ReturnValue));
}

and

public partial class uspGetDepartmentsResult
{
    private System.Nullable<int> _ParentDepartmentId;
    private System.Nullable<int> _DepartmentId;
    private string _DepartmentName;

    public uspGetDepartmentsResult() {}

    [global::System.Data.Linq.Mapping.ColumnAttribute(
        Storage="_ParentDepartmentId", DbType="Int")]
    public System.Nullable<int> ParentDepartmentId
    {
        get { return this._ParentDepartmentId; }
        set
        {
            if ((this._ParentDepartmentId != value))
            {
                this._ParentDepartmentId = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(
        Storage="_DepartmentId", DbType="Int")]
    public System.Nullable<int> DepartmentId
    { ... }

    [global::System.Data.Linq.Mapping.ColumnAttribute(
        Storage="_DepartmentName", DbType="NVarChar(200)")]
    public string DepartmentName
    { ... }
}

Answer 3

I took the simple approach in the end

Stripped the stored proc to just return the empty temp table

dropped it into linq and then add the code than ran before the select command

not the best way but its only a small project

Thanks for your help guys

Answer 4

Try adding a log to your DataContext : http://www.davidhayden.com/blog/dave/archive/2007/08/17/DataContextLogLoggingLINQToSQLOutputConsoleDebuggerOuputWindow.aspx