Databases
StackOverflow2013
Postsdbo
POLINQ to DataSet - group by variable field, or join on a variable condition (with sum)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLINQ to DataSet - group by variable field, or join on a variable condition (with sum)
    text
    copied!<p>The following query works as long as I can add DataRelations. However, the relation "Table2Table3" may no longer be clearly defined, so I think I need to remove it. The old relation strictly joins on 2 string fields. The new relation exists if Table2.code = Table3.code and Table2.class = Table3.class <strong>OR</strong> Table2.code = Table3.code and Table2.class = "*" and Table3.class is anything. </p> <p>How can I implement this condition and still sum Table3.cost field? </p> <p>Can I use regular expressions? </p> <p>I tried creating an anonymous class equality (see far below), but Table2 was no accessible on the right side of the equals clause.</p> <pre><code>var querytest = from x in ds.Tables[0].AsEnumerable() orderby x.Field&lt;int&gt;("Priority") select new { Name = x.Field&lt;string&gt;("alias"), Amount = x.GetChildRows("Table1Table2") .Sum(product =&gt; product.GetChildRows("Table2Table3") .Sum(tx =&gt; tx.Field&lt;decimal&gt;("cost"))) }; </code></pre> <p>sample tables:</p> <pre>table1: list of aliases and priorities ----------------- ID alias priority 1 alias1 1 2 alias2 2 3 alias3 4 4 alias4 3</pre> <pre>table2: children records joined to table1 by ParentID (1-to-many) ----------------- code class ParentID code1 class1 1 code2 class2 1 code3 class3 2 code4 class4 4 code5 * 3</pre> <pre>table3: data, joined on new condition ----------------- code class cost code1 class1 1.00 code1 class1 10.00 code1 class1 26.00 code2 class2 5.00 code2 class2 0.00 code3 class3 1000.00 code5 class6 25.00 code5 class7 26.00</pre> <p>expected output:</p> <pre>alias1 42.00 alias2 1000.00 alias4 0.00 alias3 51.00</pre> <p>This query can't access "codes" in the scope of the second anonymous class:</p> <pre><code>var querytest2 = from aliases in ds.Tables[0].AsEnumerable() join codes in ds.Tables[1].AsEnumerable() on aliases.Field&lt;int&gt;("ID") equals codes.Field&lt;int&gt;("ParentID") join data in ds.Tables[2].AsEnumerable() on new { code = codes.Field&lt;string&gt;("code"), classcode = codes.Field&lt;string&gt;("class") } equals new { code = data.Field&lt;string&gt;("code"), classcode = (data.Field&lt;string&gt;("class") == "*" ? codes.Field&lt;string&gt;("class") : data.Field&lt;string&gt;("class") } orderby aliases.Field&lt;int&gt;("Priority") select new { name = aliases.Field&lt;string&gt;("alias"), cost = //somehow sum here }; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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