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:

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

How can you pass NULL to varchar(30) unless it is an optional parameter? — Akash Kava, May 13 '11 at 07:58
Doesn't it give error that it is expecting a non null parameter at runtime? — Akash Kava, May 13 '11 at 17:22
@Akash - Yes, because the generated complex type has this parameter as string instead of "string?", hence the question. — O.O, May 13 '11 at 18:54
@subt13 -1, please read little about C# strings, String is by default nullable, if you can prove string? exists or its even possible, I will remove my downvote. — Akash Kava, May 15 '11 at 11:15
@Akash - Ya, I realize that there is no string? and that string is already nullable. BTW, if you have an answer to this question feel free to post it instead of spamming me to death. — O.O, May 15 '11 at 14:26

Timwi · Answer 1 · 2011-05-04T22:40:42.060

2

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);
}

edited May 04 '11 at 22:40

answered May 04 '11 at 22:27

Timwi

65,159
33
165
230

I could actually just add an additional bit param and have two code paths in the same sp, probably cleaner than two separate ones... – O.O May 04 '11 at 22:35

score 1 · Answer 2 · edited May 23 '17 at 11:43

1

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

Entity Framework 4.0 Entity SQL passing null ObjectParameter parameters

edited May 23 '17 at 11:43

Community

1
1

answered May 04 '11 at 22:05

Tom

1,234
1
18
39

score 1 · Answer 3 · edited Dec 10 '11 at 00:20

Use DBNull.Value, I've done exactly this with one of my stored procedures. To call your procedure my code would look like:

List<ObjectParameter> objectParameterList = new List<ObjectParameter>();
if (string.IsNullOrEmpty(param1))
{
    object nullValue = DBNull.Value;
    objectParameterList.Add(new ObjectParameter("param1", nullValue));
}
else
{
    objectParameterList.Add(new ObjectParameter("param1", param1));
}

context.ExecuteFunction("MyEFModel.sp", objectParameterList.ToArray());

Hopefully this helps.

score 0 · Answer 4 · answered May 12 '11 at 18:54

0

set the default value in Stored procedure as NULL.

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

answered May 12 '11 at 18:54

ZeNo

1,648
2
15
28

Doesn't work. -1 for not even attempting it before guessing. – O.O May 12 '11 at 19:10
1

@subt13, somebody is trying to help you, you are not paying anyone to help you here so please try to say thank you for help, no one is going to stop their work and attempt your solution because it is your job !! not Zeno's. This isne a work place where Zeno will get money for telling you solution. – Akash Kava May 13 '11 at 07:49
2

@subt13.. I faced something similar issue once and it got resolved by setting default parameter in stored procedure itself that's why I shared it. I didn't have any environment set up in my home computer so didn't get a chance to test it. I am sorry for the time you have wasted while testing this. @Akash.. thanks for the support – ZeNo May 13 '11 at 20:02
@subt13, Well String is already nullable, what more do you want? I was stupid to ignore the String parameter type but now I realize, you can always pass null to string parameter. – Akash Kava May 15 '11 at 11:14
2

@ZeNo - You might mention that next time in your solution or provide some proof that it works. I had already tried this before you even posted your "answer" and it doesn't work. Maybe now you can see why it appears to be just a "guess" that doesn't work and will lead to wasted time. – O.O May 15 '11 at 14:28

How does EF determine nullable parameters?

4 Answers4