针对2个复杂查询的C＃Linq UNION语法 [英] C# Linq UNION syntax on 2 complex queries
本文介绍了针对2个复杂查询的C＃Linq UNION语法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
现在，我想要采取我已经非常复杂的查询并创建一个类似查询的联合。我在下面有2个独立的 SQL查询，我使用UNION子句将其放入1个查询中。
第一个查询连接两个表。但第二个查询仅针对一个表，因为它适用于未链接到组织表中的组织的未分配资产。第一个查询将为每个组织返回一个单独的行。第二个查询将只返回所有未分配资产的一行。
整个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计数
FROM organization o，资产a
WHERE o.org_hq_id = a.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
UNION ALL
SELECT'Unassigned'，
'Unassigned'，
'Unassigned'，
'Unassigned'，
'Unassigned'，
a.hq_org_id，
a.command_org_id，
a.region_org_id，
a.installation_org_id，
a.site_org_id，
count（org_site_id）AS计数
FROM资产a
WHERE a.unassigned = 1
GROUP BY a.hq_org_id ，
a.command_org_id，
a.region_org_id，
a.installation_org_id，
a.site_org_id
我使用UNION子句将以下两个独立的 Linq查询放入1个Linq查询中。我相信下面的代码只需要一点点修改即可。我尝试过使用其他问题的例子，但我仍然无法弄清楚。
var join1 = from a context.asset
在context.organization中加入o
在新{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}
新的{a，o} org_hq_name，
o.org_command_name，
o.org_region_name，
o.org_installation_name，
o.org_site_name，
o.org_hq_id，
o.org_command_id，
o.org_region_id，
o.org_installation_id，
o.org_site_id
}进入gr1
选择新的
{
org_hq_name = gr1.Key.org_hq_name，
org_command_name = gr1.Key .org_command_name，
org_region_name = gr1.Key.org_region_name，
org_installation_name = gr1.Key.org_installation_name，
org_site_name = gr1.Key.org_site_name，
org_hq_id = gr1.Key.org_hq_id ，
org_command_id = gr1.Key.org_command_id，
org_region_id = gr1.Key.org_region_id，
org_installation_id = gr1.Key.org_installation_id，
org_site_id = gr1.Key.org_site_id，
Count = gr1.Count（）
};
UNION
var join2 =从一个in context.asset
其中（a.hq_org_id == 0）
组将新的{a.hq_org_id，
amanda_org_id，
a.region_org_id，
a.installation_org_id，
a.site_org_id
}转换为gr2
选择新
{
org_hq_name =未知，
org_command_name =未知，
org_region_name =未知，
org_installation_name =未知，
org_site_name =unknown，
org_hq_id = gr2.Key.org_hq_id，
org_command_id = gr2.Key.org_command_id，
org_region_id = gr2.Key.org_region_id，
org_installation_id = gr2.Key.org_installation_id，
org_site_id = gr2.Key.org_site_id，
Count = gr2.Count（）
};
预先感谢您的帮助。我相信这个答案对于那些正在努力处理像这样的复杂查询的人是有帮助的。
解决方案
snippetdata-lang =jsdata-hide =falsedata-console =truedata-babel =false>
class Pet {public string Name {get;组; } public int Age {get;组; }} //这个方法创建并返回一个Pet objects数组。静态Pet [] GetCats（）{Pet [] cats = {new Pet {Name =Barley，Age = 8}，new Pet {Name =Boots ，Age = 4}，新宠物{Name =Whiskers，Age = 1}};返回猫;} //这个方法创建并返回一个Pet objects数组。静态Pet [] GetDogs（）{Pet [] dogs = {new Pet {Name =Bounder，Age = 3}，new Pet {Name = 史努比，年龄= 14}，新宠物{名字=Fido，年龄= 9}}; public static void ConcatEx1（）{Pet [] cats = GetCats（）;宠物[]狗= GetDogs（）; //通过使用Concat（）将一系列猫的名字连接到//狗名的集合。 IEnumerable的<串GT; query = cats.AsQueryable（）.Select（cat => cat.Name）.Concat（dogs.Select（dog => dog.Name））; foreach（查询中的字符串名称）Console.WriteLine（name）;} //此代码产生以下输出：//// Barley // Boots // Whiskers // Bounder // Snoopy // Fido
This is pretty complicated for a person just learning Linq! I asked how to JOIN and GROUP BY in this question and a BIG SHOUT OUT to TriV for his explicit and very helpful answer.
Now, I want to take my already very complicated query and create a UNION to a similar query. I have below, 2 separate SQL queries that I have put into 1 query using a UNION clause.
The first query joins two tables. But the second query is against only one table, because it is for unassigned assets that are not linked to an organization in the organization table. The first query will return a separate row for each organization. The second query will return only one row for all unassigned assets.
The entire SQL query looks like this:
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 = a.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
UNION ALL
SELECT 'Unassigned',
'Unassigned',
'Unassigned',
'Unassigned',
'Unassigned',
a.hq_org_id,
a.command_org_id,
a.region_org_id,
a.installation_org_id,
a.site_org_id,
count(org_site_id) AS count
FROM asset a
WHERE a.unassigned = 1
GROUP BY a.hq_org_id,
a.command_org_id,
a.region_org_id,
a.installation_org_id,
a.site_org_id
I have below, 2 separate Linq queries that I want to put into 1 Linq query using a UNION clause. I believe the code below only needs a little tweeking to get it right. I've tried using examples from other questions but I still cannot figure this out.
var join1 = from a 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 {a,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_hq_id,
o.org_command_id,
o.org_region_id,
o.org_installation_id,
o.org_site_id
} into gr1
select new
{
org_hq_name = gr1.Key.org_hq_name,
org_command_name = gr1.Key.org_command_name,
org_region_name = gr1.Key.org_region_name,
org_installation_name = gr1.Key.org_installation_name,
org_site_name = gr1.Key.org_site_name,
org_hq_id = gr1.Key.org_hq_id,
org_command_id = gr1.Key.org_command_id,
org_region_id = gr1.Key.org_region_id,
org_installation_id = gr1.Key.org_installation_id,
org_site_id = gr1.Key.org_site_id,
Count = gr1.Count()
};
UNION
var join2 = from a in context.asset
where (a.hq_org_id == 0)
group m by new { a.hq_org_id,
a.command_org_id,
a.region_org_id,
a.installation_org_id,
a.site_org_id
} into gr2
select new
{
org_hq_name = "unknown",
org_command_name = "unknown",
org_region_name = "unknown",
org_installation_name = "unknown",
org_site_name = "unknown",
org_hq_id = gr2.Key.org_hq_id,
org_command_id = gr2.Key.org_command_id,
org_region_id = gr2.Key.org_region_id,
org_installation_id = gr2.Key.org_installation_id,
org_site_id = gr2.Key.org_site_id,
Count = gr2.Count()
};
Thank you in advance for Helping. I am sure the answer will be helpful to others who are struggling with complex queries like this one.
解决方案
class Pet
{
public string Name { get; set; }
public int Age { get; set; }
}
// This method creates and returns an array of Pet objects.
static Pet[] GetCats()
{
Pet[] cats = { new Pet { Name="Barley", Age=8 },
new Pet { Name="Boots", Age=4 },
new Pet { Name="Whiskers", Age=1 } };
return cats;
}
// This method creates and returns an array of Pet objects.
static Pet[] GetDogs()
{
Pet[] dogs = { new Pet { Name="Bounder", Age=3 },
new Pet { Name="Snoopy", Age=14 },
new Pet { Name="Fido", Age=9 } };
return dogs;
}
public static void ConcatEx1()
{
Pet[] cats = GetCats();
Pet[] dogs = GetDogs();
// Concatenate a collection of cat names to a
// collection of dog names by using Concat().
IEnumerable<string> query =
cats.AsQueryable()
.Select(cat => cat.Name)
.Concat(dogs.Select(dog => dog.Name));
foreach (string name in query)
Console.WriteLine(name);
}
// This code produces the following output:
//
// Barley
// Boots
// Whiskers
// Bounder
// Snoopy
// Fido
这篇关于针对2个复杂查询的C＃Linq UNION语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
