如何在linq c#中编写下面的sql查询,其中一些paramteres有时会为n

发布时间：2020-12-15 21:52:18 所属栏目：百科来源：网络整理

导读：我在sql中有以下查询, select * from dbo.WaitingLists where WaitingListTypeId in (1)or StakeBuyInId in (Select StakeBuyInId from dbo.WaitingLists where StakeBuyInId in (5) and WaitingListTypeId = 2) 在此,有时StakeBuyInId将为null或WaitingList

我在sql中有以下查询,

select * from dbo.WaitingLists 
where WaitingListTypeId in (1)
or StakeBuyInId in (Select StakeBuyInId from dbo.WaitingLists where StakeBuyInId in (5) and 
WaitingListTypeId = 2)

在此,有时StakeBuyInId将为null或WaitingListTypeId将为null.我想在以下代码中通过linq c#执行此查询.

public GameListItem[] GetMyWaitingList(Guid UserId,int LocalWaitingListTypeId,int GlobalWaitingListTypeId,int[] StakeBuyInIds)
            {
                ProviderDB db = new ProviderDB();

                List<GameListItem> objtempGameListItem = new List<GameListItem>();

                List<GameTables> objGameTablesList = new List<GameTables>();

                var objWaitingListUser = db.WaitingLists.Where(x => x.UserId.Equals(UserId));

                if (LocalWaitingListTypeId > 0 || (GlobalWaitingListTypeId > 0 && StakeBuyInIds != null))
                {
                    objWaitingListUser = objWaitingListUser.Where(x => x.WaitingListTypeId == LocalWaitingListTypeId || (x.WaitingListTypeId == GlobalWaitingListTypeId 
                                            && StakeBuyInIds != null ? StakeBuyInIds.Contains((Int32)x.StakeBuyInId) : true)
                                         );
                }
                return objtempGameListItem.ToArray();
            }

这里StakeBuyInIds int []有时会为null,那么我将如何对上面的sql查询执行linq操作.谢谢你的帮助.

解决方法

您可以在表达式之外检查null,如下所示：

if (LocalWaitingListTypeId > 0 || (GlobalWaitingListTypeId > 0 && StakeBuyInIds != null))
{
    if (StakeBuyInIds != null)
    {
        objWaitingListUser = objWaitingListUser.Where(
            x => x.WaitingListTypeId == LocalWaitingListTypeId || 
                 (x.WaitingListTypeId == GlobalWaitingListTypeId && 
                  StakeBuyInIds.Contains((Int32)x.StakeBuyInId));
    } else {
        objWaitingListUser = objWaitingListUser.Where(
            x => x.WaitingListTypeId == LocalWaitingListTypeId || 
                 x.WaitingListTypeId == GlobalWaitingListTypeId);
    }
}

您也可以这样做：

if (LocalWaitingListTypeId > 0 || (GlobalWaitingListTypeId > 0 && StakeBuyInIds != null))
{
    var arrayNull = StakeBuyInIds != null;
    var array = StakeBuyInIds ?? new int[0];
    objWaitingListUser = objWaitingListUser.Where(
        x => x.WaitingListTypeId == LocalWaitingListTypeId || 
             (x.WaitingListTypeId == GlobalWaitingListTypeId && 
              (arrayNotNull || array.Contains((Int32)x.StakeBuyInId)));
}

它会影响它在查询之外测试null,但确保在实际执行查询时它不能为null.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!