Linq C# Join tables if value of two fields exists in another table

Question

I have two tables that I want to join using a linq query

Table 1 called activities has the following fields ID Code Description

Table 2 named pred has the following fields task_id pred_task_id pred_type

I want the linq query to join both tables and returns values if and only if both task_id and pred_task_id exists in Table 1.ID

I had a stab at it with no success

from pre in pred.AsEnumerable()
join act in activities.AsEnumerable()
on pre.Field<string>("task_id") equals act.Id
where (pre.Field<string>("task_id") == activity.Id)
select new
{
    Pred = getTaskbyID(pre.Field<string>("pred_task_id"), activities),
    Succ = getTaskbyID(pre.Field<string>("task_id"),activities),
    RelationshipType = pre.Field<string>("pred_type")

});

This code does not execute what I am aiming to achieve. It returns list of all activities if the pred.task_id exist in activities.id. I want to make another filter to ensure that pred.pred_task_id also exists in the activities table.

If you're making that JOIN, won't pre.Field<string>("task_id") already equal activity.Id? — Robert Harvey
– Robert Harvey, Commented Dec 16, 2016 at 16:37
I have tried adding another join which did not resulted in achieving my objectives. — Hassan Emam
– Hassan Emam, Commented Dec 17, 2016 at 7:41
Robert, Yes the join will make task_id equal to activity.Id but what I want to achieve is a bit different. I want to join tables is the above condition is met and if pred_task_id which is another field values exist in activity.Id — Hassan Emam
– Hassan Emam, Commented Dec 17, 2016 at 7:44

Mark Cidade · Accepted Answer · 2016-12-16 16:39:34Z

1

You need to change your where clause:

where activities.Any(a=> a.Id == pre.Field<string>("pred_task_id"))

edited Dec 16, 2016 at 16:39

answered Dec 16, 2016 at 16:34

Mark Cidade

100k33 gold badges231 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hassan Emam Over a year ago

Mark, I have tried it and your code is working exactly as it is expected to perform. Many thanks

Collectives™ on Stack Overflow

Linq C# Join tables if value of two fields exists in another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related