How to Translate a Complex SQL Query with Multiple Joins, Counts, and a Left Join into LINQ?

converted SQL query containing multiple connections, counting and left connections to linq

Your SQL query uses multiple connections, left connections, and polymerization functions to retrieve data from multiple tables. To convert this query to linq, follow the following steps:

Convert sub -query:

Extracted the query into a separate variable, unless they quote the columns other than the child query.
• Table notes: Use table names as range variables, and alias as anonymous type field names.
• Treatment connection: If you use EF/EF Core, the Join clause is converted into a navigation attribute. Otherwise, use anonymous objects or cross -connecting and where and left job simulation.
Plastic function:
• Use the linq aggregate function to replace the SQL aggregation function, such as count. distinct ():
Use the distinct () method to convert the distinct.
• LEFT JOIN: Use Into, from, and defaultifempty () to simulate left join.
• Anonymous type: Use anonymous types to combine multiple columns.
• Original SQL query:
• LINQ query expression conversion (improved version):

Lambda expression conversion:

This improved linq query handles the count function and left job more concise and efficiently, and avoid some potential problems in the original code, such as the strange judgment of

SELECT DISTINCT c.Id, 
       c.Title, 
       COUNT(v.Id) AS 'Nb_V2',
       COUNT(DISTINCT v.IdUser) AS 'Nb_V1',
       r.cnt AS 'Nb_R'
FROM TABLE_C c
JOIN TABLE_V v on c.Id = v.Id
LEFT JOIN ( 
    SELECT Id, COUNT(*)  AS cnt 
    FROM TABLE_R 
    GROUP BY Id
) r ON c.Id = r.Id
WHERE c.IdUser = '1234'
GROUP BY c.Id, c.Title, r.cnt

and the situation where

may be NULL. It more accurately reflects the logic of the original SQL query. Please note that you need to adjust according to your database context and physical name , and

.

var qResult = (from c in dbContext.TABLE_C
              join v in dbContext.TABLE_V on c.Id equals v.Id
              from r in dbContext.TABLE_R.Where(r => r.Id == c.Id).DefaultIfEmpty()
              where c.IdUser == "1234"
              group new { c, v, r } by new { c.Id, c.Title } into grouped
              select new
              {
                  IdC = grouped.Key.Id,
                  Title = grouped.Key.Title,
                  Nb_V2 = grouped.Count(g => g.v.Id != null),
                  Nb_V1 = grouped.Select(g => g.v.IdUser).Distinct().Count(),
                  Nb_R = grouped.Sum(g => (int?)g.r.cnt ?? 0) // 处理r.cnt可能为null的情况
              }).Distinct();

The above is the detailed content of How to Translate a Complex SQL Query with Multiple Joins, Counts, and a Left Join into LINQ?. For more information, please follow other related articles on the PHP Chinese website!