none
Call SQL UDF Function RRS feed

  • Question

  • Hi,

    I added SQL Server UDF Functions to my entity framework report model.

    However, apparently because I'm on .Net 3.5 and not 4.0(? is this true) I cannot add these Functions using the 'Add Function Import' in the Model Browser. All I can add are Stored Procedures.

    How do I call these Functions using LINQ?

    Any help much appreciated! My function in sql server is called as below...

    SELECT dbo.GetName(@UserID)

    Thanks!

    Wednesday, March 7, 2012 5:03 PM

Answers

  • OK. In VS2010 I added a LINQ to SQL Class to my project.

    Then I dragged the required functions from Server Explorer window onto designer. (followed this... http://msdn.microsoft.com/en-us/library/bb384470.aspx)

    And added below code, which seems much simpler.

    public static bool CheckItemsMatch(int id, DateTime myDate)
    {
       using (ChecksDataContext checksContext = new ChecksDataContext())
       {
          var result = checksContext.CheckItems(id,myDate); // CheckItems is function
    
          return result.Value; // return bool
       }
    }

    And this works!
    Thanks

    • Marked as answer by obrienkev Thursday, March 8, 2012 10:52 AM
    Thursday, March 8, 2012 10:51 AM

All replies

  • Hi obrienkev,

    Welcome!

    You can refer here : How to: Call a User-Defined Function 

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 8, 2012 8:59 AM
    Moderator
  • Hi,

    I have tried the below. However, I do not know what to use as Type in ExecuteFunction<T>. I tried Boolean but gives an error...

    [System.Data.Linq.Mapping.Function(Name = "dbo.CheckItems", IsComposable = true)]
            [return: Parameter(DbType = "bit")]
            public bool CheckItemsMatch([Parameter(DbType = "bit")] string id, DateTime myDate)
            {
                global::System.Data.Objects.ObjectParameter idParameter; 
                global::System.Data.Objects.ObjectParameter myDateParameter; 
                if ((id != null)) 
                { 
                   idParameter = new global::System.Data.Objects.ObjectParameter("ID", this.id); 
                }
                if ((myDate != null)) 
                { 
                   myDateParameter = new global::System.Data.Objects.ObjectParameter("myDate", this.myDate); 
                }
    
                return (bool)base.ExecuteFunction<Boolean>("dbo.CheckItems", idParameter, myDateParameter);
            }
    Thanks
    Thursday, March 8, 2012 9:27 AM
  • OK. In VS2010 I added a LINQ to SQL Class to my project.

    Then I dragged the required functions from Server Explorer window onto designer. (followed this... http://msdn.microsoft.com/en-us/library/bb384470.aspx)

    And added below code, which seems much simpler.

    public static bool CheckItemsMatch(int id, DateTime myDate)
    {
       using (ChecksDataContext checksContext = new ChecksDataContext())
       {
          var result = checksContext.CheckItems(id,myDate); // CheckItems is function
    
          return result.Value; // return bool
       }
    }

    And this works!
    Thanks

    • Marked as answer by obrienkev Thursday, March 8, 2012 10:52 AM
    Thursday, March 8, 2012 10:51 AM