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

Question

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.

May be this information will be helpful entityframeworkcore.com/knowledge-base/57056492/… — Karan
– Karan, Commented Sep 7, 2020 at 5:47
We are getting return as IQueryable in normal case. But its not working when the input parameter is Tvp. For us the input parameter is a tvp(for eg;- IEnumerable<int>). We need the output as IQueryable. — Golda
– Golda, Commented Sep 7, 2020 at 14:27
Try this information: c-sharpcorner.com/uploadfile/78607b/…. — MikeJ
– MikeJ, Commented Sep 7, 2020 at 18:11
@MikeJ, not related. Golda is asking about table valued function's case. — nawfal
– nawfal, Commented Sep 10, 2020 at 3:59
I think the simple truth is that ObjectParameter can never be a TVP. — Gert Arnold
– Gert Arnold, Commented Sep 11, 2020 at 10:24

nerlijma · Accepted Answer · 2020-09-07 22:16:37Z

11

+250

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();
    }
}

edited Sep 7, 2020 at 22:16

answered Sep 7, 2020 at 17:09

nerlijma

9751 gold badge9 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

nerlijma Over a year ago

@golda, is this what you are looking for? Maybe you can clarify a bit if this answer won't help you.

Ivan Stoev Over a year ago

This applies to EF Core, while the error message and the structure of the OP code indicates EF6.

nerlijma Over a year ago

@IvanStoev, I edited the answer for a similar answer in EF6, however, not exactly as needed as you can't get IQueryable from SqlQuery.

nawfal Over a year ago

Thanks @nerlijma. Good to know it can be done in EFCore.

Ivan Stoev Over a year ago

EF6 and EF Core are completely different systems. The question is specifically targeting EF6 and requesting server side IQueryable result, which this answer does not satisfy. If you can't satisfy the requirements, the correct answer is simply "Not possible".

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related