Convert where clause containing select to Linq

Question

I need to convert the following SQL to Linq:

select b.*
from Branches b
where b.Id = (select top 1 bId
from Actions a
where a.aId = 10596
and a.bId is not null
order by a.createDate desc
)

But I have no idea of how to proceed. Any help pls?

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, Aug 28 '18 at 16:04

score 1 · Answer 1 · answered Aug 28 '18 at 13:08

var action = db.Actions
           .OrderByDescending(a => a.CreateDate)
           .FirstOrDefault(a => a.aId = 10596 && a.bId != DbNull.Value);
if (action != null)
{
   var result = db.Branches.Where(b => b.Id == action.bId); 
}

However, by guess b.Id is primary key for Branches and is related to Actions through foreign key bId. Then:

var result =  db.Branches
              .FirstOrDefault(b => b.Actions
                                    .Where(a => a.aId = 10596)
                                    .OrderByDescending(a => a.CreateDate));

Mojtaba Tajik · Answer 2 · 2018-08-29T10:39:55.920

1

var result = Branches
        .Where(b => b.ID == Actions.Where(w => w.aId == 10596 && w.bId != null)
        ?.OrderByDescending(o => o.createDate)
        .First()?.bId);

edited Aug 29 '18 at 10:39

answered Aug 28 '18 at 13:12

Mojtaba Tajik

1,725
16
34

You don't have any null check. A Branch might have 0 Actions, no? Or there might be no actions with given 10596. – Cetin Basoz Aug 29 '18 at 10:01
@CetinBasoz null checks added – Mojtaba Tajik Aug 29 '18 at 10:40

Convert where clause containing select to Linq

2 Answers2