C# Linq 在多列上连接 2 个表并使用 GROUP BY 进行计数 [英] C# Linq Join 2 tables on multiple columns and GROUP BY for count

问题描述

我需要在五列上加入两个实体，我需要对 SELECT 中的每一列进行 GROUP BY，并在每个 GROUP BY 上获得一个 COUNT.虽然这对我来说是 SQL 的蛋糕，但我对 LINQ 所能找到的每一个例子都感到非常困惑.

I need to JOIN two entities on five columns and I need to GROUP BY every column in the SELECT plus get a COUNT on each GROUP BY. While this is cake to me in SQL, I am hopelessly confused with every example I can find for LINQ.

我发现了以下两个问题(C# Linq Group By on multiple columns [duplicate] 和 按多列分组)与我的相似，但我仍然对如何执行此操作感到困惑.这是我的 SQL 语句:

I've found the following two questions (C# Linq Group By on multiple columns [duplicate] and Group By Multiple Columns) that are similar to mine but I am still confused about how to do this. Here is my SQL statement:

SELECT  o.org_hq_name,
        o.org_command_name,
        o.org_region_name,
        o.org_installation_name,
        o.org_site_name,
        o.org_subsite_name,
        o.org_hq_id,
        o.org_command_id,
        o.org_region_id,
        o.org_installation_id,
        o.org_site_id,
        count(org_site_id) AS count

FROM    organization o, asset a

WHERE      o.org_hq_id = hq_org_id
AND        o.org_command_id = a.command_org_id
AND        o.org_region_id = a.region_org_id
AND        o.org_installation_id = a.installation_org_id
AND        o.org_site_id = a.site_org_id

GROUP BY o.org_hq_name,
        o.org_command_name,
        o.org_region_name,
        o.org_installation_name,
        o.org_site_name,
        o.org_subsite_name,
        o.org_hq_id,
        o.org_command_id,
        o.org_region_id,
        o.org_installation_id,
        o.org_site_id

我在下面有一个连接:

var join1 =  from m in context.asset
             join o in context.organization
             on new {hqID = a.hq_org_id, commandID = a.command_org_id, regionID = a.region_org_id, installationID = a.installation_org_id, siteID = a.site_org_id}
             equals new {hqID = o.hq_id, commandID = o.command_id, regionID = o.region_id, installationID = o.installation_id, siteID = o.site_id}
             select new
             {
                 hqID = o.hq_id,
                 commandID = o.command_id,
                 regionID = o.region_id,
                 installationID = o.installation_id,
                 siteID = o.site_id
                 //hqId = o.count(org_site_id) AS count
             };

我在下面有一个分组依据:

I have a Group By below:

var group1 = from a in context.asset
            group a by new
            {
                a.hq_org_id,
                a.command_org_id,
                a.region_org_id,
                a.installation_org_id,
                a.site_org_id

                // I am not sure how to get the count

            } into asset
            select new
            {
                // I cannot figure out how to join
            }

请帮助我在两个表上使用 JOIN GROUP BY 的语法，但在两个表的 MULTIPLE 列上并获取每个组的计数.

Please help me with the syntax to GROUP BY with a JOIN on two tables, but on MULTIPLE columns of the two tables AND get the count for each group by.

推荐答案

你的查询可以像这样在 Linq 中重写

Your query could be rewritten in Linq like this

var join1 =  from m in context.asset
         join o in context.organization
         on new {hqID = a.hq_org_id, commandID = a.command_org_id, regionID = a.region_org_id, installationID = a.installation_org_id, siteID = a.site_org_id}
         equals new {hqID = o.hq_id, commandID = o.command_id, regionID = o.region_id, installationID = o.installation_id, siteID = o.site_id}
         group new {m,o} by new {   o.org_hq_name,
                                    o.org_command_name,
                                    o.org_region_name,
                                    o.org_installation_name,
                                    o.org_site_name,
                                    o.org_subsite_name,
                                    o.org_hq_id,
                                    o.org_command_id,
                                    o.org_region_id,
                                    o.org_installation_id,
                                    o.org_site_id
                                } into gr
         select new
         {
            org_hq_name = gr.Key.org_hq_name,
            org_command_name = gr.Key.org_command_name,
            org_region_name = gr.Key.org_region_name,
            org_installation_name = gr.Key.org_installation_name,
            org_site_name = gr.Key.org_site_name,
            org_subsite_name = gr.Key.org_subsite_name,
            org_hq_id = gr.Key.org_hq_id,
            org_command_id = gr.Key.org_command_id,
            org_region_id = gr.Key.org_region_id,
            org_installation_id = gr.Key.org_installation_id,
            org_site_id = gr.Key.org_site_id,
            Count = gr.Count()
         };

这篇关于C# Linq 在多列上连接 2 个表并使用 GROUP BY 进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！