Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>To get it all done in one statement you need to join Enrollments and Commissions and then group by the Tutor.</p> <p>This first example uses Query Syntax like you are using:</p> <pre><code> IEnumerable&lt;TutorCommission&gt; tutorsCommissions = from enrollment in enrollments // Take the enrollments join tutorsCommissionPercentage in tutorCommissionPercentages // Join with the tutor's commission percentages. on enrollment.TutorNoID equals tutorsCommissionPercentage.TutorNoID group enrollment by new { enrollment.TutorNoID, tutorsCommissionPercentage.CommissionPercentage } into enrollmentsAndCommissionByTutor // group enrollments and commission by the tutor select new TutorCommission { TutorNoID = enrollmentsAndCommissionByTutor.Key.TutorNoID, // the grouping which is the tutor CommissionAmount = (long) enrollmentsAndCommissionByTutor.Sum(e =&gt; e.MonthlyFee * enrollmentsAndCommissionByTutor.Key.CommissionPercentage) }; </code></pre> <p>This second example uses Method Syntax which is a little more intuitive for this operation</p> <pre><code> IEnumerable&lt;TutorCommission&gt; tutorsCommissionsAlt = enrollments // Take the enrollments .GroupJoin( // This will group enrollments by the tutor tutorCommissionPercentages, // Join with the tutor's commission percentages. e =&gt; e.TutorNoID, // Use tutorNoID for left Key tcp =&gt; tcp.TutorNoID, // ... and right key (e, tcp) =&gt; new TutorCommission // Create entry which is the tutor and his total commission { TutorNoID = e.TutorNoID, CommissionAmount = (long) tcp.Sum(c =&gt; c.CommissionPercentage * e.MonthlyFee) }); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POBulk Database Entries - Calculate Commission
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSteve
    1. USSteve
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POBulk Database Entries - Calculate Commission
      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. VTAcceptedByOriginator
    1. COHi, thanks for your answer. I've made TutorCommissionPercentage a property of Tutor now instead of having them separately. Could you please should me the first method syntax using TutorCommissionPercentage as a property in Tutor? Is this all the code I need, or do I still need to incorporate the loop?
      singulars
      1. PO
      1. USAmy
    2. COReplace "tutor(s)CommissionPercentage" with "tutor(s)" and it should all work. Post the new classes and then I'll respond to that if you want me to show you so that we can maintain consistency between Q&A. You'll need the loop to add each created entry to the database (there's no bulk insert to do so).
      singulars
      1. PO
      1. USSteve
    3. COThanks, I've added the Tutor class above. And the reason the TutorCommissionPercentage is a double is because I was having issues multiplying a decimal and double in my previous code. But will I still need the "Join" if the percentage is in tutors now?
      singulars
      1. PO
      1. USAmy
 

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