Complex SQL Subquery to LINQ

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
    whe开发者_JAVA百科re 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.

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.

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;

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.

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;