none
Unable to Add a SQL server Scalar User Defined Function to Entity Framework RRS feed

  • Question

  • For example, I have the following function

    I add it to entity framework (runtime Version v4.0.30319, Version 6.0.0.0)

    It is shown on Stored Procedure / Function, but is not shown on Function Imports and Complex Types of Model Browser

    get "does not contains..." error when I use var x = from x in db.ufn_LPA_Get_New_QuestionID() select x;

    Thanks!

    create FUNCTION [dbo].[ufn_LPA_Get_New_QuestionID]
    ()
    RETURNS [INT]
    AS

    BEGIN

    Return (Select (Max([QUESTIONS_ID]) + 1) QuestionID FROM [dbo].[FTP_QUESTIONS_VW] )

    END

    Monday, June 19, 2017 7:18 PM

Answers

  • Cole Wu,

    Thank you so much.

    Your solution works well,

    This function is scalar function, it returns on a singal value.

    Other functions I use, table value functions, show three places in Model Broswer; Stored prodcedures / Functions, Function Imports, and Complex Types.

    I expect it shows on Stored prodcedures/Functions and Function Imports of Model Browser, and able to do like below

    var r = (from x in db.ufn_LPA_Get_New_QuestionID() select x).FirstOrDefault();

    Thanks!

    Maybe you should mark his reply as the 'answer' to your question?
    • Marked as answer by klouapple Wednesday, June 21, 2017 1:10 PM
    Tuesday, June 20, 2017 7:13 PM

All replies

  • Hi klouapple,

    Based on your description, I create a demo by using entity framework code first, we could use DbContext.Database.SqlQuery method to achieve it. like this:

    using (var db = new EFDemoContext())
    {
       int result = 0;
       try
       {
          result = db.Database.SqlQuery<int>("select [dbo].[ufn_LPA_Get_New_QuestionID]()").FirstOrDefault();
       }
       catch (Exception)
       { }
       Console.WriteLine("result = {0}", result);
       Console.ReadKey();
    }

    If you use Database first, please refer to the following link.

    https://msdn.microsoft.com/en-us/data/hh859577

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 20, 2017 8:48 AM
    Moderator
  • Cole Wu,

    Thank you so much.

    Your solution works well,

    This function is scalar function, it returns on a singal value.

    Other functions I use, table value functions, show three places in Model Broswer; Stored prodcedures / Functions, Function Imports, and Complex Types.

    I expect it shows on Stored prodcedures/Functions and Function Imports of Model Browser, and able to do like below

    var r = (from x in db.ufn_LPA_Get_New_QuestionID() select x).FirstOrDefault();

    Thanks!

    Tuesday, June 20, 2017 12:59 PM
  • Cole Wu,

    Thank you so much.

    Your solution works well,

    This function is scalar function, it returns on a singal value.

    Other functions I use, table value functions, show three places in Model Broswer; Stored prodcedures / Functions, Function Imports, and Complex Types.

    I expect it shows on Stored prodcedures/Functions and Function Imports of Model Browser, and able to do like below

    var r = (from x in db.ufn_LPA_Get_New_QuestionID() select x).FirstOrDefault();

    Thanks!

    Maybe you should mark his reply as the 'answer' to your question?
    • Marked as answer by klouapple Wednesday, June 21, 2017 1:10 PM
    Tuesday, June 20, 2017 7:13 PM