If a field contains null then entire concatenate result is null

Question

If a field contains null then entire concatenate result is null

asp.net-core c# entity-framework-core postgresql

Question

I am trying to concatenate for a label. If a field contains null then entire concatenate result is null.

[HttpGet]
public ActionResult PMAByPC(string PartnerCode)
{
    var result = (from N in _POSContext.PMAs
                  where (N.PartnerCode == PartnerCode)
                  select new 
                  { 
                      label = N.Address1 + " | " + N.Address2 + " | " + N.City, 
                      id = N.ID 
                  });

    return Json(result);
}

Here, if data is not present in of the fields, label becomes null.

I tried with

select new { label = N.Address1 ?? "?" + " | " + N.Address2 ?? "?" + " | " + N.City ?? "?", id = N.ID }

then it takes only N.Address1 value and ignores the rest of the fields.

Ramesh

Popular Answer

This is the standard compliant and reasonable behavior: if you concatenate a string with an unknown string, the result is unknown.

Use the coalesce function for that:

coalesce(col1, '') || coalesce(col2, '')

Laurenz Albe

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 9/2/2018 7:06:10 PM

Looks like this is a standard SQL string concatenation behavior (the same happens with SqlServer database).

If you want to evaluate the concatenation server side (database), you need to convert null to empty string "" (or something else) using the ?? operator. Similar to your attempt, but you've missed the C# operator precedence. The way you wrote it is equivalent of

N.Address1 ??
(
  ("?" + " | " + N.Address2) ??
  (
      ("?" + " | " + N.City) ?? "?"
  )
)

which is not what was the intent.

You can avoid such issues by enclosing similar conversions with brackets:

select new
{
    label = (N.Address1 ?? "?") + " | " + (N.Address2 ?? "?") + " | " + (N.City ?? "?"),
    id = N.ID,
}

If a field contains null then entire concatenate result is null

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related