How to write below sql query in linq C # where some of the parameters will be null sometimes

Question

I have the following query in sql,

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

in this case, sometimes StakeBuyInId will be null or WaitingListTypeId will be null null. I want to execute this query through linq C # in the following code.

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

Here, StakeBuyInIds int [] will sometimes be null, while I will perform the linq operation for the above sql query. Thanks for any help.

Answer 1

You can probably just check for null outside your expression, for example:

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

You can also do this:

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

As a result, it checks the null value outside the query, but ensures that when the query actually executes, it cannot be empty.

Answer 2

waitingListTypeId and stakeBuyinId must be nullable int in the WaitingList relational object.

 List<int?> WaitingListTypeIds=new List(new int?[]{1}); var StakeBuyInIds=from w in WaitingListsCollection where new List<int?>(new int?[]{5}).Contains(w.StakeBuyInId) && w.WaitingListTypeId = 2; var output= from w in WaitingListsCollection where WaitingListTypeIds.Contains(w.WaitingListTypeId) || StakeBuyInIds.Contains(w.StakebuyInId)