I'm looking for best way to write a query with LINQ or an expression tree to return a dynamic result according to dynamic input. For example, consider this pseudocode:
CREATE PROCEDURE Test @Input NVARCHAR(50) AS BEGIN DECLARE @Query NVARCHAR(100); SET @Query=N'SELECT ' + @Input + ' FROM MyTable' EXEC @Query END
What is best way to:
Consider every dynamic Query does not include
SELECT statement.for example I recently write a dynamic
PIVOTquery.So I can't use Dynamic LINQ
Dynamic Linq? Probably not
I was going to suggest Dynamic Linq but as your "Edit" states that won't do for you.
In that case I'd say the answer to your question is "There is no best way".
Is Linq the best choice for this?
Your question is asking for a Linq, specifically a Linq to Sql, implementation of how to achieve your dynamic queries.
But consider what Linq to Sql is for. The main benefits that jump to mind of Linq to SQL are:
Now, if you're doing "Select" statements and always returning an actual mapped table, then there's no reason you can't use the dynamic Linq library.
But if you're returning arbitrary non mapped results, there's no point in using LINQ, indeed it won't let you.
DataContext class does have an
ExecuteQuery<T> method, but you unfortunately you cannot return specify dynamic as the T :-(
Linq to SQL and stored procedures
Lastly, LINQ to SQL can map to stored procedures. So you could have a stored procedure that take a query as an argument, like your example. However, I believe it's got the same limitations of
ExecuteQuery in terms of the types in can result, though don't hold me to that.
I'd have a look at that as your final option, and explore the sproc in the Linq to SQL designer returning either anonymous, dynamic, or a mapped type.