nested IN subquery in linq to sql

Question

confused how to write this sql query into linq to sql

select FirstName from [User] where [UserId] in(

select LenderId from AffiliateLenderRelation where AffiliateId in (select Userid From [User] where [UserId] = 11)
)

what i have tried so far

var innerquery = from iq in db.AffiliateLenderRelations where iq.AffiliateId == loggedInUser.UserId select iq.AffiliateId;
                List<SelectListItem> lenders = db.Users
                .Where(o => o.AccountTypeId == 1 && o.Deleted == false && innerquery.Contains(o.UserId))
                .Select(o => new SelectListItem()
                {
                    Value = o.UserId.ToString(),
                    Text = o.FirstName
                }).ToList();
                        lenders.Insert(0, new SelectListItem() { Value = "0", Text = "All" });

Answer 1

Everything is fine in your code there is only one mistake i guess.

change select iq.AffiliateId to => iq.LenderId

var innerquery = from iq in db.AffiliateLenderRelations where iq.AffiliateId == loggedInUser.UserId select iq.AffiliateId;
                List<SelectListItem> lenders = db.Users
                .Where(o => o.AccountTypeId == 1 && o.Deleted == false && innerquery.Contains(o.UserId))
                .Select(o => new SelectListItem()
                {
                    Value = o.UserId.ToString(),
                    Text = o.FirstName
                }).ToList();
                        lenders.Insert(0, new SelectListItem() { Value = "0", Text = "All" });

Answer 2

I think don't need nested query. You can use join query. Try this

var lobj= (from u db.Users.where(x=>x.UserId==11)
                                 let ces = from ce in db.AffiliateLenderRelation.where( y=> y.AccountTypeId == 1 && y.Deleted == false )
                                           select ce.LenderId 
                                 where ces.Contains(u.UserID)
                             .Select(o => new SelectListItem()
                    {
                        Value = o.UserId.ToString(),
                        Text = o.FirstName
                    })).ToList();