Databases
StackOverflow2013
Postsdbo
POCan I refer to calculated columns within a Linq to Sql query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCan I refer to calculated columns within a Linq to Sql query?
    primarykey
    data
    text
    <p>I have the following schema:</p> <pre><code>product - productID (PK) - name - units purchase - purchaseID (PF) - productID (FK) - soldUnits - woUnits boxContents - boxID (PK) - puchaseID (FK) - count </code></pre> <p>and the following Linq to SQL query:</p> <pre><code> var qStockLevel = from pr in db.products select new { pr.productID, pr.name, totalUnits = (from pu in db.purchases where pu.productID == pr.productID select pu).Count() * pr.units, sold = (from pr2 in db.products join pu in db.purchases on pr.productID equals pu.productID into pp where pr2.productID == pr.productID from pu2 in pp.DefaultIfEmpty() select (int?) pu2.soldUnits ?? 0).Sum(), writtenOff = (from pr2 in db.products join pu in db.purchases on pr.productID equals pu.productID into pp where pr2.productID == pr.productID from pu in pp.DefaultIfEmpty() select (int?)pu.woUnits ?? 0).Sum(), onDisplay = (from pr2 in db.products join bc in db.boxContents on pr.productID equals bc.purchase.productID into bp where pr2.productID == pr.productID from bc in bp.DefaultIfEmpty() select (int?)bc.count ?? 0).Sum(), available = (totalUnits - sold - writtenOff - onDisplay), }; </code></pre> <p>The "available" column at the end doesn't work, but I've included it to illustrate what I'm trying to acheive - a column calculated from the columns already generated. Is this possible? I could do it at the presentation layer, but wanted to check if this would work first.</p> <p>Also - to my (new to Linq) eye, the query looks inefficient as I am repeating myself in generating the writtenOff and sold columns. Is there a better way to acheive this when I have aggregate functions involved.</p> <p>Thanks.</p> <p><strong>EDIT</strong></p> <p>One more thing - I only want to return rows where "available" is > 0. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBen
    1. USBen
    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. POCan I refer to calculated columns within a Linq to Sql query?
      1. USTom
      1. VTFavorite
    2. VO
      singulars
      1. POCan I refer to calculated columns within a Linq to Sql query?
      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