SQL Server Table-valued Functions

Executing a table-valued function

To connect an SQL Table UDF, declare a C# function with the correct signature and add [Function(RequiresAlias = true)] and [Tuple] attributes. Note that the function returns an entity, as expected.

[Function("udfProductInYear", RequiresAlias = true), Tuple]
public static ProductInYear GetProductInYear(int year)
{
    // the method is not supported on .NET platform.
    // It's supported in SQL Server only
    throw new NotSupportedException();
}

public void CallTableUDF()
{
    var query = DbContext.Set<ProductInYear>()
        .Query(() => SelectAll(GetProductInYear(2017)));

    foreach (var orderNetValue in query.Take(3))
        Console.WriteLine((orderNetValue.ProductName, orderNetValue.ModelYear, orderNetValue.ListPrice));
}

inside a complex statement:

var query = DbContext.Set<ProductInYear>()
                .Query(() => SelectAll(GetProductInYear(2017)))
                .OrderBy(p => p.ProductName);

foreach (var orderNetValue in query.Take(3))
    Console.WriteLine((orderNetValue.ProductName, orderNetValue.ModelYear, orderNetValue.ListPrice));

< BACK | HOME