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
– Akash Kava, Commented May 13, 2011 at 7:58
Doesn't it give error that it is expecting a non null parameter at runtime? — Akash Kava
– Akash Kava, Commented May 13, 2011 at 17:22
@Akash - Yes, because the generated complex type has this parameter as string instead of "string?", hence the question. — O.O
– O.O, Commented May 13, 2011 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
– Akash Kava, Commented May 15, 2011 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
– O.O, Commented May 15, 2011 at 14:26

Timwi · Accepted Answer · 2011-05-04 22:40:42Z

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 4, 2011 at 22:40

answered May 4, 2011 at 22:27

Timwi

66.8k34 gold badges172 silver badges235 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

O.O Over a year ago

I could actually just add an additional bit param and have two code paths in the same sp, probably cleaner than two separate ones...

Community · Accepted Answer · 2017-05-23 11:43:42Z

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, 2017 at 11:43

CommunityBot

11 silver badge

answered May 4, 2011 at 22:05

Tom

1,2341 gold badge18 silver badges39 bronze badges

Comments

Steve · Accepted Answer · 2011-12-10 00:20:20Z

1

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.

edited Dec 10, 2011 at 0:20

user142162

answered May 12, 2011 at 10:35

Steve

1,50013 silver badges13 bronze badges

Comments

ZeNo · Accepted Answer · 2011-05-12 18:54:43Z

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, 2011 at 18:54

ZeNo

1,6582 gold badges16 silver badges28 bronze badges

5 Comments

O.O Over a year ago

Doesn't work. -1 for not even attempting it before guessing.

Akash Kava Over a year ago

@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.

ZeNo Over a year ago

@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

Akash Kava Over a year ago

@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.

O.O Over a year ago

@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.

Collectives™ on Stack Overflow

How does EF determine nullable parameters?

4 Answers 4

1 Comment

Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related