title: how can I convert this T-SQL statement into code that I can use with MVC3
body: <p>I am writing a web app that will display a quick view of accounts for managers to use and I am using MVC 3. I have written some jQuery that allows the user to drill down similar to a tree view using the code below.</p>
  
  <p><strong>jQuery Spend Function</strong></p>
  
  <pre><code>//Spend Category function for monthly
          pa_click = function (pa_label) {
              PA_ID = pa_label.getAttribute('pa_id');
  
              var pa_details = document.getElementById('pa-details-' + PA_ID);
  
              jQuery.getJSON('@Url.Action("getAjaxSCs")', { PA: pa_label.title }, function (SCS) {
                  pa_details.innerHTML = "";
                  jQuery.each(SCS, function (index, SC) {
                      months_html = '';
                      for (var i = 0; i &lt; 12; i++) {
                          months_html +=
                                              '&lt;div id="SC-' + SC.SPEND_CATEGORY + '-' + months[i] + '" class="month-wrapper tree border-white"&gt;' +
                                              months[i] +
                                              '&lt;/div&gt;';
                      }
  
                      pa_details.innerHTML +=
  
                              '&lt;div id ="Spend-Category-' + SC.SPEND_CATEGORY + '" class="sc-wrapper tree border"&gt;' +
                                  '&lt;div id ="sc-title-' + SC.SPEND_CATEGORY + '" class="sc-title"&gt;' +
                                      '&lt;div class = "sc-label" title = "' + SC.SPEND_CATEGORY + '" SC_id="' + SC.SPEND_CATEGORY + '" onclick = "sc_click(this)"&gt;' + SC.SPEND_CATEGORY + '&lt;/div&gt;' +
                                      months_html +
                                  '&lt;/div&gt;' +
                                  '&lt;div id="sc-details-' + SC.SPEND_CATEGORY + '" class = "pa-details" style = "display:none"&gt;' + SC.SPEND_CATEGORY + '&lt;/div&gt;' +
                              '&lt;/div&gt;';
                  })
              });
              jQuery('#pa-details-' + PA_ID).show('slide', { direction: 'up' }, 'fast');
  
          };
  </code></pre>
  
  <p>The jQuery grabs the data from the table using a JSON return in my controller. The controller code is posted below.</p>
  
  <p><strong>Controller Code</strong></p>
  
  <pre><code>[Authorize]
  public ActionResult getAjaxSCs(string PA = null, float AmountList = 0)
  {
              if (PA != null)
              {
                  var SCList = from x in db.NDE_Actuals_Web select x;
  
                  SCList = SCList.Where(x =&gt; x.PA == PA);
  
  
  
                  return Json(SCList.Select(x =&gt; new { x.SPEND_CATEGORY}).Distinct().ToArray(), JsonRequestBehavior.AllowGet);
  
  
              }
              return View();
  }
  </code></pre>
  
  <p>I have written a SQL statement that will return the data I need but I don't know how to convert the SQL statement to something that C# can understand seeing as how I have to use a sum function. The SQL statement that will return the data I need is below.</p>
  
  <p><strong>SQL query</strong></p>
  
  <pre><code>select 
      pa, SUM(amount) 
  from nde_actuals_web 
  where pa = 'A0161000' and SPEND_CATEGORY = 'Capital' and ACCOUNTING_PERIOD = 1 
  group by pa
  </code></pre>
  
  <p>The two parts I need help with are the jQuery and the controller. For the jQuery I need help understanding how to put the correct field in the row output. Right now this line below outputs <code>Jan Feb Mar Apr May Jun July Aug Sep Oct Nov Dec</code>. </p>
  
  <pre><code>months_html = '';
                      for (var i = 0; i &lt; 12; i++) {
                          months_html +=
                                              '&lt;div id="SC-' + SC.SPEND_CATEGORY + '-' + months[i] + '" class="month-wrapper tree border-white"&gt;' +
                                              months[i] +
                                              '&lt;/div&gt;';
                      }
  </code></pre>
  
  <p>I need to change the output from each month 3 letter name to the amount total in the correct accounting period. So the SQL query above would return the Amount total for Jan since it is from accounting period 1. </p>
  
  <p>In the controller I need help converting a T-SQL query to a C# controller linq query. I hope this all makes sense if it doesn't I will be checking back often feel free to ask any questions that might help. Thanks for your help!</p>
  
  <p><strong>Update: 1</strong></p>
  
  <p>I tried what Yasser wrote below </p>
  
  <p><code>SCList = SCList.Where(x =&gt; x.PA == PA &amp;&amp; x.SPEND_CATEGORY = "Capital" &amp;&amp; x.ACCOUNTING_PERIOD = 1).OrderBy(x =&gt; x.PA);</code></p>
  
  <p>but I am getting an error around the <code>'&amp;&amp;'</code> </p>
  
  <p><code>x.PA == PA &amp;&amp; x.SPEND_CATEGORY</code> it underlines in red under this section and says  </p>
  
  <p>"(operator '&amp;&amp;' cannot be applied to operands of type bool and string)"</p>
  
  <p><code>"Capital" &amp;&amp; x.ACCOUNTING_PERIOD</code> it underlines in red under this section and says</p>
  
  <p>"(operator '&amp;&amp;' cannot be applied to operands of type string and string)"</p>
  
  <p><strong>Update: 2</strong></p>
  
  <p>Good news everyone the code doesn't give me the red squiggles anymore but it does seem to be causing the website to crash. This is the new code that Charlino and Yasser helped me with below.</p>
  
  <pre><code>SCList = SCList.Where(x =&gt; x.PA == PA &amp;&amp; x.SPEND_CATEGORY == "Capital" &amp;&amp; x.ACCOUNTING_PERIOD == "1").OrderBy(x =&gt; x.PA);
  </code></pre>
  
  <p>I tried stepping through the code and I got a <code>(function evaluation timed out)</code> when I tried to watch <code>SCList</code> there are over 400,000 lines in this table so I am not surprised and this has happened before. I solved it using a distinct in the linq query, but I don't know if I can do something like that with this kind of query. Any suggestions</p>
  
  <p>Also I noticed something when I was staring at the code it doesn't look like the Amount was summed anywhere like in the SQL I wrote earlier and this is a must-have in the code or it will list every row instead of just the sum of the amounts like I need. Thanks for everyone's help! </p>