Linq: Querying a "Virtual" columns created from one split column

Question

Hi all,

I'm working with a pretty nasty looking, but non-changable db.

We've got a table, called "Locations" with column, "Position"

This column has values like "A SHELF 5"

Which means "Case A, Shelf 5". In an ideal world, I'd have a Case and Shelf column, with the values "A" and 5, respectively.

What I'm wondering, is if there's a way to define some "Virtual" (maybe not the right word) columns, in my entity classes, so that once I do that, I can just query the table as if those columns existed (Selects, Where clauses, Group By, etc)?

Admittedly, I'm pretty new to Linq, so I'm not even using the right technical terms to describe what I want to do. Which makes it harder to find the answers. So if someone just wants to point me to any introductory documentation that would even help me get started, I'd consider that a valid answer.

To make it more complicated, the column is only structured as above, depending on the value of some other columns, but I'll not go there.

thanks for taking the time to read this. Past experience tells me the SO crew is the best!

Answer 1

I'm used to Ling-to-Sql rather than Linq-to-Entities, but this should work

partial class Location
{
     public string Case 
     {
         get
         {
             int pos = Position.IndexOf(' ');
             if (pos == -1)
                 throw new Exception();
             return Position.Substring(pos);
         }
     }

}

Answer 2

Anything you do to split the column into multiple other columns within Entity Framework isn't going to translate into SQL within your queries. That means that anything filtering on the derived columns is going to do its filtering on the client after retrieving all the rows not filtered by other where-clause elements. This may not be a big deal, but it's something you need to bear in mind.

Doing a calculated column itself is a simple matter of creating a new property in a partial code file (as demonstrated by James Curran's answer). Your objects should use that property just fine, even in queries.

Personally, though, in your situation, I'd look at creating views at the database level. A view of the Locations table could be setup to break the offending column up into two other columns without too much difficulty and the additional overhead can payoff in the long term. You'll have to watch your updates, but for most purposes this would serve your purposes.