C＃Linq将多个列加2个表，将GROUP BY加入count [英] C# Linq Join 2 tables on multiple columns and GROUP BY for count
问题描述
我需要在五列上加入两个实体，并且我需要在GROUP BY中的每列添加GROUP BY，并在每个GROUP BY上获得一个COUNT。虽然这对我来说是一个蛋糕，但我无法理解我能找到的每一个LINQ的例子。
我发现了以下两个问题（C＃Linq Group By在多列[重复] 和 Group By Multiple Columns ）与我的类似，但我仍然对如何做到这一点感到困惑。这是我的SQL语句：
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计数
从组织o，资产a
其中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 =从context.asset中的m
在context.organization中加入o
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}
等于new {hqID = o.hq_id，commandID = o .command_id，regionID = o.region_id，installationID = o.installation_id，siteID = o.site_id}
选择新的
{
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计数
};
我有以下分组：
var group1 =从一个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
//我不知道如何将计数
}资产
选择新的
{
//我不知道如何加入
}
请帮助我在GROUP BY中使用两个表上的JOIN，但是在两个表的MULTIPLE列上获取每个组的计数。
var join1 =来自context.asset中的m
在context.organization中加入o $ b $ 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}
等于new {hqID = o.hq_id，commandID = o.command_id， regionID = o.region_id，installationID = o.installation_id，siteID = o.site_id}
新的{o.org_hq_name，
o.org_command_name，
o组成的新的{m，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
}转换成gr
选择新的
{
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（）
};
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.
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
I have a join below:
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
}
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.
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加入count的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
