title: Linq to SQL translation Failing as well as Local Evaluation
body: <p>Okay, so I have the following linq to sql query:</p>
  
  <pre><code>var baseQuery = _context.AuditTransaction
      .Include(at =&gt; at.AuditEntityEntries)
      .ThenInclude(aee =&gt; aee.AuditPropertyEntries)
      .Where(at =&gt; auditTransactionFilter.Id == 0 || at.Id == auditTransactionFilter.Id)
      .Where(at =&gt;
          string.IsNullOrWhiteSpace(auditTransactionFilter.UserName) ||
          at.UserName == auditTransactionFilter.UserName);
  
  
  return baseQuery
      .Where(at =&gt; at.AuditEntityEntries.Any() || at.AuditEntityEntries
          .Any(ataee =&gt; auditTransactionFilter.AuditEntityEntries
              .Any(atfaee =&gt;
                  (atfaee.Id == 0 || ataee.Id == atfaee.Id) &amp;&amp;
                  (string.IsNullOrWhiteSpace(atfaee.TableName) ||
                      ataee.TableName.ToLower().Contains(atfaee.TableName.ToLower()))
                  &amp;&amp;
                  (string.IsNullOrWhiteSpace(atfaee.KeyValues) ||
                      ataee.KeyValues.ToLower().Contains(atfaee.KeyValues.ToLower()))
                  &amp;&amp; (atfaee.AuditPropertyEntries.Any() || ataee.AuditPropertyEntries
                      .Any(atape =&gt; atfaee.AuditPropertyEntries
                          .Any(atfape =&gt;
                              (atfape.Id == 0 || atape.Id == atfape.Id) &amp;&amp;
                              (string.IsNullOrWhiteSpace(atfape.PropertyName) ||
                                  atape.PropertyName == atfape.PropertyName)
                              &amp;&amp;
                              (string.IsNullOrWhiteSpace(atfape.AuditType.ToString()) ||
                                  atape.AuditType == atfape.AuditType)
                              &amp;&amp;
                              (atfape.PropertyValues == null ||
                                  atape.PropertyValues.Contains(atfape.PropertyValues))
                          ))
                      )
              )
  
          ));
  </code></pre>
  
  <p>For whatever reason, when it runs, i get hundreds of warnings that various parts of it can't be translated and will be evaluated locally.  However, the query runs and does not properly filter out non-matches: returning all records in the DB.</p>
  
  <p>However, when I return baseQuery.ToList().Where(...) the query runs perfectly.</p>
  
  <p>Update: Even a simple query such as:</p>
  
  <pre><code>return baseQuery
       .Where(at =&gt; at.AuditEntityEntries.Any(ataee =&gt; auditTransactionFilter.AuditEntityEntries.Any(atfaee =&gt; atfaee.TableName == ataee.TableName)));
  </code></pre>
  
  <p>returns errors like this:</p>
  
  <pre><code>warn: Microsoft.EntityFrameworkCore.Query[20500]
        The LINQ expression 'where ([atfaee].TableName == [ataee].TableName)' could not be translated and will be evaluated locally.
  warn: Microsoft.EntityFrameworkCore.Query[20500]
        The LINQ expression 'Any()' could not be translated and will be evaluated locally.
  warn: Microsoft.EntityFrameworkCore.Query[20500]
        The LINQ expression 'where {from AuditEntityEntry atfaee in __auditTransactionFilter_AuditEntityEntries_0 where ([atfaee].TableName == [ataee].TableName) select [atfaee] =&gt; Any()}' could not be translated and will be evaluated locally.
  warn: Microsoft.EntityFrameworkCore.Query[20500]
        The LINQ expression 'where ([atfaee].TableName == [ataee].TableName)' could not be translated and will be evaluated locally.
  warn: Microsoft.EntityFrameworkCore.Query[20500]
        The LINQ expression 'Any()' could not be translated and will be evaluated locally.
  warn: Microsoft.EntityFrameworkCore.Query[20500]
        The LINQ expression 'where  ?= (Property([at], "Id") == Property([ataee], "AuditTransactionId")) =?' could not be translated and will be evaluated locally.
  warn: Microsoft.EntityFrameworkCore.Query[20500]
  </code></pre>
  
  <p>What am I doing wrong here?</p>
  
  <p>Please help.</p>