In T-SQL you can use CROSS APPLY
to get all possible variations between the table left and right from the statement. Now I have the following situation in C#
and I hope there is a way to solve my problem using LINQ-to-Objects.
I have a list with TestData
objects (like below) which is similar to the KeyValuePair<string, object>
object (Just a Key
and a Value
property):
The key can be everything and there can be multiple objects with the same key.
IList<KeyValuePair<String, Object>> objects;
// Content of list
// # | Key | Value
// 1 | "A" | 1
// 2 | "A" | 2
// 3 | "A" | 3
// 4 | "B" | 4
// 5 | "B" | 5
// 6 | "C" | 6
// 7 | "D" | 7
// 8 | "D" | 8
I have also a list of requested keys:
IList<String> requestedKeys = new List<string>() { "A", "D" };
Now I want to have all possible combinations of KeyValuePair objects between the keys in the requestedKeys
list.
IList<IList<KeyValuePair<String, Object>>> result = ...
// Content of 'result' will be in this example 6 lists with each 2 KeyValuePair objects
// # | "A" | "D" | (If there are more in the requestedKey list then there are more KeyValuePair items in the innerlist.)
// 1 | 1 | 7 |
// 2 | 2 | 7 |
// 3 | 3 | 7 |
// 4 | 1 | 8 |
// 5 | 2 | 8 |
// 6 | 3 | 8 |
Is it possible to solve my problem using LINQ-to-Objects. If not can you tell me the most efficient way to build it anyway.
EDIT 1:
To make more clear what the result should be:
I want to have a LINQ-to-Objects query something like this:
@Joanna thanks for the tip about multiple from
s but the problem is: With this syntax you cannot have a dynamic amount of from
s. In my case I need as many from
s as items in the requestedKeys
list
var result =
from listA in objects.Where(m => m.Key == "A")
from listD in objects.Where(m => m.Key == "D")
// from .....
// from .....
// overhere as many froms as items in 'requestedKeys' list
select new [] { listA, listD /*, All other lists */ }
I found the solution myself:
It is a very complex join in LINQ because each item in the requestKeys list requires an extra cross join. Regarding to the given example list, the result should be objects.Count(m => m.Key == "A") * objects.Count(m => m.Key == "D")
(result is 3 * 2 = 6). Each extra item in the list causes an extra multiply of the whole result set.
So this is the result:
// The result list
IEnumerable<IList<KeyValuePair<char, int>>> result;
// If there are no requestedKeys there is no result expected
if(requestedKeys.Count() > 0)
{
// Loop through all request keys to cross join them together
foreach (var key in requestedKeys)
{
if (result == null)
{
// First time the innerlist List<KeyValuePair<char, int>> will contain 1 item
// Don't forget to use ToList() otherwise the expression will be executed to late.
result = objects.Where(m => m.Key == key).Select(m => new List<KeyValuePair<char, int>>() { m }).ToList();
}
else
{
// Except for the first time the next subresult will be cross joined
var subresult = objects.Where(m => m.Key == key).Select(m => new List<KeyValuePair<char, int>>() { m });
result = result.Join(
subresult,
l1 => 0, // This and the next parameter does the cross join trick
l2 => 0, // This and the previous parameter does the cross join trick
(l1, l2) => l1.Concat(l2).ToList() // Concat both lists which causes previous list plus one new added item
).ToList(); // Again don't forget to 'materialize' (I don't know it is called materialization in LINQ-to-Objects
// but it has simular behaviors because the expression needs to be executed right away)
}
}
}
return result;
Unfortunately it is not completely LINQ so if someone know an better solution. Please comment me or answer my question :)