CODEWITHSUNDEEP
linqlinq-to-sqljoinsubquery
Andy
Andy

Reputation: 856

How do I return a table in LINQ that relies on a join/subquery?

I need the fields in 1 table contingent on 1 property matching rows in another table. I can write this query in SQL with a subquery as such:

SELECT *
FROM Table1
WHERE Property1 IN
(
    SELECT Property1
    FROM Table2
    WHERE Property0 = 1
)

But I read here that it's less complicated and just as easy to write with a join, which I did. However, so far I'm unable to return just Table1 as I'd like since I'm using a join, which if I'm not mistaken, requires me to create this anonymous type, as below. What I did here works (I created another object with the same properties of Table1 I need), but I can't help thinking there's a better way to do this.

Table1.Join(Table2, t1 => t1.Property1, t2 => t2.Property1, (t1, t2) => new
{
    t1.Property1,
    t1.Property2,
    t1.Property3
})
.Select(ob => new UnnecessaryObject
{
    Property1 = ob.Property1,
    Property2 = ob.Property2,
    Property3 = ob.Property3
}

I also tried just creating a Table1 in the .Select part, but I got an error about explicit construction not being allowed.

Just to clarify, I'd like to be able to return the IQueryable of type Table1, which it seems like I ought to be able to do without having to create UnnecessaryObject...but I'm still pretty new to LINQ, so I'd appreciate any help you can offer. Thanks in advance.

Upvotes: 1

Views: 771

Answers (2)

Coding Flow
Coding Flow

Reputation: 21881

You could just do:

from t1 in table1
join t2 in table2 on t1.property1 equals t2.property1
select t1;

That would return a collection of table1 objects. This assumes from your example table1 is a collection of table1 objects and table2 is a collection of table2 objects.

Upvotes: 3

Sander Rijken
Sander Rijken

Reputation: 21615

The best translation of your original query I can come up with is:

from item in context.Table1
where context.Table2
    .Where(x => x.Property0 == 0)
    .Any(x => x.Property1 == item.Property1)
select item

This selects all items from Table1, where there's an item with matching Property1 and Property0 == 0 from Table2

It can also be solved with a join indeed. To get an efficient join, you need to have a relation between the two tables. Then you can do something like assuming the relation is called RelatedItems:

from item in context.Table1
join relatedItem in item.RelatedItems
    on item.Property1 equals relatedItem.Property
where relatedItem.Property0 == 0

select item

This is equivalent to the SQL:

SELECT * 
FROM Table1
JOIN Table2 ON Table1.Property1 = Table2.Property1
WHERE Table2.Property0 = 0

Upvotes: 2

Related Questions