Need help with LINQ to SQL WHERE clause on external table

Question

Let's say I have these two tables:

ParentDataItem
    ParentDataItemID
    ...some other fields...

ChildDataItem
    ChildDataItemID
    ParentDataItemID (foreign key)
    Name
    Value

Now I want to select any ParentDataItems that have a ChildDataItem with the given name and value.

I know that I had the first approach, which was something like this:

// db is the data context object
db.ParentDataItems.Where(p => p.ChildDataItems.Where(c => c.Name == "XXX" && c.Value == "XXX"));

I prefer lambda syntax, but both.

Answer 1

If there is already a relationship between them (because you, for example, set one of them in the designer), you should simply do:

var foo = db.ParentDataItems.Where(p => p.ChildDataItems.Any(c => c.Name == "value");

Which will receive any parent data items that have children with a name matching "value".

, ( ):

var foo = db.ParentDataItems.Join(db.ChildDataItems.Where(c => c.Name == "value"),
                                  p => p.ChildDataItemId,
                                  c => c.ParentDataItemId,
                                  (parent, child) => parent);

Answer 2

LINQ:

var foo = from p in ctx.Parent
          join c in ctx.Children on c.ParentDataItemID equals p.ParentDataItemID
          where c.Name = "Foo"
          select p;

LINQPad LINQ.