How does EF determine nullable parameters?

Question

I have a SQL stored procedure and I need to be able to pass a NULL as a value to one of its parameters to be used in a query like this:

create procedure sp
    @param1 varchar(30)
as
select * from table where field = isnull(@param1, field)

So I need to some how tell EF to make @param1 nullable. How do I do this?

Thanks!

In case it helps, the process I use with EF is:

1. Create SP
2. Update Model (edmx)
3. Add new function import
4. Generate new complex type by clicking the button
5. Run Custom Tool on separate template file (to generate POCO)

Answer 1

As a work-around, you could declare two separate stored procedures:

-- use this for non-null parameters
create procedure sp
    @param1 varchar(30)
as
select * from table where field = @param1

-- use this for null
create procedure sp_null
as
select * from table

and then you can write the desired abstraction in C#:

public ... GetSp(string param1)
{
    if (param1 == null)
        return ....sp_null();
    else
        return ....sp(param1);
}

Answer 2

Quick look and I found this on stackoverflow. Hope it helps.

Entity Framework 4.0 Entity SQL passing null ObjectParameter parameters