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