Table Valued Function and Entity Framework
Here is a very good article on the newer features of Entity Framework that provide direct support for Table Valued UDFs. MSDN blog on Table-Valued Function Support in Entity Framework.
For greater depth, this article provides significant detail. EDM and store functions exposed in LINQ.
One of the great advantages of the recent support for Table-Valued UDFs involves support for Full-Text Search capabilities. Read more about that here: Full text search features involving database objects.
[Tested] using:
Install-Package EntityFramework.CodeFirstStoreFunctions
Declare a class for output result:
public class MyCustomObject
{
[Key]
public int Id { get; set; }
public int Rank { get; set; }
}
Create a method in your DbContext class
[DbFunction("MyContextType", "SearchSomething")]
public virtual IQueryable<MyCustomObject> SearchSomething(string keywords)
{
var keywordsParam = new ObjectParameter("keywords", typeof(string))
{
Value = keywords
};
return (this as IObjectContextAdapter).ObjectContext
.CreateQuery<MyCustomObject>(
"MyContextType.SearchSomething(@keywords)", keywordsParam);
}
Add
public DbSet<MyCustomObject> SearchResults { get; set; }
to your DbContext class
Add in the overriden OnModelCreating
method:
modelBuilder.Conventions
.Add(new CodeFirstStoreFunctions.FunctionsConvention<MyContextType>("dbo"));
And now you can call/join with a table values function like this:
CREATE FUNCTION SearchSomething
(
@keywords nvarchar(4000)
)
RETURNS TABLE
AS
RETURN
(SELECT KEY_TBL.RANK AS Rank, Id
FROM MyTable
LEFT JOIN freetexttable(MyTable , ([MyColumn1],[MyColumn2]), @keywords) AS KEY_TBL
ON MyTable.Id = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 0
)
GO