Databases
StackOverflow2013
Postsdbo
POCombine results after join and groupby with DataTable and List<Object>
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCombine results after join and groupby with DataTable and List<Object>
    primarykey
    data
    text
    <p>I'd like to combine the results of a LINQ join between a DataTable and a List.</p> <p>This works just fine:</p> <pre><code>var lpYear = ( from a in _ds.Tables[0].AsEnumerable() join b in LandingPages on a["OFFERINGKEY"].ToString() equals b.Code into c from d in c.DefaultIfEmpty() where DateTime.Parse(a["PURCHASEDATE"].ToString()) &gt;= DateTime.Parse("January 1, " + year) where DateTime.Parse(a["PURCHASEDATE"].ToString()) &gt;= DateTime.Parse("December 31, " + year) where LandingPages.Any(x =&gt; x.Code == a["OFFERINGKEY"].ToString()) orderby d.Title select new { title = d.Title, price = a["PRICE"] }).GroupBy(o =&gt; o.title) .Select(o =&gt; new { total = o.Sum(p =&gt; decimal.Parse(p.price.ToString())), count = o.Count(), title = o.Key } ); </code></pre> <p>And I end up with rows containing "<code>total | count | title</code>".</p> <p>What I'd like to do is add some more columns. For example, <code>LandingPage.URL</code> or <code>LandingPage.Code</code>. I've tried like this, but it doesn't work:</p> <pre><code>var lpYear = ( from a in _ds.Tables[0].AsEnumerable() join b in LandingPages on a["OFFERINGKEY"].ToString() equals b.Code into c from d in c.DefaultIfEmpty() where DateTime.Parse(a["PURCHASEDATE"].ToString()) &gt;= DateTime.Parse("January 1, " + year) where DateTime.Parse(a["PURCHASEDATE"].ToString()) &gt;= DateTime.Parse("December 31, " + year) where LandingPages.Any(x =&gt; x.Code == a["OFFERINGKEY"].ToString()) orderby d.Title select new { title = d.Title, price = a["PRICE"], url = d.URL, code = d.Code }).GroupBy(o =&gt; o.title) .Select(o =&gt; new { total = o.Sum(p =&gt; decimal.Parse(p.price.ToString())), count = o.Count(), title = o.Key, url = o.Select(p=&gt;p.url), code = o.Select(p=&gt;p.code) } ); </code></pre> <p>This is the resulting value for <code>url</code> and <code>purchased</code>:</p> <pre><code>System.Linq.Enumerable+WhereSelectEnumerableIterator`2[&lt;&gt;f__AnonymousType2`3[System.String,System.Object,System.String],System.String] </code></pre> <p><strong>The Solution</strong> <em>(thanks to <a href="https://stackoverflow.com/users/1284526/cedric-bignon">Cédric Bignon</a>)</em>:</p> <p>Place <code>.First()</code> at the end of my <code>o.Select(p=&gt;p.url)</code> lines:</p> <pre><code>url = o.Select(p=&gt;p.url).First(), code = o.Select(p=&gt;p.code).First() </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USjiy
    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
    1. This table or related slice is empty.
    1. COWhy don't you use LINQ syntax (instead of extention methods) from begin to end of your query? It may simplify making reference between multiple parts of the query. When you say "it doesn't work, what is the problem exactly?"
      singulars
      1. POCombine results after join and groupby with DataTable and List<Object>
      1. USCédric Bignon
    2. COI have tried with various extension method configurations (I think) and using the `.GroupBy()` and `.Select()` seemed to the only way to do what I needed. Can you provide an example?
      singulars
      1. POCombine results after join and groupby with DataTable and List<Object>
      1. USjiy
 

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