Dynamic Linq on Dapper dynamic collections - possible?

c# dapper linq

Question

We are investigating using LinQ to query an internal dynamic collection created by Dapper. The question is:

How to execute dynamic LinQ against the collection using Scott Guthrie dynamic linq (or some other technology if possible)? (http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library)

This is what we want to do (much simplified):

Use Dapper to return a dynamic collection (here called rows):

rows = conn.Query(“select ACCOUNT, UNIT, AMOUNT from myTable”);

If we use a “static” LinQ query there is no problem. So this works fine:

var result = rows.Where(w => w.AMOUNT > 0);

But we would like to write something similar to this using dynamic Linq:

var result = rows.Where("AMOUNT > 0");

But we can’t get this to work.

The error we get is:

No property or field ‘AMOUNT’ exists in type ‘Object’

(We have tried a lot of other syntax also – but cant get it to work)

Please note: We do not want to use dynamic SQL when Dapper requests data from the database (that is easy). We want to execute many small dynamic Linq statements on the collection that the Dapper query returns.

Can it be that ScottGu dynamic Linq only works with ‘LinQ to SQL’?

Is there some other alternative approach to achieve the same thing? (Performance is a key issue)

/Erik

Expert Answer

conn.Query("...")

returns an IEnumerable<dynamic>. However, "dynamic LINQ" pre-dates dynamic, and presumably nobody has updated it to work with dynamic; it could certainly be done, but it is work (and isn't trivial). Options:

  • use Query<T> for some T
  • make the required changes to "dynamic LINQ", and preferably make those changes available to the wider community

Popular Answer

I suspect that conn.Query(“select ACCOUNT, UNIT, AMOUNT from myTable”); returns IEnumerable<object>. In order for DLinq to work, you need to have IEnumerable<TheActualType>.

You can try this:

conn.Query<dynamic>("yourQueryString")
   .ToList()
 //.ToAnonymousList()
   .Where("AMOUNT > 0"); 

If that doesn't work, you could try and use ToAnonymousList, that tries to return the IList<TheActualType.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why