Databases
StackOverflow2013
Postsdbo
POFiltering Entity Framework results on SQL Server
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFiltering Entity Framework results on SQL Server
    primarykey
    data
    text
    <p>First up: we are <em>not</em> doing TPH (table per hierarchy), that would make this a lot simpler.</p> <p>I have about 20 POCOs that all have similar properties in some cases. The similar properties I care about are <code>___.CreatedDate</code> and <code>___.UpdatedDate</code>. For some of the POCOs, <code>UpdatedDate</code> doesn't make sense, so they don't have that property. <code>CreatedDate</code> will always exist, <code>UpdatedDate</code> may be null. Sometimes there's a third field as well.</p> <p>The query to retrieve objects from the database always looks the same, regardless of which of the 20 POCOs I'm querying. However, the query was duplicated 20 times, one for each type of the object. I was able to break out the retrieve part to an extension method (off of <code>IDbSet&lt;T&gt;</code>) to do the initial lookup and joins, leaving the date range filtering as an exercise to the consumer. I'm now wanting to consolidate 20 nearly-identical looking date range filters into one, but running into problems with query comprehension.</p> <p>The date filtering logic, per POCO, is that UpdatedDate should be checked and its value compared to a threshold. If UpdatedDate was null (or the property didn't exist), then CreatedDate should be used instead.</p> <p>&lt;/background&gt;</p> <p>I started by creating a static property getter on each POCO, which I named "DateFields". It's typed as an <code>IEnumerable&lt;Expression&lt;Func&lt;T, DateTime?&gt;&gt;&gt;</code> that looks like this:</p> <pre><code>get { yield return x =&gt; x.UpdatedDate; yield return x =&gt; x.CreatedDate; yield return x =&gt; x.Date; } </code></pre> <p>These fields are returned in the order I want them checked, and are unique to each POCO.</p> <p>I then created a predicate to check each value against a high and low range:</p> <pre><code>public static bool DatesBetween&lt;T&gt; (T value, IEnumerable&lt;Expression&lt;Func&lt;T, DateTime?&gt;&gt;&gt; dates, DateTime? dateFrom, DateTime? dateTo) { var firstDate = dates .Select(expression =&gt; expression.Compile()) .FirstOrDefault(func =&gt; func(value) != null); if (firstDate == null) return false; var minDate = dateFrom.GetValueOrDefault(SqlDateTime.MinValue.Value); var maxDate = dateTo.GetValueOrDefault(SqlDateTime.MaxValue.Value); var actualDate = firstDate(value); return (minDate &lt;= actualDate &amp;&amp; actualDate &lt;= maxDate); } </code></pre> <p>However, as expected, when I try and use this in my <code>IQueryable</code>, I get a runtime exception, because there's no translation to DatesBetween in SQL. I was hoping that by keeping the expression tree, I could ... I dunno ... keep EF happy. The whole thing is used like this:</p> <pre><code>return Entities .GetEntitiesBySomeField(field := "magic value") .Where(entity =&gt; RepositoryExtensions.DatesBetween(entity, MyPOCO.MyDates, dateFrom, dateTo)); </code></pre> <p>Is what I'm asking clear, and is there a way to accomplish this kind of generic filtering without doing an AsEnumerable () (which does work, but doesn't accomplish my goal of filtering on the DB).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser7116
    1. USBryan Boettcher
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFiltering Entity Framework results on SQL Server
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFiltering Entity Framework results on SQL Server
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload