Hi all,
Little question, I know an answer, but I'm open to suggestions or alternatives (or confirmation). My solution sounds a bit complicated, but I can't really think of anything else that's flexible enough.
The problem is this, I have some entity, MyEntity, of which each record can be combined with one or multiple records of MyEntity.
For example, I have A, B, and C.
Possible combinations are now:
A
B
C
A, B
A, C
B, C
A, B, C
Now here's the challenge, A, B and C have a percentage, say 100%.
However, when A is coupled with B their percentages change, for example A 40% and B 60%
A = 100%
B = 100%
C = 100%
A = 40%, B = 60%
A = 40%, C = 60%
B = 45%, C = 55%
A = 20%, B = 30%, C = 50%
There is no logic in the percentages, it's just a given percentage.
However, the percentages may change over time and an additional D may be added, changing not only the possible combinations, but also the corresponding percentages.
So I'm looking for the easiest way to model this in SQL Server.
I'm thinking:
MyEntity (Id, Description)
MyEntityCombination (Id, Description)
MyEntityCombinationValue (Id, MyEntityCombinationId, MyEntityId, Percentage)
And then comes the next challenge, in my C# code I get B and C and I now need to get the corresponding percentages for B and C (45% and 55% in the example).
I'm guessing this should be fairly easy using Entity Framework and LINQ.
Something like:
var entities = new[] { bId, cId };
var percentages = context.MyEntityCombinations.Where(c => c.Values.Count() == entities.Length && c.Values.All(v => entities.Contains(MyEntityId)).Values;
var bPercentage = percentages.Single(p => p.MyEntityId == bId).Percentage;
What I have tried:
I've thought about it, just curious for (easier) alternatives.