Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you explicitly want to use the LINQ Dynamic Query Library then my answer isn't going to be what you want, but if you want your desired behaviour and you're happy to use regular LINQ then I think I can help.</p> <p>Essentially I've created an <code>EntryGrouper</code> class that handles the logic of grouping by the selected values in the dropdown lists and I've assumed that the variables <code>section</code>, <code>page</code> &amp; <code>module</code> hold those values. I've also assumed that <code>ObjectContext.OmniturePageModules</code> is an enumerable of type <code>Entry</code>.</p> <p>So your LINQ query now becomes these two:</p> <pre><code>var entries = (from entry in ObjectContext.OmniturePageModules where entry.StartOfWeek &gt;= startDate &amp;&amp; entry.StartOfWeek &lt;= endDate &amp;&amp; (section == "Total" || section == "All" || entry.Section == section) &amp;&amp; (page == "Total" || page == "All" || entry.Page == page) &amp;&amp; (module == "Total" || module == "All" || entry.Module == module) select entry).ToArray(); // Force query execution var grouping = from entry in entries let grouper = new EntryGrouper(entry, section, page, module) group entry by grouper into entryGroup select new { SeriesName = entryGroup.Key.SeriesName, Week = entryGroup.Key.StartOfWeek, Clicks = entryGroup.Sum(p =&gt; p.Clicks), }; </code></pre> <p>The first query is used to force a simple select query on the database and return only the records that you want to group. Generally <code>group by</code> queries call the database multiple times so querying in this way is usually much faster.</p> <p>The second query groups the results of the first query by creating instances of the <code>EntryGrouper</code> class as the grouping key.</p> <p>I've included a <code>SeriesName</code> property in the <code>EntryGrouper</code> class so that all of the grouping logic is neatly defined in one place.</p> <p>Now, the <code>EntryGrouper</code> class is quite large as, to allow grouping to work, it needs to have properties for <code>StartOfWeek</code>, <code>Section</code>, <code>Page</code> &amp; <code>Module</code>, and contain overloads of the <code>Equals</code> &amp; <code>GetHashCode</code> methods, and implement the <code>IEquatable&lt;Entry&gt;</code> interface.</p> <p>Here it is:</p> <pre><code>public class EntryGrouper : IEquatable&lt;Entry&gt; { private Entry _entry; private string _section; private string _page; private string _module; public EntryGrouper(Entry entry, string section, string page, string module) { _entry = entry; _section = section; _page = page; _module = module; } public string SeriesName { get { return String.Format("{0}:{1}:{2}", this.Section, this.Page, this.Module); } } public DateTime StartOfWeek { get { return _entry.StartOfWeek; } } public string Section { get { if (_section == "Total" || _section == "All") return _section; return _entry.Section; } } public string Page { get { if (_page == "Total" || _page == "All") return _page; return _entry.Page; } } public string Module { get { if (_module == "Total" || _module == "All") return _module; return _entry.Module; } } public override bool Equals(object other) { if (other is Entry) return this.Equals((Entry)other); return false; } public bool Equals(Entry other) { if (other == null) return false; if (!EqualityComparer&lt;DateTime&gt;.Default.Equals(this.StartOfWeek, other.StartOfWeek)) return false; if (!EqualityComparer&lt;string&gt;.Default.Equals(this.Section, other.Section)) return false; if (!EqualityComparer&lt;string&gt;.Default.Equals(this.Page, other.Page)) return false; if (!EqualityComparer&lt;string&gt;.Default.Equals(this.Module, other.Module)) return false; return true; } public override int GetHashCode() { var hash = 0; hash ^= EqualityComparer&lt;DateTime&gt;.Default.GetHashCode(this.StartOfWeek); hash ^= EqualityComparer&lt;string&gt;.Default.GetHashCode(this.Section); hash ^= EqualityComparer&lt;string&gt;.Default.GetHashCode(this.Page); hash ^= EqualityComparer&lt;string&gt;.Default.GetHashCode(this.Module); return hash; } public override string ToString() { var template = "{{ StartOfWeek = {0}, Section = {1}, Page = {2}, Module = {3} }}"; return String.Format(template, this.StartOfWeek, this.Section, this.Page, this.Module); } } </code></pre> <p>The grouping logic of this class looks simply like this:</p> <pre><code>if (_page == "Total" || _page == "All") return _page; return _entry.Page; </code></pre> <p>If I have misunderstood how you the dropdown values turn grouping on and off then you should just need to change these methods, but the crux of this code is that when grouping is on it should return a group value based on the value in the entry and otherwise it should return a common value for all entries. If the value is common for all entries then it logically only creates a single group which is the same as not grouping at all.</p> <p>If you have more dropdowns that you're grouping by then you need to add more properties to the <code>EntryGrouper</code> class. Don't forget to add these new properties to the <code>Equals</code> &amp; <code>GetHashCode</code> methods too.</p> <p>This logic, therefore, represents the dynamic grouping that you wanted. Please let me know if I've helped or if you need more detail.</p> <p>Enjoy!</p>
    singulars
    1. This table or related slice is empty.
    1. PODynamic LINQ GroupBy Multiple Columns
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USEnigmativity
    1. USEnigmativity
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODynamic LINQ GroupBy Multiple Columns
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you very much for your extensive answer. I'll try this tomorrow and let you know if it works for me -- a quick glance over it is encouraging.
      singulars
      1. PO
      1. USDaniel Coffman
    2. COThis doesn't seem to group by StartOfWeek for some reason. I had to change the grouping code for each column to if( _section == "All" ) return _entry.Section; return _section;
      singulars
      1. PO
      1. USDaniel Coffman
    3. CO@`Daniel Coffman` - I don't know why it hasn't grouped by `StartOfWeek`, it should have. I rechecked the code and the `Equals` & `GetHashCode` methods use the `StartOfWeek` value. Give me a yell if you'd like me to look into it further. I had expected the grouping code for each column would probably need to be "tweaked" a bit for your needs.
      singulars
      1. PO
      1. USEnigmativity
 

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