protected void Page_Load(object sender, EventArgs e)
{
// Beautiful example
COR_Basic_DemoEntities Repo = new COR_Basic_DemoEntities(ExcelDataFeed.GetEntityConnectionString());
string strSQL = @"
SELECT
BE_Vorname
,BE_Name
,BE_User
,Name
,COUNT(Name) AS qty
,SUM(1) AS summe
FROM T_Benutzer
LEFT JOIN T_Benutzer_Benutzergruppen
ON T_Benutzer_Benutzergruppen.BEBG_BE = BE_ID
LEFT JOIN T_Benutzergruppen
ON ID = BEBG_BG
WHERE BE_ID <> 1234
GROUP BY
BE_Vorname
,BE_Name
,BE_User
,Name
ORDER BY Name, BE_User, qty, summe
";
int __UserId = 1234;
/*
* var ls = (
* from QueryToGroup in
* (
* from users in Repo.T_Benutzer
* from mappings in Repo.T_Benutzer_Benutzergruppen
* .Where(mapping => mapping.BEBG_BE == users.BE_ID)
* .DefaultIfEmpty() // <== makes join left join
* from groups in Repo.T_Benutzergruppen
* .Where(gruppe => gruppe.ID == mappings.BEBG_BG)
* .DefaultIfEmpty() // <== makes join left join
* where users.BE_ID != __UserId
*
* select new
* {
* Vorname = users.BE_Vorname
* ,Name = users.BE_Name
* ,UserName = users.BE_User
* ,GroupName = groups.Name
* //,abc = 1
* }
* )
* group QueryToGroup by new { QueryToGroup.Vorname, QueryToGroup.Name, QueryToGroup.UserName, QueryToGroup.GroupName } into g
* select new
* {
* Vorname = g.Key.Vorname
* ,Name = g.Key.Name
* ,Nutzer = g.Key.UserName
* ,Gruppe = g.Key.GroupName
* //,qty = g.Count( x => x.GroupName != null)
* ,qty = g.Select(x=> x.GroupName).Count()
* //,Count = g.Select(x => x.GroupName).Distinct().Count()
*
* // ,summe = g.Sum(x => x.abc)
* }
*
* ).ToList();
*/
var lol = (
from users in Repo.T_Benutzer
from mappings in Repo.T_Benutzer_Benutzergruppen
.Where(mapping => mapping.BEBG_BE == users.BE_ID)
.DefaultIfEmpty() // <== makes join left join
from groups in Repo.T_Benutzergruppen
.Where(gruppe => gruppe.ID == mappings.BEBG_BG)
.DefaultIfEmpty() // <== makes join left join
where users.BE_ID != __UserId
select new
{
Vorname = users.BE_Vorname
, Name = users.BE_Name
, UserName = users.BE_User
, GroupName = groups.Name
//,value = 1
}
into QueryToGroup
group QueryToGroup by new { QueryToGroup.Vorname, QueryToGroup.Name, QueryToGroup.UserName, QueryToGroup.GroupName } into g
// orderby g.Key.GroupName, g.Key.UserName, g.Count(x => x.GroupName != null)
select new
{
Vorname = g.Key.Vorname
, Name = g.Key.Name
, Nutzer = g.Key.UserName
, Gruppe = g.Key.GroupName
//,qty = g.Count(x => x.GroupName != null)
//,qty = g.Select(x => x.GroupName).Where(x => x != null).Count()
, qty = g.Select(x => x.GroupName).Where(x => x != null).Distinct().Count()
//, a = g.Select(x => x.value).Distinct().Count()
//,a = g.Select(x => x.GroupName != null).Count()
//,qty = g.Select(x => x.GroupName != null).Count()
//,Count = g.Select(x => x.GroupName).Distinct().Count()
// ,summe = g.Sum(x => x.abc)
}
)
// .OrderBy(t => t.Gruppe).ThenBy(t => t.Nutzer).ThenBy(t => t.qty) // Order in SQL
.ToList()
.OrderBy(t => t.Gruppe).ThenBy(t => t.Nutzer).ThenBy(t => t.qty) // Order in .NET
;
System.Console.WriteLine(strSQL);
// System.Console.WriteLine(ls);
System.Console.WriteLine(lol);
}
protected void Page_Load(object sender, EventArgs e)
{
// Beautiful example
COR_Basic_DemoEntities Repo = new COR_Basic_DemoEntities(ExcelDataFeed.GetEntityConnectionString());
string strSQL = @"
SELECT
BE_Vorname
,BE_Name
,BE_User
,Name
,COUNT(Name) AS qty
,SUM(1) AS summe
FROM T_Benutzer
LEFT JOIN T_Benutzer_Benutzergruppen
ON T_Benutzer_Benutzergruppen.BEBG_BE = BE_ID
LEFT JOIN T_Benutzergruppen
ON ID = BEBG_BG
WHERE BE_ID <> 1234
GROUP BY
BE_Vorname
,BE_Name
,BE_User
,Name
ORDER BY Name, BE_User, qty, summe
";
int __UserId = 1234;
/*
var ls = (
from QueryToGroup in
(
from users in Repo.T_Benutzer
from mappings in Repo.T_Benutzer_Benutzergruppen
.Where(mapping => mapping.BEBG_BE == users.BE_ID)
.DefaultIfEmpty() // <== makes join left join
from groups in Repo.T_Benutzergruppen
.Where(gruppe => gruppe.ID == mappings.BEBG_BG)
.DefaultIfEmpty() // <== makes join left join
where users.BE_ID != __UserId
select new
{
Vorname = users.BE_Vorname
,Name = users.BE_Name
,UserName = users.BE_User
,GroupName = groups.Name
//,abc = 1
}
)
group QueryToGroup by new { QueryToGroup.Vorname, QueryToGroup.Name, QueryToGroup.UserName, QueryToGroup.GroupName } into g
select new
{
Vorname = g.Key.Vorname
,Name = g.Key.Name
,Nutzer = g.Key.UserName
,Gruppe = g.Key.GroupName
//,qty = g.Count( x => x.GroupName != null)
,qty = g.Select(x=> x.GroupName).Count()
//,Count = g.Select(x => x.GroupName).Distinct().Count()
// ,summe = g.Sum(x => x.abc)
}
).ToList();
*/
var lol = (
from users in Repo.T_Benutzer
from mappings in Repo.T_Benutzer_Benutzergruppen
.Where(mapping => mapping.BEBG_BE == users.BE_ID)
.DefaultIfEmpty() // <== makes join left join
from groups in Repo.T_Benutzergruppen
.Where(gruppe => gruppe.ID == mappings.BEBG_BG)
.DefaultIfEmpty() // <== makes join left join
where users.BE_ID != __UserId
select new
{
Vorname = users.BE_Vorname
,Name = users.BE_Name
,UserName = users.BE_User
,GroupName = groups.Name
//,value = 1
}
into QueryToGroup
group QueryToGroup by new { QueryToGroup.Vorname, QueryToGroup.Name, QueryToGroup.UserName, QueryToGroup.GroupName } into g
// orderby g.Key.GroupName, g.Key.UserName, g.Count(x => x.GroupName != null)
select new
{
Vorname = g.Key.Vorname
,Name = g.Key.Name
,Nutzer = g.Key.UserName
,Gruppe = g.Key.GroupName
//,qty = g.Count(x => x.GroupName != null)
//,qty = g.Select(x => x.GroupName).Where(x => x != null).Count()
,qty = g.Select(x => x.GroupName).Where(x => x != null).Distinct().Count()
//, a = g.Select(x => x.value).Distinct().Count()
//,a = g.Select(x => x.GroupName != null).Count()
//,qty = g.Select(x => x.GroupName != null).Count()
//,Count = g.Select(x => x.GroupName).Distinct().Count()
// ,summe = g.Sum(x => x.abc)
}
)
// .OrderBy(t => t.Gruppe).ThenBy(t => t.Nutzer).ThenBy(t => t.qty) // Order in SQL
.ToList()
.OrderBy(t => t.Gruppe).ThenBy(t => t.Nutzer).ThenBy(t => t.qty) // Order in .NET
;
System.Console.WriteLine(strSQL);
// System.Console.WriteLine(ls);
System.Console.WriteLine(lol);
}