How do I do an IsNull with entity framework?

Question

4.00/5 (1 vote)

See more:

I have a results page that displays a single record.

Question:
If there isn't a phone extension, how do I exclude (suppress) the phone extension itself and "ext." label from the returned data?

If there is an extension I want:
• Chris Compton 919-754-6000 ext. 6512 chris@notmymail.net
else
• Chris Compton 919-754-6512 chris@notmymail.net

If I don't get an answer this week I'll just code it into a t-sql stored proc like this:

SELECT ..., Phone, IsNull('ext. '+nullif([PhoneExt],''), ''), Email ...

(but I'd rather keep that logic out of the database)

Here's what I have (shortened) that works, but the label "ext." always shows:

C#

try { pk = Convert.ToInt32(Request.QueryString["id"]); }
catch (Exception) { pk = 0; }

var query = (
    from c in context.tEmployees
    where (c.pkEmployee == pk)
    orderby c.NameLast, c.NameFirst, c.NameMiddle, c.Agency
    select new
    {   Name = c.First + " " + c.Last,
        Telephone = c.Phone ,
        TelephoneExt = " ext. " + c.PhoneExt,
        Email = c.Email }
    );

if (query.Count() == 1)
{   ResultsRepeater.DataSource = query;
    ResultsRepeater.DataBind();
}
else
{ Response.Write("I'm sorry we could not find that employee, please try the search page again."); }

Also, if there's a better way to handle the variable "pk" I'm open to suggestions.

NOTE: that the t-sql and the code both work. If I've introduced an error shortening the code to post the question my apologies.

Edit 6/7/12:
ALL THREE solutions below are great and helped me!
I saw the first answer in time to deploy the working code (in QA) just before the end of my day yesterday.
I actually changed to the second one this morning (I try to avoid using "not" in an if clause).
I made a couple more improvements after reading number three (and the comments).
Thanks again!
-Chris C. 5/7/12 1 PM EDT

Posted 6-Jun-12 10:49am

JChrisCompton

Updated 7-Jun-12 7:09am

v2

Add a Solution

3 solutions

Solution 3

Clifford has the correct solution for the IsNull issue, but I thought I'd add an alternative way to parse the integer:

C#

if (!Int32.TryParse(Request.QueryString["id"], out pk))
{
    // The TryParse will default it to 0 on failure,
    // but I put this here in case you want to default to something else.
    pk = 0;
}

Posted 6-Jun-12 13:23pm

AspDotNetDev

Comments

AspDotNetDev 6-Jun-12 19:27pm

I'd also add that you don't need to do the query if the parse fails (which would save you a database hit), and that you should not be using Response.Write (use a label or something like that instead).

JChrisCompton 7-Jun-12 12:56pm

Yes + yes
I did NOT think about the db hit - I knew better though :-)
I did have a clean up the code period (Response.Write is left over from before the controls were there).
Thanks!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Accepted Answer · 2012-06-06T11:12:00

Solution 2

What about:

C#

TelephoneExt = string.IsNullOrEmpty(c.PhoneExt) ? " ext. " + c.PhoneExt : string.Empty;

Posted 6-Jun-12 11:12am

Zoltán Zörgő

Comments

AspDotNetDev 6-Jun-12 19:24pm

You have it reversed and Clifford beat you. I recommend you delete your answer.

JChrisCompton 7-Jun-12 13:12pm

That's okay, I realized and swapped them - thanks again!

Clifford Nelson · Accepted Answer · 2012-06-06T11:09:00

Solution 1

var query = (
    from c in context.tEmployees
    where (c.pkEmployee == pk)
    orderby c.NameLast, c.NameFirst, c.NameMiddle, c.Agency
    select new
    {   Name = c.First + " " + c.Last,
        Telephone = c.Phone ,
        TelephoneExt = c.PhoneExt != null ? " ext. " + c.PhoneExt : string.Empty,
        Email = c.Email }
    );

or

var query = (
    from c in context.tEmployees
    where (c.pkEmployee == pk)
    orderby c.NameLast, c.NameFirst, c.NameMiddle, c.Agency
    select new
    {   Name = c.First + " " + c.Last,
        Telephone = c.Phone ,
        TelephoneExt = ! string.IsNullOrEmpty(c.PhoneExt) ? " ext. " + .PhoneExt : string.Empty,
        Email = c.Email }
    );

Posted 6-Jun-12 11:09am

Clifford Nelson

Comments

Wonde Tadesse 6-Jun-12 20:21pm

Instead of " ext. " + c.PhoneExt, please do string.Concat("ext.",c.PhoneExt). :)

AspDotNetDev 6-Jun-12 20:36pm

Why? The compiler will do that for you, and for only 2 strings there is no benefit.

Wonde Tadesse 6-Jun-12 22:16pm

We know that the compiler will do that. However Good programming skill starts with smallest. Don't you think?

AspDotNetDev 6-Jun-12 22:28pm

With smallest what?

Wonde Tadesse 6-Jun-12 22:30pm

Things to do with smallest change of " ext. " + c.PhoneExt to string.Concat("ext.",c.PhoneExt).

AspDotNetDev 6-Jun-12 22:32pm

Only if it's an actual improvement, which this does not appear to be.

Wonde Tadesse 6-Jun-12 22:33pm

Actual improvement of what? Don't get it

AspDotNetDev 6-Jun-12 22:34pm

Your recommended change of using the Concat function rather than the + operator provides no actual improvement. That is to say, using the Concat function in this case offers no benefits over using the + operator. Neither approach is superior or inferior to the other in this example.

Wonde Tadesse 6-Jun-12 22:41pm

I'm not trying to make superior or inferior the "+" operator and Concat. I'm recommending a way of good programming practice. Don't forget it's a recommendation/suggestion. If we can avoid such kind of coding style for such small code we will not forget for the larger one.

JChrisCompton 7-Jun-12 12:34pm

I got an error trying this:
"LINQ to Entities does not recognize the method 'System.String Concat(System.String[])' method, and this method cannot be translated into a store expression."

Did I do it wrong, or is this functionality not available in EF 4.0?

AspDotNetDev 7-Jun-12 12:37pm

Oh yeah, I forgot that we are dealing with LINQ to Entities. Stick with the + operator.

Wonde Tadesse 7-Jun-12 21:18pm

I don't expect such kind of suggestion from you. In any case LINQ to Entities supports String.Conact funtionality.

AspDotNetDev 7-Jun-12 22:08pm

It makes perfect sense to use the plus operator. And the error from the OP indicates that it does not support Concat (that would only be apparent at runtime, and I don't feel like testing that right now).

Wonde Tadesse 8-Jun-12 22:39pm

OP indicated may be he doesn't know. I don't blame the OP, Since he asked as a question. I understand you don't feel like to test this issue, however from my experience, either in runtime/compile time, String.Concat 100% works with LINQ to Entities query.