How to add sql 'when' functionality in c# linq

Question

I have the following parameters:

public object GetDataByProjectCostID(string employeeid, DateTime costdate, int id = 0)

And the query:

var projectCost = (from pc in db.ProjectCosts
                           where pc.ProjectCostID == id
                           where System.Data.Entity.DbFunctions.TruncateTime(pc.CostDate) == costdate.Date
                          
                           join p in db.Projects
                           on pc.ProjectID equals p.ProjectID

                           join sct in db.SubCostTypes
                           on pc.SubCostTypeID equals sct.SubCostTypeID

                           join ct in db.CostTypes
                           on sct.CostTypeID equals ct.CostTypeID

                           select new
                           {
                               p.ProjectName,
                               ct.CostTypeName,
                               ct.CostTypeID,
                               sct.SubCostTypeName,
                               pc.ProjectID,
                               pc.ProjectCostID,
                               pc.SubCostTypeID,
                               pc.Amount,
                               pc.Quantity,
                               pc.Note,
                               pc.CreatedBy,
                               sct.Unit,
                               pc.CreateDate,
                               pc.CostDate,
                               pc.ProjectCostImage


                           }).ToList();

Now my question is how i can add optional parameter in query. Suppose if id not existed in request then i need to skip the where clause

where pc.ProjectCostID == id

In sql we can use when clause for that but what for in LINQ? Thanks in advance.

Construct the main body of your query, omitting the .ToList() at the end. Then simply check whether `id` has a value. If it does, update your query using the fluent syntax. `projectCost = projectCost.Where(item => item.ProjectCostID == id);` Then you can turn that into a list. — Anthony Pegram, Jun 15 '21 at 00:14

Serge · Accepted Answer · 2021-06-15T00:59:16.000

1

try this

 where (( id==0 || pc.ProjectCostID == id)
 && System.Data.Entity.DbFunctions.TruncateTime(pc.CostDate) == costdate.Date)

edited Jun 15 '21 at 00:59

answered Jun 15 '21 at 00:50

Serge

40,935
4
18
45

score 1 · Answer 2 · answered Jun 15 '21 at 00:51

I use this technique in LinQ with the LinQ fluent form, you can try this:

//Make id nullable
public object GetDataByProjectCostID(string employeeid, DateTime costdate, int? id)
{
var projectCost = (from pc in db.ProjectCosts
                               //This expression is evaluated only if id has a value
                               where (!id.HasValue || pc.ProjectCostID == id)
                               && System.Data.Entity.DbFunctions.TruncateTime(pc.CostDate) == costdate.Date

                               join p in db.Projects
                               on pc.ProjectID equals p.ProjectID

                               join sct in db.SubCostTypes
                               on pc.SubCostTypeID equals sct.SubCostTypeID

                               join ct in db.CostTypes
                               on sct.CostTypeID equals ct.CostTypeID

                               select new
                               {
                                   p.ProjectName,
                                   ct.CostTypeName,
                                   ct.CostTypeID,
                                   sct.SubCostTypeName,
                                   pc.ProjectID,
                                   pc.ProjectCostID,
                                   pc.SubCostTypeID,
                                   pc.Amount,
                                   pc.Quantity,
                                   pc.Note,
                                   pc.CreatedBy,
                                   sct.Unit,
                                   pc.CreateDate,
                                   pc.CostDate,
                                   pc.ProjectCostImage


                               }).ToList();
}

How to add sql 'when' functionality in c# linq

2 Answers2