title: Implementing DateTime.ToString(string) with Linq to Entities
body: <p>Is there a way around Linq to Entities inability to use the DateTime.ToString(string) method? Using the method below throws the exception <code>LINQ to Entities does not recognize the method 'System.DateTime ToString(System.String)' method, and this method cannot be translated into a store expression.</code>, which is to be expected as it cannot be converted into sql. </p>
  
  <pre><code>protected override bool TryGetEntitiesByKey(IEnumerable&lt;string&gt; keys, out IEnumerable&lt;Trade&gt; entities)
  {
    return this.TryLoadBase(x =&gt; keys.ToList().Contains(x.date.ToString("yyyyMMdd"), out entities);
  }
  </code></pre>
  
  <p>The code in the generic provider, which is what uses the func to filter the results from the db, is below.</p>
  
  <pre><code>protected virtual bool TryLoadBase(Expression&lt;Func&lt;TEntity, bool&gt;&gt; filter, out IEnumerable&lt;TEntity&gt; entities)
  {
    bool loaded = true;
    try
    {
      using (var db = this.dbContext)
      {
        entities = db.Set&lt;TEntity&gt;().Where(filter).ToList();
      }
    }
    catch (Exception ex) // Exception caught here
    {
      loaded = false;
      entities = new List&lt;TEntity&gt;();
    }
    return loaded;
  }
  </code></pre>
  
  <p>After getting the exception stated earlier, I tried replacing the date.ToString("yyyyMMdd") with this:</p>
  
  <pre><code>SqlFunctions.StringConvert((double)notional.date.Year).Trim() +
  SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double)notional.date.Month).Trim().Length) + SqlFunctions.StringConvert((double)notional.date.Month).Trim() +
  SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double)notional.date.Day).Trim().Length) + SqlFunctions.StringConvert((double)notional.date.Day).Trim()
  </code></pre>
  
  <p>Whilst this works, it has to be inline since otherwise you get the same message about LINQ to Entities not recognising the method. Which means it's a lot harder to read, and can't be reused by anything else.</p>
  
  <p>After looking around I found this question: (<a href="https://stackoverflow.com/questions/27324641/reusable-calculations-for-linq-projections-in-entity-framework-code-first">Reusable Calculations For LINQ Projections In Entity Framework</a>) but am reluctant to include any external packages simply to solve this one issue.</p>
  
  <p>There are also the answers on <a href="https://stackoverflow.com/questions/5370402/entity-framework-4-linq-how-to-convert-from-datetime-to-string-in-a-query">Entity Framework 4 / Linq: How to convert from DateTime to string in a query?</a>, but as far as I'm aware using AsEnumerable() like in the accepted answer would mean enumerating the db set into memory, which I cannot do due to memory limitations. And I couldn't get the second answer to work properly, and it would again suffer from the issues of the SqlFunctions mess above.</p>
  
  <p>If this simply isn't possible, would anybody be able to offer a cleaner alternative to the SqlFunctions block?</p>