I'm using MSSQL, EntityFramework 6 and PredicateBuilder to create a dynamic, user controlled query mechanism to pull records from the DB. If you know a way in Entity to do this then great. Otherwise if I can get a way from MSSQL then I'll be a step closer in researching how to do this in EntityFramework.
I've got tables with parent identifier and key/value as the rows. The data looks like this:
ParentKey
ID ImportIdentifier
1 FFFFF-FFFFF-FFFFF-FFFFF
ProductTable
ID name value fkParentKey
1 Style Round 1
2 Color Blue 1
3 Package Boxed 1
4 ProdName Hair clip 1
*** The column "name" in this table is not static; it changes. So my original attempt with using MAX kind of worked until I realized that issue.
I'm trying to make the data storage as flexible as possible, but I guess with the flexibility I'm getting a whole another level of complexity with searching for that data.
I can query on one search term ok; it comes back with say rows of "style". But I only care about returning "Name" from the query, but searching on all terms.
So if this was in a single table with each of the above names as a column I would expect something like this:
select ProdName from ProductTable where Style='round' and Color='blue'
or
select ProdName from ProductTable where Package='Boxed' and Color='blue'
Both of which would give my expected result of "Hair clip"
However, since name is in the same table as well as the other keys I don't know how to query this; especially in EntityFramework. And since I'm using PredicateBuilder to make the dynamic query it adds another level of complexity to it.
I found the following SO questions, but they don't fit my scenario:
Linking rows together, not searching: Join a whole table as single row Get query's result as a single row?
I need to be able to search all data for terms as stated above, but I'm only needing the "non-searched" row of "ProdName" returned.
