Linq dan SQL

Standar

query 1

SQL : Select * from viewtransaksi

di convert ke Linq :

Linq : from vt in aging.viewtransaksi

select vt

query 2 (SUM)

sql :Select sum(foreignamount) from viewtransaksi where

     date<=’2012-02-29′ and accountno=”11.05.01-CONTOH”

     and drcr=’C’ and trxno not in

     (select trxno from viewtransaksi where

AccountNo =‘11.05.01-CONTOH’ and drcr=‘D’ and date<=‘2012-02-29’) group by accountno

Linq :

var query = from vt in ag.ViewTransacts

             whereString.Compare(vt.AccountNo, _accNo, true,  CultureInfo.InvariantCulture) == 0 && vt.DrCr == ‘C’ && !(

(from Vt1 in ag.viewtransaksi whereString.Compare(Vt1.AccountNo, _accNo, true, CultureInfo.InvariantCulture) == 0 && Vt1.DrCr == _flag1 && Vt1.Date <= _dtEnd.Date select Vt1.TrxNo).Distinct() ).Contains(vt.TrxNo) && vt.ForeignAmount > 0

&& vt.Date <= _dtEnd.Date

group vt bynew { vt.AccountNo} into a

select new ReportAgingNew

{ SumPayment = a.Sum(c => c.ForeignAmount),

};

query 3 (distinct)

SQL : select distinct invoice.InvoiceNo, invoice.InvoiceDate
from invoice in billing.TrnInvoicess
join invoiceD in billing.TrnInvoiceDetailss on invoice.InvoicePk = invoiceD.InvoiceFK

LINQ :

(from invoice in billing.TrnInvoicess
join invoiceD in billing.TrnInvoiceDetailss on invoice.InvoicePk equals invoiceD.InvoiceFK
select new LookUpInvoice
{
InvoiceNo = invoice.InvoiceNo,
InvoiceDate = invoice.InvoiceDate
}).Distinct();

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s