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 have successfully implemented 'in' queries by using the Contains() method. For example:

int[] ids = new int[] { 1, 4 };

databasecontext.SomeTable.Where(s => ids.Contains(s.id));

The above will return all records from SomeTable where id is 1 or 4.

I believe you can chain the Contains() methods together. I know it seems backwards, but start with the innermost subselect and work your way out from there.

Answer 3

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;

Answer 4

Depends on the model, but you should be able to do it similar to:

var attributes =
    from t0 in db.AttributeValues
    where t0.ProductAttributeValues.Any( t1=> 
        t1.ProductAttribute.AttributeId == 1207 &&
        t1.ProductAttribute.Product.Manufacturers
             .Any(m=> m.name == "manufacturer name")
    )
    select t0;

---

An alternative, reasonably similar to the query / just translation approach:

var attributes =
    from t0 in db.AttributeValues
    where db.Product_Attribute_Values.Any(t1 => 
        db.Product_Attributes.Any(t2 =>
            t2.product_attribute_id == t1.product_attribute_id &&
            db.Products.Any(p=> 
                 p.product_id == t2.product_id &&
                 db.Manufacturers.Any(m=> 
                      m.manufacturer_id == p.manufacturer_id && 
                      m.name == "manufacturer name"
                 )
            ) &&
            t2.attribute_id = 1207
        ) &&
        t0.attribute_value_id == t1.attribute_value_id
     )
     select t0;