score:3

Accepted answer

try

var recent = from p in dc.Properties
        orderby p.modtime descending
        where p.status == "Current"
        select new
       {
            rsub = (p.subNumber).ToString(),
            rnumber = (p.streetNumber).ToString(),
            rnum = string.IsNullOrEmpty((p.subNumber).ToString()) ? (p.streetNumber).ToString() : (p.subNumber).ToString() + "/" + (p.streetNumber).ToString(),
            rstreet = p.street,
            rsuburb = p.suburb,
            rurl = p.propertyURL,
        };

score:0

Just a ternary conditional aka ?: operator in the "select" should do:

select new
{
    house = p.subNumber != null
        ? p.subNumber + "/" + p.streetNumber
        : p.streetNumber;
    ...
};

This makes the assumption street number is always there (or it might result in "xxx/" or null). It also assumes that sub is null (not just empty) if truly not present.

If it starts to get "too complicated", consider the following (which has slightly different rules than above, those are left to be figured out):

select new
{
    house = PrettyHouseNumber(p.subNumber, p.streetNumber),
    ...
};

string PrettyHouseNumber(string sub, string street) {
    // ?: could also be used here as well, but since invoking the method
    // can be used as an expression itself, breaking it up like this also
    // allows the use of other constructs
    if (!string.IsNullOrEmpty(sub)) {
        return sub + "/" + street;
    } else {
        return "" + street; // NULL will go to "", if it can even ever come up
    }
}

Which should show how any expression, including a method call, can be used there -- pass it some data and get some data back :) While there are limits with expression trees and which ones can be efficiently turned into SQL, since this is just processing data already returned then there is nothing to worry about here.

Happy coding.

score:0

On the table level you could create a computed column; these are not stored - the value is 'created' when the column is queried.

CREATE TABLE [Customer]
(
     [subNumber] NVARCHAR(256),
     [streetNumber] NVARCHAR(256),
     [fullAddress] AS (CASE 
                      WHEN [subNumber] IS NULL THEN [streetNumber]
                      ELSE [subNumber] + N' ' + [streetNumber]
                    END)
);
GO

Or you can add it the table:

ALTER TABLE [Customer]
   ADD COLUMN [fullAddress]
       AS (CASE 
            WHEN [subNumber] IS NULL THEN streetNumber
            ELSE [subNumber] + N' ' + streetNumber
          END);
GO

Now the value will be directly-accessible from your EF model.


Related Articles