I have one table Person:
Id Name 1 Person1 2 Person2 3 Person3
And I have its child table Profile:
Id PersonId FieldName Value 1 1 Firstname Alex 2 1 Lastname Balmer 3 1 Email email@example.com 4 1 Phone +1 2 30004000
And I want to get data from these two tables in onw row like this:
Id Name Firstname Lastname Email Phone 1 Person1 Alex Balmer firstname.lastname@example.org +1 2 30004000
- What is the most optimized query to get these vertical (key, value) values in one row like this? Now I have a problem that I done four joins of child table to parent table because I need to get these four fields. Some optimization is for sure possible.
- I would like to be able to modify this query in easy way when I add new field (key,value). What is the best way to do this? To create some StoreProcedure?
I would like to have strongly types in my DB layer (C#) and using LINQ (when programming) so it means when I add some new Key, Value pair in Profile table I would like to do minimal modifications in DB and C# if possible. Actually I am trying to get some best practises in this case.