sql到linq扩展方法(带聚合函数)

yyhrrdl8 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(367)

首先我要感谢那些花时间帮助我的人。在这个例子中，假设我的数据库中有以下表格。

------------
tPerson
____________
ID | AGE | countryID

------------
tPet
____________
ID | personID

-----------
tCountry
___________
ID | Name

我想知道如何使用linq编写下一个查询（但是使用扩展方法）

SELECT tCountry.ID, tCountryName,
    SUM(CASE WHEN(tPeople.Age <20 && subQ.hasPets = 0) THEN 1 ELSE 0) AS first,
    SUM(CASE WHEN(tPeople IN (20,21,22,23,24,25) && subQ.hasPets > 0) THEN 1 ELSE 0) AS second,
    SUM(CASE WHEN(tPeople.Age > 25) THEN 1 ELSE 0) AS third
FROM tPeople
LEFT JOIN (
    SELECT tPeople.ID, COUNT(tPet.ID) AS hasPets
    FROM tPeople INNER JOIN tPet ON tPet.PersonID = tPeople.ID
    GROUPBY tPeopleID
) AS subQ
ON subQ.ID = tPeople.ID
LEFT JOIN tCountry ON tPeople.countryID = tCountry.ID WHERE tCountry.ID = XX
GROUP BY (tCountry.ID, tCountryName)

提前谢谢！

sql Aggregate linq

来源：https://stackoverflow.com/questions/62964022/sql-to-linq-extensions-methods-with-aggregate-function

1条答案

按热度按时间

nhjlsmyf1#

var subQuery = from tPeople in People
           join tPet in Pet on tPeople.Id equals tPet.PersonId
           group tPeople by tPeople.Id into g
           select new { Id = g.Key, hasPets = g.Count() };

var mainQuery = from tPeopel in People
                join _subQ in subQuery on tPeopel.Id equals _subQ.Id into _subQLeft
                from subQ in _subQLeft.DefaultIfEmpty()
                join _tCuntry in Country on tPeopel.CountryId equals _tCuntry.Id into _tCountryLeft
                from tCountry in _tCountryLeft.DefaultIfEmpty()     
                select new
                {
                    Id = tPeopel.Id,
                    Age = tPeopel.Age,
                    HasPets= subQ.hasPets,
                    CountryId = tCountry.Id,
                    CountryName = tCountry.Name
                };

var Finalquery = from mainQ in mainQuery
            where mainQ.CountryId == 55
            group mainQ by new { mainQ.CountryId, mainQ.CountryName } into g
            select new
            {
                CountryId = g.Key.CountryId,
                CountyName = g.Key.CountryName,
                First = g.Sum(t => (t.Age < 20 && t.HasPets == 1) ? 1 : 0),
                Second = g.Sum(t => (new int[] { 20, 21, 23, 24, 25 }.Contains(t.Id) && t.HasPets > 0) ? 1 : 0),
                Third = g.Sum(t => t.Age > 25 ? 1 : 0)
            };

var result = await Finalquery.ToListAsync();

为了便于阅读和理解，我把它们分开了
但如果你问我，我建议你用 Raw SQL Query 或者 Dapper 在这种情况下
因为在我看来，它简单、更优化、更快
而且，据我所知，当linq查询（to sql）很复杂时，它不会生成最佳的sql查询
如果你想用 Window Functions() 以及 Sql.Ext. ，我觉得这种情况下它不是很有用，但是如果你想，首先请改变你的想法 SQL Query 使用 window function() 在里面

赞(0）回复(0）举报 2021-07-29

我来回答

sql到linq扩展方法(带聚合函数)

1条答案

相关问题

热门标签

最新问答