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.

1
2
9/2/2018 6:25:11 PM

Accepted Answer

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,
} 
3
9/2/2018 7:06:10 PM

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, '')


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow