C#, LINQ, SQL: Compound columns

Question

I have a LINQ entity that I need to create a special string value for. It is 7 different values separated by "-". Some of the fields used are in that table, while some of them are in different tables. I would like to add this field to the entity somehow so that I don't have to create this string myself every time I need it.

I was thinking I could add it to the entity with a partial class like this:

    public string SpecialField
    {
        get
        {
            return string.Format("{0}-{1}-{2}-{3}-{4}-{5}-{6}",
                                 TableId,
                                 Number,
                                 ForeignTableA.Date,
                                 ForeignTableB.Name,
                                 ForeignTableC.ForeignTableD.Name,
                                 ForeignTableB.ForeignTableE.Name,
                                 ForeignTableB.Number ?? 0);
        }
    }

However, after writing that, I became a bit unsure how that would work. Cause, unless I am mistaken, this would result in a database query every time that value is used for every item. And would it work to for example use that field in a Where clause?

I need to use that field in a Where clause, and I would like it to happen at the server so I don't fetch more data than I need to.

How would you best do this?

Answer 1

I think you'll need a let clause with a dash of anonymous types. I haven't tested this, but something like this might do the trick:

var query = from master in MasterTable
            join foreignA in ForeignTableA on ...
            join foreignB in ForeignTableB on ...
            let special = string.Format ("...", master.TableID, ...)
            where special.Contains ("foo")
            select { 
                 // ...
                 string specialResult = special,
                 // ...
            }

Answer 2

You could create a view in the database to this for you. This way SQL Server takes care of it and you can query on it more efficiently

Answer 3

How about caching the value after you've read it once? It won't be ultra-efficient like it would be if you got all of these values at once, but it would prevent multiple attempts to get the same value. (Of course, this only works if the SpecialField content doesn't change while in use.)

protected string specialField = null;

public string SpecialField
{
    get
    {
        if (specialField == null)
        {
            specialField = string.Format("{0}-{1}-{2}-{3}-{4}-{5}-{6}",
                             TableId,
                             Number,
                             ForeignTableA.Date,
                             ForeignTableB.Name,
                             ForeignTableC.ForeignTableD.Name,
                             ForeignTableB.ForeignTableE.Name,
                             ForeignTableB.Number ?? 0);
        }
        return specialField;
    }
}