Can anyone modify this LINQ to SQL query so it only returns one instance of each row?

Question

var nodes = (from n in db.Nodes
                 join st in db.SessionTrackings on n.NodeID equals st.NodeID
                 where st.UserID == userid && st.GroupID == groupid
                 select n);

IDictionary<int, bool> trackingData = new Dictionary<int, bool>();

foreach (Node n in nodes)
{
    trackingData.Add(new KeyValuePair<int, bool>(n.ID, true));
}

I keep getting a 'that key was already added' because there could be many SessionTrackings per Node, however I just want to get back all the Nodes that have at least 1 SessionTracking in existence for the NodeID but I don't need to get the Nodes more than once. If there are 4000 SessionTrackings for a Node (say ID = 45) I still only one 1 instance of Node 45 in my IQueryable. How can I modify my query for this? Please don't worry about why I need it in a Dictionary that's just the way it is.

Answer 1

You just need to tell the query engine that you only want distinct instances of your objects:

var nodes = (from n in db.Nodes 
             join st in db.SessionTrackings on n.NodeID equals st.NodeID 
             where st.UserID == userid && st.GroupID == groupid 
             select n).Distinct();

IDictionary<int, bool> trackingData = nodes.ToDictionary(n => n.ID, n => true);

If you don't need the nodes query for anything else, you can combine the statements like this:

IDictionary<int, bool> trackingData =
            (from n in db.Nodes 
             join st in db.SessionTrackings on n.NodeID equals st.NodeID 
             where st.UserID == userid && st.GroupID == groupid 
             select n.Id)
            .Distinct()
            .ToDictionary(i => i, i => true);

Answer 2

This is how you can get the first session:

var nodes = (from n in db.Nodes 
    join st in db.SessionTrackings on n.NodeID equals st.NodeID 
    where st.UserID == userid && st.GroupID == groupid 
    select n)
    .GroupBy(n => n.ID)
    .Select(g => g.First());

Answer 3

I think you would need to do something like

var nodeids = (from n in db.Nodes
join st in db.SessionTrackings on n.NodeID equals st.NodeId
where  st.UserId.equals(userid) && st.GroupID.equals(groupid)
select new {Id = n.Id})).distinct();

That should give you everything you need for the iteration, without repeats.