none
How can I run a Stored Procedure from LINQ? RRS feed

  • Question


  • Hi,

    I am trying to call a stored procedure on SQL Server 2008 which has my full-text index search using LINQtoSQL Object. The autogenerated code returns an "int" and I want an IQueriable<News> - this should return multiple rows. What needs to change below to get this to work?

    Code is as follows:-

    (Auto Generated)

    [

    Function(Name="dbo.SelectAllNewsBySearchCriteria")]

     

    public int SelectAllNewsBySearchCriteria([Parameter(Name="Keyword", DbType="VarChar(100)")] string keyword)

    {

     

    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), keyword);

     

    return ((int)(result.ReturnValue));

    }

    (My function Call)

     

    public static IEnumerable<New> SelectAllNewsBySearchCriteria(string Keyword)

    {

    data.DataAccessDataSetTableAdapters.

    NewsTableAdapter ta = new data.DataAccessDataSetTableAdapters.NewsTableAdapter();

     

    DataAccessLINQToSQLDataContext dc = new DataAccessLINQToSQLDataContext();

     

    var result = dc.SelectAllNewsBySearchCriteria("information technology");

     

    return result;

    }

    Can anyone Help?

    Thanks.

    john.




    • Moved by Bob Beauchemin Monday, February 1, 2010 3:14 PM Moved to a more appropriate group (From:.NET Framework inside SQL Server)
    Monday, February 1, 2010 11:31 AM

Answers

  • Solution:

    (Change Auto Generated Function to)

    [

    Function(Name = "dbo.SelectAllNewsBySearchCriteria", IsComposable = false)]

     

    public ISingleResult<New> SelectAllNewsBySearchCriteria([Parameter(Name="Keyword", DbType="VarChar(100)")] string keyword)

    {

     

    IExecuteResult objResult = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), keyword);

     

     

    ISingleResult<New> objresults = (ISingleResult<New>)objResult.ReturnValue;

     

    return objresults;

    }


    • Marked as answer by John Guilbert Tuesday, February 2, 2010 8:58 AM
    Tuesday, February 2, 2010 8:58 AM