Formatting of decimals in dynamic LINQ to Entities "where" clause
<p>I'm using LINQ to Entities, and I'm creating a generic class that can tag on dynamic &quot;Where&quot; clauses to queries based on the fields the user wants to search on in the app.</p>
<p>This could be used on <strong>any</strong> type of Entity in my database, so I'm using string-based Where clauses.</p>
<p>By using System.Linq.Dynamic, I've got some basic examples working, e.g.</p>
<p>String field contains:</p>
<pre><code>var data = db.Products;
var query = String.Format(&quot;{0}.Contains(@0)&quot;, filter.property);
data = data.Where(query, filter.value);
</code></pre>
<p>Number field greater than:</p>
<pre><code>var data = db.Products;
var query = String.Format(&quot;{0} &gt; @0&quot;, filter.property);
data = data.Where(query, filter.value);
</code></pre>
<p>These examples both work fine.</p>
<p>I now need to create a decimal &quot;equals to&quot;, but the issue is that the currency value in the database might be 10.3125, but it's displayed in the app UI as 10.31, so if a user wants to restrict where the price is equal to &quot;10.31&quot;, no results will be returned.  What I need to do is perform a comparison by restricting the number of decimal places to two when querying the database, and comparing that to the user's entered decimal value, something like this:</p>
<pre><code>var data = db.Products;
var query = String.Format(&quot;Decimal.Round({0}, 2).Equals(@0)&quot;, filter.property);
data = data.Where(query, filter.value);
</code></pre>
<p>Where &quot;filter.property&quot; is the column/field name, and &quot;filter.value&quot; contains the decimal value.</p>
<p>However, this throws a runtime error:</p>
<blockquote>
<p>An exception of type 'System.ArgumentException' occurred in System.Core.dll but was not handled in user code</p>
<p>Additional information: Argument types do not match</p>
</blockquote>
<p>I can't seem to find many decent examples of string-based LINQ Where clauses involving decimal numbers, or formatting of numbers.</p>
<p>Are there any suggestions on how to get this working? As a last resort, I could bring back all the data from the table, format the decimal numbers appropriately in memory, then finally add the Where clauses to the in-memory result set, but that would be a lot heavier on resources.  Surely there's a way to do this dynamically with LINQ to Entities?</p>
<p>Thanks in advance.</p>