CODEWITHSUNDEEP
c#sqllinqlinq-to-entitieslinqpad
david.s
david.s

Reputation: 11403

How to rewrite a SQL query in LINQ to Entities?

I'm trying to rewrite a SQL query in LINQ to Entities. I'm using LINQPad with a typed datacontext from my own assembly to test things out.

The SQL query I'm trying to rewrite:

SELECT DISTINCT variantID AS setID, option_value AS name, option_value_description AS description, sort_order as sortOrder
FROM all_products_option_names AS lst
WHERE lst.optionID=14 AND lst.productID IN (SELECT productID FROM all_products_option_names
                                            WHERE optionID=7 AND option_value IN (SELECT name FROM brands
                                                                                  WHERE brandID=1))
ORDER BY sortOrder;

The LINQ to Entities query I've come up with so far (which doesn't work due to a timeout error):

from a in all_products_option_names
where a.optionID == 14 && all_products_option_names.Any(x => x.productID == a.productID && x.optionID == 7 && brands.Any(y => y.name == x.option_value && y.brandID == 1))
select new
{
    id = a.variantID,
    name = a.option_value,
    description = a.option_value_description,
    sortOrder = a.sort_order,
}

This is the error I get when I run the above query: An error occurred while executing the command definition. See the inner exception for details.

And the inner exception is: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Edit:

I use MySQL and probably that's why LINQPad doesn't show me the generated SQL.

The SQL version doesn't time out.

Edit 2:

I solved the problem by completely changing the query, so this question is irrelevant now.

I marked Steven's response as the correct one, because he was closest to what i was trying to achieve and his response gave me the idea which led me to the solution.

Upvotes: 1

Views: 562

Answers (3)

Aducci
Aducci

Reputation: 26644

I would try using joins with a distinct at the end like this:

var results =
    (from p in db.all_products_option_names
     join p2 in db.all_products_option_names on p.productId equals p2.productId
     join b in db.Brands on p2.option_value equals b.name
     where p.optionID == 14
     where p2.optionID == 7
     where b.BrandID == 1
     select new
     {
        setID = p.variantID, 
        name = p.option_value, 
        description = p.option_value_description, 
        sortOrder = p.sort_order
     }).Distinct();

Or you could try using joins with the into and with an any like so

 var results =
         from p in db.all_products_option_names
         join p2 in (from p3 in db.all_products_option_names.Where(x => x.optionId == 7) 
                     join b in db.Brands.Where(x => x.BrandID == 1) on p3.option_value equals b.name 
                     select p3) into pg
         where p.optionID == 14
         where pg.Any()
         select new
         {
            setID = p.variantID, 
            name = p.option_value, 
            description = p.option_value_description, 
            sortOrder = p.sort_order
         };

Upvotes: 0

Steven
Steven

Reputation: 172646

Try this:

var brandNames =
    from brand in db.Brands
    where brand.ID == 1
    select name;

var brandProductNames =
    from p in db.all_products_option_names
    where p.optionID == 7
    where brandNames.Contains(p.option_value)
    select p.productId;

var results =
    from p in db.all_products_option_names
    where p.optionID == 14
    where brandProductNames.Contains(p.productId)
    select new
    {
        setID = p.variantID, 
        name = p.option_value, 
        description = p.option_value_description, 
        sortOrder = p.sort_order
    };

Upvotes: 1

John
John

Reputation: 3546

I would recommend doing joins rather than sub-select's as you have them. Sub-selects are not very efficient when you look at performance, it's like having loops inside of loops when you code , not a good idea. This could actually cause that timeout your getting if your database is running slowly even thou that looks like a simple query.

Upvotes: 0

Related Questions