Ternary operator in LINQ where clause

Question

I am using linq for join multiple tables and write complex query. Here when i will have '0' as any parameter i.e. CategoryId, GameId, LimitVariantId, that means user has selected "All" from interface.

My Sql query, when I will pass parameter values greater than '0' is :

select * from dbo.GameCombinations gc
inner join dbo.StakeBuyInByStakeCategories sbsc
on sbsc.StakeBuyInByStakeCategoryId = gc.StakeBuyInByStakeCategoryId
inner join dbo.GameTables gt
on gc.GameCombinationId = gt.GameCombinationId
where gc.CurrencyId=1 and gc.GameTypeId=2
and sbsc.StakeBuyInId=gt.BuyIn
and gc.CategoryId=4
and gc.GameId=7
and gc.LimitVariantId=23
and gc.StakeCategoryId in (3,5,6)

When I will pass CategoryId as 0 then My Sql query will be :

select * from dbo.GameCombinations gc
inner join dbo.StakeBuyInByStakeCategories sbsc
on sbsc.StakeBuyInByStakeCategoryId = gc.StakeBuyInByStakeCategoryId
inner join dbo.GameTables gt
on gc.GameCombinationId = gt.GameCombinationId
where gc.CurrencyId=1 and gc.GameTypeId=2
and sbsc.StakeBuyInId=gt.BuyIn
--and gc.CategoryId=4
and gc.GameId=7
and gc.LimitVariantId=23
and gc.StakeCategoryId in (3,5,6)

So I don't need to include that fields in where clause. For that I had written the following LINQ:

ProviderDB db = new ProviderDB();
try
{
    IQueryable<dynamic> query;

    if (StakeCategoryIdsByStakeBuyIn != null)
    {
        query = (from gc in db.GameCombinations.Where(x => x.CurrencyId == CurrencyId && x.GameTypeId == GameTypeId
                            && CategoryId <= 0 ? true : x.CategoryId == CategoryId
                            && GameId <= 0 ? true : x.GameId == GameId
                            && LimitVariantId <= 0 ? true : x.LimitVariantId == LimitVariantId
                            && StakeCategoryIdsByStakeBuyIn.Contains(x.StakeCategoryId)
                        )
                 join sbsc in db.StakeBuyInByStakeCategories
                 on gc.StakeBuyInByStakeCategoryId equals sbsc.StakeBuyInByStakeCategoryId
                 join gt in db.GameTables
                 on gc.GameCombinationId equals gt.GameCombinationId
                 join gx in db.Games
                 on gc.GameId equals gx.GameId into joined
                 from gx in joined.DefaultIfEmpty()
                 where gt.BuyIn == sbsc.StakeBuyInId
                 select new
                 {
                     GameTableId = gt.GameTableId,
                     Description = gt.Description,
                     BuyIn = gt.BuyIn,
                     Table = gx.GameName,
                     MaxAllowPlayer = gt.MaxAllowPlayer
                 }).Distinct();
    }
    else
    {
        query = (from gc in db.GameCombinations.Where(x => x.CurrencyId == CurrencyId && x.GameTypeId == GameTypeId
                              && CategoryId == 0 ? true : x.CategoryId == CategoryId
                              && GameId == 0 ? true : x.GameId == GameId
                              && LimitVariantId == 0 ? true : x.LimitVariantId == LimitVariantId
                              && StakeCategoryIdsByStakeBuyIn == null
                        )
                 join sbsc in db.StakeBuyInByStakeCategories
                 on gc.StakeBuyInByStakeCategoryId equals sbsc.StakeBuyInByStakeCategoryId
                 join gt in db.GameTables
                 on gc.GameCombinationId equals gt.GameCombinationId
                 join sb in db.StakeBuyIns
                 on gt.BuyIn equals sb.StakeBuyInId
                 join gx in db.Games
                 on gc.GameId equals gx.GameId into joined
                 from gx in joined.DefaultIfEmpty()
                 where gt.BuyIn == sbsc.StakeBuyInId
                 select new
                 {
                     GameTableId = gt.GameTableId,
                     Description = gt.Description,
                     BuyIn = sb.StakeBuyInValue,
                     Table = gx.GameName,
                     MaxAllowPlayer = gt.MaxAllowPlayer
                 }).Distinct();
    }

But this will return all fields from my database. So Can any one help me to write these queries in LINQ with ternary condition that will return my filtered fields' records?

Solution 1

With Linq to SQL (with LINQ in general) you can add Where conditions programmatically, like:

var query = db.GameCombinations.Where(x => x.CurrencyId == CurrencyId && x.GameTypeId == GameTypeId);

if (CategoryId > 0)
{
    query = query.Where(x => x.CategoryId == CategoryId);
}

and so on.

Besides, it is better to use "type inference" (using the var keyword) instead of dynamic, you won't get intellisense with dynamic

[Edit] The Linq to SQL provider will group all the Where conditions when translating to SQL

Solution 2

Your example is very complicated but essentially you are using linq to build a query.

var someIQueryableInProgress = ... ;

if (categoryId != 0)
{
    someIQueryableInProgress = someIQueryableInProgress
        .Where(s => s.categoryId = categoryId)
}

Then evaluate when all your conditions are applied and let the provider do the work for you.

Solution 3

You can still perform this query in SQL, try something like:

select * from dbo.GameCombinations gc
inner join dbo.StakeBuyInByStakeCategories sbsc
on sbsc.StakeBuyInByStakeCategoryId = gc.StakeBuyInByStakeCategoryId
inner join dbo.GameTables gt
on gc.GameCombinationId = gt.GameCombinationId
where gc.CurrencyId=1 and gc.GameTypeId=2
and sbsc.StakeBuyInId=gt.BuyIn
and (0 = categoryParameter OR gc.CategoryId=categoryParameter) //Pass 0 to take all categories
and gc.GameId=7
and gc.LimitVariantId=23
and gc.StakeCategoryId in (3,5,6)

EDIT:

Do the same for the remaining parameters:

ProviderDB db = new ProviderDB();

                try
                {
                    IQueryable<dynamic> query;

                    if (StakeCategoryIdsByStakeBuyIn != null)
                    {
                        query = (from gc in db.GameCombinations.Where(x => x.CurrencyId == CurrencyId && x.GameTypeId == GameTypeId
                                            && (CategoryId == 0 || x.CategoryId == CategoryId)
                                            && (GameId == 0 || x.GameId == GameId)
                                            && (LimitVariantId == 0 || x.LimitVariantId == LimitVariantId)
                                            && StakeCategoryIdsByStakeBuyIn.Contains(x.StakeCategoryId)
                                        )
                                 join sbsc in db.StakeBuyInByStakeCategories
                                 on gc.StakeBuyInByStakeCategoryId equals sbsc.StakeBuyInByStakeCategoryId
                                 join gt in db.GameTables
                                 on gc.GameCombinationId equals gt.GameCombinationId
                                 join gx in db.Games
                                 on gc.GameId equals gx.GameId into joined
                                 from gx in joined.DefaultIfEmpty()
                                 where gt.BuyIn == sbsc.StakeBuyInId
                                 select new
                                 {
                                     GameTableId = gt.GameTableId,
                                     Description = gt.Description,
                                     BuyIn = gt.BuyIn,
                                     Table = gx.GameName,
                                     MaxAllowPlayer = gt.MaxAllowPlayer
                                 }).Distinct();
                    }
          }

Comments

[KomalJariwala]

Thanks. but i need query in linq.

[noobob]

Use the same principle as the above query. Try replacing CategoryId == 0 ? true : x.CategoryId == CategoryId with (CategoryId == 0 || x.CategoryId == CategoryId). The other parameters too

[Mike Perrenoud]

@noobob, +1, 0 may not be the right "other" value, it may be null, but this principle works perfect.

[noobob]

@MichaelPerrenoud 0 is the parameter, as he mentioned, that he will explicitly send when he wants to skip the filtering.

[KomalJariwala]

@noobob Can you please tell me whole LINQ query in my query means where i need to place "(CategoryId == 0 || x.CategoryId == CategoryId)" in my linq query?

[noobob]

@KomalJariwala edited. Add the else condition and you should basically put the same where condition there

[KomalJariwala]

Thanks it is helpful to me.