Home LINQ - query on query results (some complex one)
Reply: 1

LINQ - query on query results (some complex one)

Ilan Published in 2018-02-14 10:53:37Z

Need some help in writing LINQ from the following SQL. The main problem is double grouping. I'm stacked in the second grouping

group by s.[e-year], s.[e-month]

Don't know how to implement.

Thanks a lot.

select s.[e-year], s.[e-month], count(s.projectid) 'projects entranced',
          (select count(subquery.CustomerTypeID) from
              (select count(ap.ProjectID) as 'count', c.CustomerTypeID FROM Logging_ProjectsEntrances] pe
              inner join users u on pe.userid = u.userid
              inner join Companies c on u.CompanyId = c.CompanyID
              inner join AssignedProjects up on pe.ProjectID = up.ProjectID
              inner join Projects p on up.ProjectID = p.ProjectID
              where ap.ProductID = 1 and year(pe.EntranceDate) = s.[e-year] and MONTH(pe.entrancedate) = s.[e-month] and c.CustomerTypeID = 2
              group by ap.ProjectID, c.CustomerTypeID) subquery
          group by subquery.CustomerTypeID
      select YEAR(pe.EntranceDate) as 'e-year', MONTH(pe.EntranceDate) as 'e-month', up.ProjectID as 'projectid' 
      FROM Logging_ProjectsEntrances pe
      inner join AssignedProjects ap on pe.ProjectID = ap.ProjectID
      inner join Projects p on ap.ProjectID = p.ProjectID
      where ap.ProductID = 1
      group by year(pe.EntranceDate), month(pe.EntranceDate), ap.ProjectID
  ) as s
  group by s.[e-year], s.[e-month]
  order by s.[e-year] desc , s.[e-month] desc
NetMage Reply to 2018-02-14 18:48:46Z

For translating SQL to LINQ query comprehension:

  1. Translate FROM subselects as separately declared variables.
  2. Translate each clause in LINQ clause order, translating monadic operators (DISTINCT, TOP, etc) into functions applied to the whole LINQ query.
  3. Use table aliases as range variables. Use column aliases as anonymous type field names.
  4. Use anonymous types (new { ... }) for multiple columns.
  5. Left Join is simulated by using a into join_variable and doing another from from the join variable followed by .DefaultIfEmpty().
  6. Replace COALESCE with the conditional operator and a null test.
  7. Translate IN to .Contains() and NOT IN to !...Contains()
  8. SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
  9. SELECT fields must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
  10. Proper FULL OUTER JOIN must be handled with an extension method.

Note: Your SQL query is using a SQL trick (SELECT x ... GROUP BY x) to perform the equivalent of a DISTINCT, which should be used as it expresses the intent more clearly.

So, for your SQL query:

var subq = (from pe in projectsEntrances
            join ap in assignedProjects on pe.ProjectID equals ap.ProjectID
            join p in projects on ap.ProjectID equals p.ProjectID
            where ap.ProductID == 1
            select new { e_year = pe.EntranceDate.Year, e_month = pe.EntranceDate.Month, ap.ProjectID }).Distinct();

var ans = from s in subq
          group s by new { s.e_year, s.e_month } into sg
          orderby sg.Key.e_year descending, sg.Key.e_month descending
          select new { sg.Key.e_year, sg.Key.e_month, ProjectsEntranced = sg.Count() };
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 7.868016 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO