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.
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,
}
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
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow