sql server - UDT as a parameter in EF4 query

Question

I know that EF4 does not support User Defined Table Types (yet). I need to write a query, that accepts a list of pairs <product_code, quantity> and returns a record set with product_code and price for each product_code passed, based on quantity. What is my best option with EF4 to model this? The calculation in the database to get the price are quite complex, and there are a lot of products, which means that most of the action should happen server-side. (For example I can't get full list of prices from server first and then filter to the products I need on the client. I also can't apply quantity to the calculation on the client, that has to be passed to server and processed there). Any thoughts are welcome.

Answer 1

I think you mostly answered your question. Computation must be done on the database server and you just want to get result, don't you? If you are using SQL Server 2008 you can create stored procedure which accepts table valued parameter. Now you can call this procedure either directly using ADO.NET or using EF and context.ExecuteStoreQuery where you still pass DataTable to SqlParameter with SqlDbType.Structured.

If you don't use SQL Server 2008 you need stored procedure with one big nvarchar parameter passing whole list as comma delimited string. Your stored procedure will first parse this list to temporary table and then process the computation in the same way as with table valued parameter.