I have a SQL Query that I'm having trouble converting to LINQ query:
SELECT DISTINCT Nodes.NodeName, NodeConfig.IPAddresses, NodeConfig.InSCOM, NodeConfig.InOrion, NodeConfig.OrionCustomerName, NodeConfig.OrionApplication, NodeConfig.NodeID
FROM Tags INNER JOIN
TagToNode ON Tags.TagID = TagToNode.TagID RIGHT OUTER JOIN
NodeConfig INNER JOIN
Nodes ON NodeConfig.NodeID = Nodes.NodeID ON TagToNode.NodeID = NodeConfig.NodeID
WHERE (NodeConfig.Session = '7/3/2014 1:46:33 PM') AND (NodeConfig.InSCOM = 0)
That returns 1076 rows.
I tried to write the LINQ equivalent:
var list1 = (from t in mldb.Tags
join tn in mldb.TagToNodes on t.TagID equals tn.TagID into tagJoin
from tj in tagJoin.DefaultIfEmpty()
join nc in mldb.NodeConfigs on tj.NodeID equals nc.NodeID
join n in mldb.Nodes on nc.NodeID equals n.NodeID
where (nc.Session == @"7/3/2014 1:46:33 PM") && (nc.InSCOM == 0)
select new { Customer = nc.OrionCustomerName, DeviceName = n.NodeName, DeviceType = nc.OrionApplication, IPAddress = nc.IPAddresses, NodeID = n.NodeID }).Distinct().ToList();
That returns 183 rows.
I have tried converting the query to inner joins as suggested by some when I searched for solutions on this site. The original query implements a SQL "RIGHT OUTER JOIN" which from what I've read left/right isn't supported in LINQ but joins can be done.
The tables that I'm pulling from have primary keys as follows:
[DataServiceKey(new string[] { "NodeID", "TagID" })]
public partial class TagToNode { }
[DataServiceKey(new string[] { "NodeID" })]
public partial class Node { }
[DataServiceKey(new string[] { "TagID" })]
public partial class Tag { }
[DataServiceKey(new string[] { "ConfigID" })]
public partial class NodeConfig { }
The relationship is that Nodes have many NodeConfigs, and many Nodes are Tagged with many tags.
Can someone help me with the query logic?
OK so I took a step back and restructured the query. I created an inner join first, and then left-joined that to the main query.
I have a list of computers in a data base that are "tagged" in my application. I wanted to be able to search the tag names in the database via the many-to-many relationship between a tag and a device, where I have a join table in the middle named "TagToNode".
The distinct selection just weeds out the dupes in the end, the idea is to get ALL of the computers (nodes) even if they're not tagged with anything.
LINQ
var tags = (from tn in mldb.TagToNodes
join t in mldb.Tags on tn.TagID equals t.TagID
select new { tn.TagID, tn.NodeID, t.TagName, t.AssocUser });
return (from nc in mldb.NodeConfigs
join n in mldb.Nodes on nc.NodeID equals n.NodeID
join t in tags on n.NodeID equals t.NodeID into nj
from tg in nj.DefaultIfEmpty()
where nc.Session == sc.SessionName && n.NodeActive == 1 && ((tg.TagName.Contains(sc.SearchTerm) && (tg.AssocUser.Contains(windowsId) || (tg.AssocUser == null || tg.AssocUser == ""))) || (n.NodeName.Contains(sc.SearchTerm)) || (nc.OrionCustomerName.Contains(sc.SearchTerm)) || (nc.IPAddresses.Contains(sc.SearchTerm)))
select new NodeInfo { Customer = nc.OrionCustomerName, DeviceName = n.NodeName, DeviceType = nc.OrionApplication, IPAddress = nc.IPAddresses, NodeID = n.NodeID }).Distinct().ToList();
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments