UDT as parameter in EF4 request

Question

I know that EF4 does not support custom table types (yet). I need to write a query that takes a list of pairs <product_code, quantity> and returns a set of records with product_code and price for each passed product_code based on quantity . What is my best option with EF4 to simulate this? The calculation in the database to get the price is quite complicated, and there are many products, which means that most of the actions must be performed on the server side. (For example, I cannot first get a complete list of prices from the server, and then filter on the products I need on the client. I also can not use the amount for calculation on the client, which must be transferred to the server and processed there). Any thoughts are welcome.

Answer 1

I think you basically answered your question. The calculation should be done on the database server, and you just want to get the result, right? If you are using SQL Server 2008, you can create a stored procedure that accepts a parameter that is evaluated by the table . Now you can call this procedure either directly using ADO.NET or using EF and context.ExecuteStoreQuery , where you still pass the DataTable to SqlParameter using SqlDbType.Structured .

If you are not using SQL Server 2008, you need a stored procedure with one large nvarchar parameter that passes the entire list as a comma-delimited string. Your stored procedure first parses this list into a temporary table, and then processes the calculations in the same way as with the table parameter.