none
How to call table valued UDF with parameter from EF? RRS feed

  • Question

  • Hi everybody,

    For calling stored procedure I am using the following syntax

     SqlParameter locationPar = new SqlParameter("@Location", SqlDbType.Char, 10);
    
                locationPar.Value = guest.ModSp;
    
               _siriusContext.CoreContext.ExecuteStoreCommand(@"EXECUTE [dbo].[siriussp_CreateGuestActivityRecord] 
       @ActivID = @ActivID, @GuestNo = @GuestNo, @SiteNo = @SiteNo, @ActivType = @ActivType, @TagLine = @TagLine, @RefNo = @RefNo
      ,@Details = @Details, @Dt = @Dt, @Person = @Person, @Location = @Location", 
      activityIdPar, guestNoPar, siteNoPar, activTypePar, tagLinePar, refNoPar, detailsPar, dtPar, personPar, locationPar);


    Now, instead of SP I created an inline table valued function called 

    ALTER function [dbo].[siriusfn_GetTemplateItems] 
       (@i_tmpnm_id int)
    returns table

    So, how should I call that function with parameter? I also would like to add ORDER BY to the result of the function.

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Thursday, October 6, 2016 11:25 AM

Answers

  • Yes, I later figured this out. This is my call:

    SqlParameter templateId = new SqlParameter("@i_tmpnm_id", SqlDbType.Int);
                templateId.Value = id;
                List<TemplateItems> result = _siriusContext.CoreContext.ExecuteStoreQuery<TemplateItems>
                    (@"select * from dbo.siriusfn_GetTemplateItems(@i_tmpnm_id) ORDER BY Department, Category, Item", templateId).ToList();
                return result;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Naomi N Friday, October 7, 2016 1:54 PM
    Friday, October 7, 2016 1:51 PM

All replies

  • I can probably use the same syntax and embed parameter inside the query, which is not really recommended, e.g.

    ExecuteStoreQuery<ResultType>(String.Format("select * from dbo.myFunc(@param = {0}) order by ...", id))

    Does it look like a valid approach or there is something better?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, October 6, 2016 11:36 AM
  • Well, that syntax didn't work. I can embed the parameter directly in that query, but how save it is? E.g. just use

    ExecuteStoreQuery<ResultType>(String.Format("select * from dbo.myFunc({0}) order by ...", id))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, October 6, 2016 8:48 PM
  • Like this

                    var pID = new SqlParameter("@id", 1);
                    var name = db.Database.SqlQuery<string>("SELECT FirstName FROM [dbo].[ufnGetContactInformation] (@id)",pID ).First();
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, October 6, 2016 10:03 PM
  • Yes, I later figured this out. This is my call:

    SqlParameter templateId = new SqlParameter("@i_tmpnm_id", SqlDbType.Int);
                templateId.Value = id;
                List<TemplateItems> result = _siriusContext.CoreContext.ExecuteStoreQuery<TemplateItems>
                    (@"select * from dbo.siriusfn_GetTemplateItems(@i_tmpnm_id) ORDER BY Department, Category, Item", templateId).ToList();
                return result;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Naomi N Friday, October 7, 2016 1:54 PM
    Friday, October 7, 2016 1:51 PM