Today I need to retrieve data from Entity Framework using stored procedure, and passing input values from .NET collection through the table-valued parameter. As for technologies that will be used here, I wrote in these posts:
- Using Table-Valued Parameters in SQL Server Stored Procedures – how to create SQL Server type and stored procedure;
- Calling SQL Server Stored Procedure With Table-Valued Parameter in C# – calling stored procedure via ADO.NET;
- Code First. Executing Stored Procedures – calling stored procedures via DbContext.Database.SqlClient().
As stated in MSDN article Table-Valued Parameters, table-valued parameter can be populated from:
- DataTable
- DbDataReader
- IEnumerable
If you have a .NET collection with parameters, you could transform it to IEnumerable. Here is a good article on C# Corner Passing Table Valued Parameter to Stored Procedure: Part 2 that shows how to make this conversion.
Let’s start to write the code.
1. Create SQL Server type UserType to store the row of a table-valued parameter
|
1 2 3 4 5 |
CREATE TYPE [dbo].[SqlUserType] AS TABLE ( UserId int NOT NULL, AccessTypeId smallint NOT NULL ) |
2. Create stored procedure with a readonly parameter of type UserType
|
1 2 3 4 5 6 7 |
CREATE PROCEDURE [dbo].[ListUsers] @UserList [dbo].[SqlUserType] READONLY AS BEGIN ... END |
3. Create .NET class UserEntry to store the row of a table-valued parameter
|
1 2 3 4 5 |
public class UserEntry { public int UserId { get; set; } public short AccessTypeId { get; set; } } |
4. Create .NET class UserParamCollection that must be inherited from the preceding .NET class and also implements IEnumerable
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
public class UserParamCollection : List<UserEntry>, IEnumerable<SqlDataRecord> { IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator() { var sqlRow = new SqlDataRecord( new SqlMetaData("UserId", SqlDbType.Int), new SqlMetaData("AccessTypeId", SqlDbType.SmallInt)); foreach (UserEntry entry in this) { sqlRow.SetInt32(0, entry.UserId); sqlRow.SetInt16(1, entry.AccessTypeId); yield return sqlRow; } } } |
5. Convert List<UserEnty> to a newly created class UserParamCollection
|
1 2 |
UserParamCollection sqlUserList = new UserParamCollection(); sqlUserList.AddRange(userList); |
6. Create SqlParameter
|
1 2 3 4 5 |
SqlParameter paramUserList = new SqlParameter("@UserList", SqlDbType.Structured) { TypeName = "SqlUserType", Value = sqlUserList }; |
Here you need to specify parameter type SqlDbType.Structured. TypeName must be the same as SQL Server type defined in step 1.
7. Call stored procedure and pass the preceding SqlParameter
|
1 2 |
var resultSet = context.Database.SqlQuery<T>("[dbo].[ListUsers] @UserList", paramUserList); var resultCollection = resultSet as IEnumerable<T>; |
You must place the parameter name of the SQL Server stored procedure right after the name of the stored procedure as following: “[dbo].[ListUsers] @UserList”. If you omit it, you’ll get an error:
Procedure or function 'ListUsers' expects parameter '@UserList', which was not supplied.
Because SqlQuery() returns a collection of type System.Data.Entity.Infrastructure.DbRawSqlQuery, it would better to cast it to IEnumerable to conform to application interfaces.