SQL Server Stored Procedure Multiple Condtions -

i have linq in c#, have convert sql query. , not sure how multiple filtering based on conditions:

var geofencereport = companycontext.geofencesimplereports.where(x => x.entertime != null && x.exittime != null && x.minutesingeofence != null).asqueryable();  if (model.geofenceid != -1) {     geofencereport = geofencereport.where(x => x.igeofenceid == model.geofenceid).asqueryable(); }  if (model.assetid != -1) {     geofencereport = geofencereport.where(x => x.iassetid == model.assetid).asqueryable(); }  if (model.categoryid != -1) {     geofencereport = geofencereport.where(x => x.icategoryid == model.categoryid).asqueryable(); }  if (model.siteid != -1) {     geofencereport = geofencereport.where(x => x.isiteid == model.siteid).asqueryable(); }  geofencereport = geofencereport                    .where(x => x.entertime >= model.startdatetime &&                            x.entertime <= model.enddatetime)                     .asqueryable(); 

so came in sql:

i created new type assetid:

use mydatabase go  create type idtable table (id int) 

and in sql:

use mydatabase go  create procedure [dbo].[xpt_getgeofencesummaryreport]     @iassetids idtable,     @igeofenceid int,     @icategoryid int,     @isiteid int,     @iassetid int  if @iassetid != -1     select * geofencesimplereport iassetid in (@iassetids)  if @igeofenceid != -1     select * geofencesimplereport igeofenceid = @igeofenceid  if @icategoryid != -1     select * geofencesimplereport icategoryid = @icategoryid  if @isiteid != -1     select * geofencesimplereport isiteid = @isiteid 

and geofencesimplereport database view.

but not work logically wrong. 4 separate selects geofencesimplereport.

i need have 1 read geofencesimplereport filters applied.

and don't want read data temporarily table/list in memory there lot of data.

is there way write query dynamically doing in linq?

you're thinking procedurally, , going through series of if-statements, rather approaching view set of data can filter @ once.

you can filter on original criteria related entrytime, exittime, etc., , each parameter provide filterable value (not -1) make sure id matches record in table. gave -1 value automatically make and statement true.

i sort of thing time passing in nullable parameters - if they're non-null filter on them - otherwise evaluate true , pass through.

use mydatabase go  create procedure [dbo].[xpt_getgeofencesummaryreport]     @iassetids idtable,     @igeofenceid int,     @icategoryid int,     @isiteid int,     @iassetid int select *     geofencesimplereport    entertime not null     , exittime not null    , minutesingeofence not null    , (@iassetid = -1 or iassetid in (@iassetids))    , (@igeofenceid = -1 or igeofenceid = @igeofenceid)    , (@icategoryid = -1 or icategoryid = @icategoryid)    , (@isiteid = -1 or isiteid = @isiteid)