ironman ironman - 7 months ago 75
SQL Question

c# linq sql coalesce conversion

I'm trying to convert an existing sql stored proc to use linq. The stored proc uses coalesce() while building up the 'where' clause.

Below is what I've tried.

Can some give advice / other on the correct way to approach this conversion?

------- sql

if @a is not null
set @WhereClause = @WhereClause + ' and v.a_number = ''' + @a + ''''


if @b is not null
set @WhereClause = @WhereClause + ' and v.b_number = ''' + @b + ''''


if @aDateMin is not null
set @WhereClause = @WhereClause + ' and coalesce(v.a_end_dt, v.a_start_dt, ''1/1/1753'')
>= ''' + convert(varchar(10), @aDateMin, 101) + ''''


if @aDateMax is not null
set @WhereClause = @WhereClause + ' and coalesce(v.a_start_dt, v.a_end_dt, ''12/31/9999'')
<= ''' + convert(varchar(10), @aDateMax, 101) + ''''

set @Sql = from Results v
@WhereClause ;';

--print @Sql
exec sp_executesql @Sql


-----linq

var aNumber = "";
var bNumber = "";
DateTime? aDateMin = null;
DateTime? aDateMax = null;

var query = from v in searchResults
select v;

if(!String.IsNullOrEmpty(aNumber )){
query = query.Where(v => v.a_number == aNumber);
}

if(!String.IsNullOrEmpty(bNumber)){
query = query.Where(v => v.b_number == bNumber);
}


// this is where I'm having issue with the conversion.
if(aDateMin.HasValue){

query = query.Where(v =>
v.a_end_dt.HasValue ? v.a_end_dt >= aDateMin : v.a_start_dt.HasValue ? v.a_start_dt <= aDateMin : "1/1/1753");

}

Answer

I've not tried it, but could you try something like:

var dt = new DateTime(1753, 1,1);
query = query.Where(v =>  (v.a_end_dt ?? v.a_start_dt ?? dt) >= aDateMin);