sql server - Query a key/value table as a single row using EntityFramework 6

Question

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.

Answer 1

等待大神答复