LINQ Union head scratching

akakjs · 4 Apr 2008 at 15:33

Hi,

I'm busy learning the new LINQ features in .Net and I've come across a problem that I can't seem to find an answer for.

I'm trying to use linq to work with a undirected graph, I have two tables in my database called SimpleNodes (for the nodes) & Relations (for the edges), these are mapped to "Node" and "RelationShip" classes respectively. The problem is in the relationships table, which looks like this (rel_from and rel_to are the PK's of the nodes):

rel_key | rel_from | rel_to
1 | 1 | 2
2 | 1 | 3
3 | 2 | 3

Now the gotcha is as this is an undirected each edge is bi-directional, but I want to implement this this with out creating two records for each edge (do to the number of edges I would like to have). If I was writing the SQL to find the related nodes of a specific node I would do this:

select rel_to as 'related node key'
from relations
where rel_from = @current_node_key
union
select rel_from as 'related node key'
from relations
where rel_to = @current_node_key

this would get me a table with a single column of 'related node key', containing all the nodes related to the current node (e.g. the current node's key is in a from or to clause connected to this node).

The problem is I can figure out how to make LINQ do this aliasing of the column name. The nearest I've gotten in LINQ is:

var nodes = from n in ndc.Nodes
select new {
currentnode,
Nodes = from node in (from rl in ndc.Relations
where rl.FromKey == n.Key
select new {Name = rl.To}
).Union(
from rl in ndc.Relations
where rl.ToKey == n.Key
select new { Name = rl.From }
)
select node
};

for this Linq-To-Sql generates

SELECT [t0].[node_key] AS [Key], [t0].[node_name] AS [Name], [t8].[test], [t8].[
node_key] AS [Key2], [t8].[node_name] AS [Name2], (
SELECT COUNT(*)
FROM (
SELECT [t11].[test], [t11].[node_key], [t11].[node_name]
FROM [rel_relation] AS [t9]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t10].[node_key], [t10].[node_name]
FROM [SimpleNode] AS [t10]
) AS [t11] ON [t11].[node_key] = [t9].[rel_to]
WHERE [t9].[rel_from] = [t0].[node_key]
UNION
SELECT [t14].[test], [t14].[node_key], [t14].[node_name]
FROM [rel_relation] AS [t12]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t13].[node_key], [t13].[node_name]
FROM [SimpleNode] AS [t13]
) AS [t14] ON [t14].[node_key] = [t12].[rel_from]
WHERE [t12].[rel_to] = [t0].[node_key]
) AS [t15]
) AS [value]
FROM [SimpleNode] AS [t0]
LEFT OUTER JOIN (
SELECT [t7].[test], [t7].[node_key], [t7].[node_name], [t7].[rel_from], [t7]
.[rel_to]
FROM (
SELECT [t3].[test], [t3].[node_key], [t3].[node_name], [t1].[rel_from]
FROM [rel_relation] AS [t1]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t2].[node_key], [t2].[node_name]
FROM [SimpleNode] AS [t2]
) AS [t3] ON [t3].[node_key] = [t1].[rel_to]
UNION ALL
SELECT [t6].[test], [t6].[node_key], [t6].[node_name], [t4].[rel_to]
FROM [rel_relation] AS [t4]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t5].[node_key], [t5].[node_name]
FROM [SimpleNode] AS [t5]
) AS [t6] ON [t6].[node_key] = [t4].[rel_from]
) AS [t7]
) AS [t8] ON ([t8].[rel_from] = [t0].[node_key]) AND ([t8].[rel_to] = [t0].[
node_key])
ORDER BY [t0].[node_key]

which throws an error as it's trying to select the column "rel_to" from the union of "SELECT [t3].[test], [t3].[node_key], [t3].[node_name]" And "SELECT [t6].[test], [t6].[node_key], [t6].[node_name], [t4].[rel_to]" which won't return the rel_to column.

Does anyone have any ideas how I could force LINQ to alias the rel_to and rel_from columns, so I can get the bi-directional edges working?

Many thanks if you've gotten this far in my fairly long post

akakjs