How can I convert a SQL query to a Linq expression for a Join on with various conditions in EF Core-CodePudding

This is my SQL query I'm trying to convert:

SELECT TOP (10) 
    t1.ProductName, t2.ProductName, 
    COUNT(DISTINCT t1.OrderId) AS num_orders
FROM 
    Reports t1 
JOIN
    Reports t2 ON t1.OrderId = t2.OrderId
               AND t1.ProductId < t2.ProductId
GROUP BY
    t1.ProductName, t2.ProductName
ORDER BY 
    num_orders DESC

As you can see, in the "on", the orderId must be the same and also, the productId of one must be less than the other.

This is what I have achieved so far (very incomplete):

var reportData = await (from t1 in this.Context.Reports 
                        join t2 in this.Context.Reports 
                             on t1.OrderIdequals t2.OrderId
                        where t1.ProductId < t2.ProductId
                        into GroupedData
                        orderby GroupedData.Key
                        select new 
                        {
                           GroupedData
                        }).ToListAsync();

I get an error if I put an expression with an "and" in the "on" and I have tried to do it in a separate "where", but it still doesn't work.

Also the select is incomplete because I have not yet managed to get all the above code to work, so don't give it any importance.

The closest I've come to getting it to work for me I got the same error this person gets: How can I use Linq expression for Join with GroupBy in EF Core 3.1

This is the page I have used to search for information, but it does not show what I am looking for: https://learn.microsoft.com/en-us/ef/core/querying/complex-query-operators

I have also used Linqer and this repository of SQL to Linq, but I can't get them to work, I'm a junior :(

Could someone help me or recommend me where to look for information?

CodePudding user response：

try this,

var result = (from t1 in Reports
             join t2 in Reports on t1.OrderId equals t2.OrderId
             where t1.ProductId < t2.ProductId
             group new { t1, t2 } by new { t1.ProductName, t2.ProductName } into g
             let numOrders = g.Select(x => x.t1.OrderId).Distinct().Count()
             orderby numOrders descending
             select new
             {
                 ProductName1 = g.Key.ProductName,
                 ProductName2 = g.Key.ProductName2,
                 NumOrders = numOrders
             }).Take(10);

CodePudding user response：

Try this

var reportData = (from t1 in this.Context.Reports 
                        join t2 in this.Context.Reports on t1.OrderId equals t2.OrderId
                        select new {t1 = t1, t2 = t2}
                 ).Where(x => t1.ProductId < t2.ProductId)
                  .GroupBy(x => new { t1Id = x.t1.ProductId, t1Id = x.t2.ProductId})
                  .Select(x => new {t1ProductName = x.t1.First().ProductName, x.t2.First().ProductName, num_Orders = x.t1.Select(y => y.OrderId).Disinct().Count()})
                  .OrderByDescending(x => x.num_Orders);