I am new to c#, and am using Linq To SQL in Linqpad to fetch data from the database. In my database I have many columns which for legacy reasons, are names Col1, Col2, Col3 etc.
At the moment I have to remember which Column references which value, to enable me to extract data.
Instead of having to write:
Clients.Where(c => c.Col1443 == true)
I would like to be able to do something like the following:
var HasRedEyes= "Col1443";
Clients.Where(c => c.HasRedEyes == true)
Please note that I need this to translate to SQL, as I need to fetch this data from the database, this data is not localised.
You really should find a way to A. Rename your columns and B. Reduce the number of them (1,000+ is a bit excessive). It's been a while since I used LINQ to SQL but if all you need is querying abilities, could you define a view with the columns aliased instead?
That aside, one way to solve this would be to define named expressions that represent predefined filters. For example, assuming the class is named Client:
Expression<Func<Client, bool>> hasRedEyes = c => c.Col1443; // == true is implicit
var query = Clients.Where(hasRedRyes).ToList();
But note those are only composable if you AND them via successive calls* to Where:
Expression<Func<Client, bool>> hasRedEyes = c => c.Col1443;
Expression<Func<Client, bool>> hasBrownHair = c => c.Col1567 == "brown";
var query = Clients.Where(hasRedEyes).Where(hasBrownHair).ToList();
If you need an OR you'd have to define the or'd filter as its own predefined expression*, for example:
Expression<Func<Client, bool>> hasRedEyesOrBrownHair = c => c.Col1443 || c.Col1567 == "brown";
var query = Clients.Where(hasRedEyesOrBrownHair).ToList();
(* The alternative to the last bit would be to either use a tool like LINQKit or to manually use the Expression API. With the latter you'd have to take the two seperate conditions, retrieve the body of the individual lambdas expressions, use an expression visitor to replace the parameter of one of them to match the other, call Expression.Or and finally Expression.Lambda to create a new filter you could pass to Where. This same concept can be applied to and'ing as well, though it all gets a bit tricky.)