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.
Step 1 − Select the Console Application from the middle pane and enter TableValuedFunctionDemo in the name field. Step 2 − In Server explorer right-click on your database. Step 3 − Select New Query and enter the following code in T-SQL editor to add a new table in your database.
Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine. You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement.
User-defined functions can't call a stored procedure, but can call an extended stored procedure. User-defined functions can't make use of dynamic SQL or temp tables. Table variables are allowed.
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();
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With