Click here to Skip to main content
15,036,363 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
i am new to Linq.My problem is i have this formula to find the net commission=NB+Renewal-clawback ; i am having NB,Renewal,Clawback values in my data base.all i need to do is calculate the net commission.

I have to do this in LINQ. but its not coming the correct result. I have written the SQL query for the above records ie

SQL
SELECT  COMMISSIONSPAIDDATE,(SUM(CommissionAmount) +(SELECT TAB1.CommissionAmount FROM (SELECT     COMMISSIONSPAIDDATE,PaymentType, SUM(CommissionAmount) AS CommissionAmount FROM  tblCommissionStatus WHERE (PaymentType = '113') GROUP BY PaymentType,COMMISSIONSPAIDDATE) TAB1)-(SELECT TAB1.CommissionAmount FROM (SELECT     COMMISSIONSPAIDDATE,PaymentType, SUM(CommissionAmount) AS CommissionAmount FROM  tblCommissionStatus WHERE (PaymentType = '115') GROUP BY PaymentType,COMMISSIONSPAIDDATE) TAB1)) AS NETCOMMISSION FROM tblCommissionStatus WHERE (PaymentType = '111') GROUP BY PaymentType,COMMISSIONSPAIDDATE



Pls help me to write the LINQ for the above SQL. It will be a real great help.
Thanks
Posted

1 solution

I' not sure I understand your description correctly - in particular, some description of what tables / columns you have in your database and what they represent would help.

But if you are just trying to do a straightforward calculation on each record, something like the following would work.

You need to set up your data context and entities (drag and drop in the VS designer and then do a bit of renaming of relevant bits in the Properties pane is all you need). Then see code below.

If your data structure is different, post details of the data structure and I'll see what I can do to amend the code.

XML
IEnumerable<MyCommissionStatus> MyCommissionStatuses =
(from ACommissionStatus in MyDC.CommissionStatuses
where // [add whatever you need here]
select ACommissionStatus);

foreach(MyCommissionStatus ACommissionStatus in MyCommissionStatuses)
{
    ACommissionStatus.commission = ACommissionStatus.NB + ACommissionStatus.Renewal - ACommissionStatus.clawback;
}

MyDC.SubmitChanges();


You might also want to think about transactions / failure modes etc. - but the above gives the basics.
   
Comments
Saumya J Pandey 11-May-11 0:20am
   
hei thanks NuttingCDEF... I am going to try it... thank u so much
Saumya J Pandey 11-May-11 2:20am
   
Thanks a ton NuttingCDEF it worked .....:) god bless u

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900