sql to LINQ query

Question

I am trying to write this query in LINQ. This query returns a list:

SELECT TOP 500 sst.Id

, sst.status

, sst.Created

, sst.StatusChangeDate

, sstcnt.numitemid

, ssti.XML

FROM dbo.Transaction sst

  JOIN ( SELECT transactionid, COUNT(itemid) numitemid
     FROM dbo.TransactionItem ssti

	 GROUP BY ssti.TransactionId

	) sstcnt

ON sstcnt.TransactionId = sst.TransactionId

  JOIN dbo.TransactionItem ssti ON ssti.TransactionId = sst.TransactionId

ORDER BY sst.Created desc

any help will be appreciated.

Answer 1

Hi @Anjali Agarwal , Welcome to Microsoft Q&A.

Its corresponding Linq is as follows:(Modify it according to your needs)

using (var db = new MyDbContext())
{
    var result = (
        from sst in db.Transaction
        join sstcnt in (
            from ssti in db.TransactionItem
            group ssti by ssti.TransactionId into g
            select new { TransactionId = g.Key, NumItemId = g.Count() }
        ) on sst.TransactionId equals sstcnt.TransactionId
        join ssti in db.TransactionItem on sst.TransactionId equals ssti.TransactionId
        orderby sst.Created descending
        select new object[]
        {
                        sst.Id,
                        sst.Status,
                        sst.Created,
                        sst.StatusChangeDate,
                        sstcnt.NumItemId,
                        ssti.XML
        }
    ).Take(500).ToList();
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.