Wants this Sql Query in Linq

Question

How to write below given Sql Query in Linq?

SELECT  
    Invoice.ExtInvoiceId AS Id, 
    Invoice.ExtClientBranchId, 
    Invoice.CustomerReference,
    Invoice.CountryDataSetId,
    Invoice.JmsJobNumber,
    Invoice.InvoiceDate,
    Invoice.TotalAmount,
    Invoice.RequestedBy,
    Invoice.DateCreated,
    PaidTable.Paid,
    Invoice.So_Terms_Disc,
    '' AS RequestedByValue,
    Client.ParentId,
    Invoice.CurrencyCode 

FROM Invoice 

INNER JOIN Client 
    ON Invoice.ExtClientBranchId = Client.ExtClientBranchId 

LEFT OUTER JOIN (
    SELECT 
        ExtInvoiceId,
        (CASE WHEN SUM(TotalAmount) = 0 THEN 1 ELSE 0 END) AS Paid 

    FROM [Transaction] GROUP BY ExtInvoiceId
) AS PaidTable 
    ON Invoice.ExtInvoiceId = PaidTable.ExtInvoiceId

You have to translate it step by step. At the start don't focus to mutch on the long list of column you need to select. Get a brunch of ID to validate your result and start joining table one by one. If you fail to translate some SQL key word hit a search engine with "[insert SQL keywords] LinQ". And remember LinQ is lazy, you can cut your query in smaller part for readability. — xdtTransform, Sep 16 '19 at 06:14
In a world with an [mcve], and definition of those table, perhaps some of the join and clause won't be usefull as the relation will be well define. But who knows. — xdtTransform, Sep 16 '19 at 06:16
@Gabriel LloricoI am trying this var invoiceModels = ( from d in db.Invoices join j in db.Clients on d.ExtClientBranchId equals j.ExtClientBranchId into yGroup from y1 in yGroup.DefaultIfEmpty()) but query under Left Outer Join I am unable to understand how to write it. — Diya, Sep 16 '19 at 06:27
I believe that there are sites online where you can do this see [here](https://www.sqltolinq.com/), also see [this](https://stackoverflow.com/questions/296972/sql-to-linq-tool) — Bosco, Sep 16 '19 at 07:22
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, Sep 16 '19 at 19:52

score 0 · Answer 1 · answered Sep 16 '19 at 07:00

Hey yes the query looks it has few operations/join to be dealt with , so approach it step by step :

Fetch all the records , using inner join
Try using left outer join
Further simplify the query to the best of what you need.

Also note if you are visual-studio , it gives you this best interface wherein you can get to know at which point your linq is correct and what all options you can use with it.

Wants this Sql Query in Linq

1 Answers1