CODEWITHSUNDEEP
c#sql-serverentity-frameworkentity-framework-6table-valued-parameters
Golda
Golda

Reputation: 131

EF: Passing a table valued parameter to a user-defined function from C#

I have a user-defined function in SQL Server that accepts a TVP (table valued parameter) as parameter. In EF, how do I call such a function from C# ?

I tried using the method ObjectContext.CreateQuery<> but got the following error:

The parameter 'param' of function 'QueryByParam' is invalid. Parameters can only be of a type that can be converted to an Edm scalar type.

Also tried method ObjectContext.ExecuteStoreQuery<> and got the same error. It doesn't return an IQueryable anyway.

Sample code

[DbFunction(nameof(SampleDbContext), "QueryByParam")]
public IQueryable<SecurityQueryRow> QueryByParam(IEnumerable<ProfileType> profiles, bool isActive = false)
{
    DataTable dataTable = ....
    ObjectParameter profilesParam = new ObjectParameter("profileTypeIds", dataTable);

    ObjectParameter isActiveParam = new ObjectParameter("isActive ", isActive);

    return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<SecurityQueryRow>(
            string.Format("[{0}].[{1}](@profileTypeIds, @isActive)", GetType().Name, "QueryByParam"),
            profilesParam,
            isActiveParam);
}

The requirement is that we need an IQueryable back, not the consumed result.

Upvotes: 13

Views: 4806

Answers (1)

nerlijma
nerlijma

Reputation: 975

You can do it with Raw Sql en EF Core, Similar aproach in EF6, but you can't get an IQueryable. Both examples below.

Entity Framework Core

SQL type to use it as your list filter:

CREATE TYPE [dbo].[Table1Type] AS TABLE(
    [Id] [int] NULL,
    [Name] [nchar](10) NULL
)

SQL UDF:

CREATE FUNCTION [dbo].[Func1] 
(   
    @Ids Table1Type readonly
)
RETURNS TABLE 
AS
RETURN
(
    SELECT * from Table1 where id in (select Id from @Ids)
)

EF context:

public class MyContext : DbContext
{
    public DbSet<Table1> Table1 { get; set; }
}

DTO to match the sql Type (also same as table for simplicity):

public class Table1
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Example:

static void Main(string[] args)
{
    using (var context = new MyContext())
    {
        // Declare de Structure filter param
        var dt = new DataTable();

        DataTable table = new DataTable();
        table.Columns.Add(new DataColumn("Id", typeof(int)));
        table.Columns.Add(new DataColumn("Name", typeof(string)));
        DataRow row = table.NewRow();
        row["Id"] = 1;
        row["Name"] = "Item";
        table.Rows.Add(row);
        var param = new SqlParameter("@Ids", table) { TypeName = "dbo.Table1Type", SqlDbType = SqlDbType.Structured };

        IQueryable<Table1> query = context.Table1.FromSqlRaw("SELECT * FROM dbo.func1(@Ids)", param);
        var result = query.ToList();
    }
}

Entity Framework 6

You can't get an IQueryable, but you can linq to the resulting IEnumerable.

static void Main(string[] args)
{
    using (var context = new MyContext())
    {
        context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

        // Declare de Structure filter param
        var dt = new DataTable();

        DataTable table = new DataTable();
        table.Columns.Add(new DataColumn("Id", typeof(int)));
        table.Columns.Add(new DataColumn("Name", typeof(string)));
        DataRow row = table.NewRow();
        row["Id"] = 1;
        row["Name"] = "Item";
        table.Rows.Add(row);
        var param = new SqlParameter("@Ids", table) { TypeName = "dbo.Table1Type", SqlDbType = SqlDbType.Structured };

        var query = context.Table1.SqlQuery("SELECT * FROM dbo.func1(@Ids)", param);

        var result = query.ToList();
    }
}

Upvotes: 11

Related Questions