实体框架4没有映射属性的困难查询
本文关键字:查询 属性 映射 框架 实体 | 更新日期: 2023-09-27 18:08:45
我是EF
的新手,我认为有时牺牲ORM的优势更容易。但是我想知道是否有一种方法可以使用EF4
来执行这个查询。
我有数据仓库与事实表。映射看起来像
public class GameResult
{
[Key]
public int GameResultId { get; set; }
public virtual Competition Competition { get; set; }
public virtual CustomDate DateGame { get; set; }
public virtual Contender ContenderFirst { get; set; }
public virtual Contender ContenderSecond { get; set; }
public virtual Location Location { get; set; }
public int ScoreContenderFirst { get; set; }
public int ScoreContenderSecond { get; set; }
public int PrizeStock { get; set; }
public int Budget { get; set; }
[NotMapped]
public int WinCount { get; set; }
[NotMapped]
public int FailCount { get; set; }
[NotMapped]
public int DeadHeatCount { get; set; }
[NotMapped]
public int CountGames { get; set; }
}
和查询sql
select
Contenders.Name,
sum((Case
when (ScoreContenderFirst > ScoreContenderSecond) then 1
else 0
end)) as wins,
sum ((Case
when (ScoreContenderFirst = ScoreContenderSecond) then 1
else 0
end)) as equals,
sum((Case
when (ScoreContenderFirst < ScoreContenderSecond) then 1
else 0
end)) as fail,
COUNT(GameResults.GameResultId)as countGames
from GameResults
inner join Contenders
on GameResults.ContenderSecond_ContenderId = Contenders.ContenderId
where GameResults.ContenderFirst_ContenderId = 42
group by Contenders.Name
UNION
select
Contenders.Name,
sum((Case
when (ScoreContenderFirst < ScoreContenderSecond) then 1
else 0
end)) as wins,
sum ((Case
when (ScoreContenderFirst = ScoreContenderSecond) then 1
else 0
end)) as equals,
sum((Case
when (ScoreContenderFirst > ScoreContenderSecond) then 1
else 0
end)) as fail,
COUNT(GameResults.GameResultId)as countGames
from GameResults
inner join Contenders
on GameResults.ContenderFirst_ContenderId = Contenders.ContenderId
where GameResults.ContenderSecond_ContenderId = 42
group by Contenders.Name
这个sql
查询意味着(我想获得特定球队与其他球队的比赛结果(特定球队获胜的比赛次数,以及特定球队较弱的比赛次数))
_efContext.GameResult
.Where(game => game.ContenderFirst_ContenderId == 42)
.Select(game => new {
ContendersName = game.ContenderFirst.Name
, Win = game.ScoreContenderFirst > game.ScoreContenderSecond
, Draw = game.ScoreContenderFirst == game.ScoreContenderSecond
, Lose = game.ScoreContenderFirst < game.ScoreContenderSecond
})
.GroupBy(game => game.ContendersName)
.Select(grp => new {
ContendersName= grp.Key
, Wins = grp.Where(game => game.Win).Count()
, Draws = grp.Where(game => game.Draw).Count()
, Loses = grp.Where(game => game.Lose).Count()
})
不包括countGames,因为它只是赢、平、输的总和。
对于第二个查询,您可以直接连接与第一个查询类似的查询:
_efContext.GameResult
.Where(game => game.ContenderSecond_ContenderId == 42)
.Select(game => new {
ContendersName = game.ContenderSecond.Name
, Win = game.ScoreContenderFirst < game.ScoreContenderSecond
, Draw = game.ScoreContenderFirst == game.ScoreContenderSecond
, Lose = game.ScoreContenderFirst > game.ScoreContenderSecond
})
.GroupBy(game => game.ContendersName)
.Select(grp => new {
ContendersName= grp.Key
, Wins = grp.Where(game => game.Win).Count()
, Draws = grp.Where(game => game.Draw).Count()
, Loses = grp.Where(game => game.Lose).Count()
})