SQL to LINQ possible?

Question

Can this SQL query be translated to LINQ in one select?

select ID as SBDID,(select top 1 T.ID from Transactions T where T.SBDID=S.ID order by id desc) as TransID
from SBD S where ID in(223,225)

I want to list the last TransactionID for each SBD.ID Thanks

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you. — NetMage, Mar 13 '19 at 18:45

score 1 · Accepted Answer · answered Mar 14 '19 at 08:37

Apparently you have SBDS and TRANSACTIONS. Every Sbd has a primary key Id. Every Transaction has a primary key Id and a foreign key to the Sbd that it belongs to in SbdId:

class Sbd
{
    public int Id {get; set;}      // Primary key
    ...
}
class Transaction
{
    public int Id {get; set;}      // Primary key
    public int SbdId {get; set;}   // Foreign key to the Sbd that this transaction belongs to
    ...
}

Now you want all Sdbs with an Id between 223 and 225, each Sbd with its Transaction with the highest value for Id.

Whenever you see a query for an object with all or some of its sub-objects, like a School with its Students, a Bank with his New York Clients, a Customer with his Orders, etc, consider using GroupJoin

// GroupJoin Sbds and Transactions:
var result = dbContext.Sbds.GroupJoin(dbContext.Transactions, 
    sbd => sbd.Id,                     // from every Sbd, take the Id
    transaction => transaction.SbdId,  // from every Transaction take the SbdId

    // ResultSelector: take the Sbd with all its matching Transactions to make one new:
    (sbd, transactionsOfThisSbd) => new
    {
        Id = sbd.Id,

        // You don't want all transactions of this Sbd, you want only the transaction
        // with the highest Id:
        Transaction = transactionsOfThisSbd
            .OrderByDescending(transaction => transaction.Id)
            .FirstOrDefault(),
    });

Or without all the comment, so you see how small the statement is:

    var result = dbContext.Sbds.GroupJoin(dbContext.Transactions, 
    sbd => sbd.Id,
    transaction => transaction.SbdId,
    (sbd, transactionsOfThisSbd) => new
    {
        Id = sbd.Id,
        Transaction = transactionsOfThisSbd
            .OrderByDescending(transaction => transaction.Id)
            .FirstOrDefault(),
    });

Thank you very very much for the answer and explanations. This is indeed what I need and it works. On another note seems it's slower than hitting the db for every **SBD** and getting the latest **Transaction**. But the same query in SQL runs very quick. — Rares P, Mar 21 '19 at 11:31

score 0 · Answer 2 · answered Mar 13 '19 at 15:17

0

I have not compiled the code but this should work. Context is your dbContext.

from s in context.SBD 
select new {
             id = s.id,
             tid = (from t in context.Transactions
                    where t.SBDID == s.id
                    select t).OrderByDescending().First().Id
            }).ToList();

answered Mar 13 '19 at 15:17

The_Outsider

1,875
2
24
42

1

The query ended up like `(from s in _context.Sbd select new { id = s.Id, tid = (from t in _context.Transactions where t.IdSbd == s.Id select t).OrderByDescending(a => a.IdTransaction).First().IdTransaction }).ToList();` – Rares P Mar 21 '19 at 11:08
Unfortunately it also crashes with: _{System.InvalidOperationException: Sequence contains no elements at System.Linq.Enumerable.First[TSource](IEnumerable`1 source) at lambda_method(Closure , QueryContext , ValueBuffer ) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Shape(QueryContext queryContext, ValueBuffer& valueBuffer) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext() at ... _ – Rares P Mar 21 '19 at 11:10

SQL to LINQ possible?

2 Answers2