I am looking for some ideas on the best way to solve a class of similar problems. I am using Linq2SQL below, but I suspect the same issues would apply to linq2Entities.
I have a database table that has some 30 columns. This is an old application that is using Linq2Sql for the data layer (few hundred tables). I had some very common formatting that I wanted to encapsulate in a convenient place, so I put them into properties on the table using a partial class. For example, say a Users table with 30+ columns including a user's first, middle, and last name in separate columns and so one property called FirstnameLastname concatenates the first and last names with a space ("Sam Spade"), and another property called LastnameFirstname concatenates them in a different way ("Spade, Sam"), and others that, for example, might have to be smart about optional middle names, honorifics, etc. If it's not obvious, the reason to encapsulate these is that I can make sure the name is always formatted correctly throughout the app, and I can change the formatting in one place to change it everywhere (or implement language- or locale-specific differences in the formatting).
These two properties look like this currently:
public partial class User : IAudit, ICloneable
{
...
public string FirstnameLastname => Firstname + " " + Lastname;
public string LastnameFirstname => Lastname + ", " + Firstname;
...
}
There are other properties that are much more complicated, but are variations on the theme.
This works great, and I can write something extremely readable like this:
dc.Users.Single(u => u.UserId == 20).FirstnameLastname;
or
from u in Users
where u.UserId == 20
select u.FirstnameLastname
However, what I didn't think about at the time was that this generates a query that pulls down all 30+ columns of the table for the one row where UserId is 20. This is wasteful, but not particularly annoying. However, let's say I'm doing a join between seven tables and I use this property in a very large select. Now, I'm adding 30 columns to the result set for every row when I only need to add Firstname and Lastname (and linq is throwing away the rest of the data).
If instead I write:
dc.Users.Where(u => u.UserId == 20).Select(u => u.Firstname + " " + u.Lastname)
then it only brings down two columns, Firstname and Lastname (in fact, in this super-simple example it encodes the expression in SQL, which might or might not be possible for more complex properties).
The question is, is there a way I can hint to Linq2Sql that I only need the few columns the property requires? Is there a better way to do this that will result in clean, self-documenting code like these properties? It seems like predicates are what I need, but I'm not seeing how to make that work. Obviously, these properties are also used outside of linq statements, when I have a user object at hand and want to get the proper formatting.
I'm looking for a general methodology discussion, not necessarily a solution covering this specific example.
Thanks for reading!
