LINQ to SQL 中的嵌套 IN 子查询

Question

>困惑如何将此SQL查询写入Linq to SQL

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

到目前为止我尝试过什么

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

你的代码一切都很好，我想只有一个错误。

更改选择智商。AffiliateId to => iq.贷款人编号

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

我认为

不需要嵌套查询。可以使用联接查询。试试这个

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();