How can i use SQL inner join statement as query in LINQ

Question

SELECT
    Id,
    No,
    NetAmount,
    PaidAmount,
    NetAmount - PaidAmount AS AmountToBePayed
FROM
    (
        SELECT
            m.Id,
            m.No,
            SUM(NetAmount) as NetAmount,
            (
                SELECT
                    COALESCE( SUM( PaidAmount ), 0 )
                FROM
                    A_Account_Payable_Payment_Invoice
            ) as PaidAmount
        FROM
            A_Account_Payable_Invoice_M m
            INNER JOIN A_Account_Payable_Invoice_Item d ON
                m.Id = d.A_Account_Payable_Invoice
        GROUP BY
            m.Id,
            m.No
    ) a

How can i use this query directly in LINQ C#

Yes i tried to use this query directly in LINQ but i can'not I just want to use this query and get result in LIST — Ayesha Sheikh, Dec 26 '19 at 09:35
So as far as I understand you don't want to translate this SQL ve LINQ, you just want to call this script from EF or something, am I right? — Selim Yildiz, Dec 26 '19 at 10:24
@AyeshaSheikh You can use the tools below in my answer for the required conversion of your `SQL` query. If you directly want to call a query (which will be complicated) in your case, you can refer to this: https://www.learnentityframeworkcore.com/raw-sql — Rahul Sharma, Dec 26 '19 at 10:29
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Dec 26 '19 at 19:33

score 0 · Answer 1 · answered Dec 26 '19 at 10:39

As you mentioned on comment section you don't want to translate SQL to LINQ, you just want to directly run this script.

To do so first you need to create class that will match with your Script like:

 [DataContract()]
 public class PaymentInvoiceResult
 {
    [DataMember]
    public int Id { get; set; }

    [DataMember]
    public int No { get; set; }

    [DataMember]
    public int NetAmount { get; set; }

    [DataMember]
    public int PaidAmount { get; set; }

    [DataMember]
    public int AmountToBePayed { get; set; }
 }

Then you need to use SqlQuery as following:

List<PaymentInvoiceResult> paymentInvoiceResultList = db.Database.SqlQuery<PaymentInvoiceResult>("Your Script").ToList();

score 0 · Answer 2 · answered Dec 26 '19 at 12:07

0

You can simply use:

var results = db.ExecuteQuery(typeof(DTO), "sql query ");

Your dto should have members:

Id,
    No,
    NetAmount,
    PaidAmount,
    NetAmount - PaidAmount AS AmountToBePayed

answered Dec 26 '19 at 12:07

Gauravsa

6,330
2
21
30

You can mark it as answer as it will help other members:). Cheers – Gauravsa Dec 27 '19 at 03:11

How can i use SQL inner join statement as query in LINQ

2 Answers2