Complex SQL Subquery to LINQ

Question

I have this SQL query that is just impossible to get going in LINQ.

select * from attribute_value t0
where t0.attribute_value_id in
(
    select t1.attribute_value_id from product_attribute_value t1
    where t1.product_attribute_id in
    (
        select t2.product_attribute_id from product_attribute t2
        where t2.product_id in
        (
            select product_id from product p, manufacturer m
            where p.manufacturer_id = m.manufacturer_id
            and m.name = 'manufacturer name'
        )
        and pa.attribute_id = 1207
    )
)

The where clause also has to be done dynamically later on in the code.

Answer 1

Try to use Linqer. I remember writing some really convoluted things with it.

On a side note, your query isn't all that complex, you're just going from product to its attribute values. Just make a lot of joins on keys and you're done.

Answer 2

I like to compose Linq queries by writing the discrete components of the query as individual statements. Because each statement is a query rather than a result, Linq will then compose these all together to a single SQL query at run-time.

Writing the query this way, to me, makes it very easy to read, without sacrificing run-time database performance, since Linq makes it into one big query at run-time anyway. It will convert the Contains in the queries below into sub-selects.

Use LinqPad to see the generated SQL - it can be very interesting to see the SQL Linq creates.

Note result itself is a query. To materialize it, do result.ToList();

var productIds = from p in product
                 join m in manufacturer on p.manufacturer_id equals m.manufacturer_id
                 where m.name == 'manufacturer name'
                 select p.product_id;

var productAttributeIds =  from pa in product_attribute
                           where productIds.Contains(pa.product_id)
                           select pa.product_attribute_id;

var attributeValueIds = from pav in product_attribute_value
                        where productAttributeIds.Contains(pav.product_attribute_id)
                        select pav.attribute_value_id;

result = from av in attribute_value
         where attributeValueIds.Contains(av.atttriute_value_id)
         select av;