Linq or Expression Tree are the best ways to construct dynamic queries with dynamic results.

c# expression-trees linq linq-to-sql


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:

@Input      NVARCHAR(50)
DECLARE @Query      NVARCHAR(100);
SET @Query=N'SELECT ' + @Input + ' FROM MyTable'

EXEC @Query

What is best way to:

  1. Code this using LINQ or an expression tree
  2. Call this as a stored procedure using LINQ to SQL


Consider every dynamic Query does not include SELECT statement.for example I recently write a dynamic PIVOTquery.So I can't use Dynamic LINQ

2/12/2016 7:10:28 PM

Accepted Answer

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:

  1. Strongly typed queries (catch errors at compile time)
  2. Strongly typed results. Meaning nice pre-written classes (in Linq to SQL's case, pre generated)

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.ExecuteQuery<T>(string query)

The 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.

5/23/2017 11:59:26 AM

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow