Need help converting SQL code to LINQ

Question

1.00/5 (1 vote)

See more:

Can someone please help me convert the following SQL to LINQ, I have tried all in vain to get a LINQ code that would work.

SELECT TOP 5 ProductName, COUNT(ProductName) AS CountOfProducts FROM OrderDetails 
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE OrderID IN (
    SELECT OrderID FROM OrderDetails WHERE ProductID = 1
) 
AND OrderDetails.ProductID <> 1 
GROUP BY ProductName ORDER BY CountOfProducts DESC

What I have tried:

I have tried installing LINQER but it doesn't install. Googling around won't give me another tool.... please help

Posted 9-Jan-17 23:23pm

Willie254

Updated 11-Jan-17 7:34am

Add a Solution

Comments

Wendelius 10-Jan-17 5:46am

What kind of LINQ query you're after? Are you using Entity Framework and how? Please post an example of what you have tried.

Andy Lanng 10-Jan-17 6:19am

have you tried linqpad? I hear good things but I have not tried it myself. At least, not it's current iteration.

Jon McKee 11-Jan-17 1:17am

Could you explain what the SQL command is trying to accomplish? I'm particularly interested in "WHERE OrderID IN (SELECT OrderID FROM OrderDetails WHERE ProductID = 1) AND OrderDetails.ProductID <> 1." Is this SQL attempting to find ProductName/OrderDetails combinations that don't have the proper data? The LINQ isn't too hard but all mock-ups I attempt return nothing because of this statement. You filter by OrderID's that have ProductID = 1, but then subsequently filter on ProductID != 1. This seems counter-intuitive unless errors are expected.

Willie254 11-Jan-17 3:51am

I am building an e-commerce site and the SQL (or rather the LINQ I need) is to implement the "Those who bought product A also bought B,C,D,E products". Hope this help you get the insight. I know it could be simple but am new to LINQ, please help.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jon McKee · Accepted Answer · 2017-01-11T07:34:00

C#

var query = (from order in db.OrderDetails
             where order.ProductID != 1
             join product in db.Products on order.ProductID equals product.ProductID
             group product by product.ProductName into result
             let count = result.Count()
             orderby count descending
             select new { ProductName = result.Key, CountOfProducts = count }
            ).Take(5);

Still not really sure what the OrderID IN (SELECT ....) section accomplishes but you can do that with something like this:

C#

db.OrderDetails.Any(o => o.ProductID == 1 && o.OrderID == external.OrderID)

I'll leave that up to you since I honestly can't get the query to do anything useful when including that check. Obviously I'm missing some relationship here.