Entity Framework 4 difficult query with not mapped property

Question

I am a newbie in EF and I think that sometimes it's easier to sacrifice the advantage of ORM. But I want to know if there is a way to execute this query using EF4.

I have datawarehouse with fact table. Mapping looks like

    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; } 
    }

And query on 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

This sql query means (I want to get game results of a particular team versus other teams (number of games that the particular team is winner, and number of games where the particular team is looser) )

Maybe you could explain what your SQL request returns, i.e. what is its goal. — ken2k, Apr 12 '13 at 12:31
Why do you store aggregated results in each individual object and not in another specific results class that's not part of the EF model? — Gert Arnold, Apr 12 '13 at 13:20
@GertArnold I find two ways http://stackoverflow.com/questions/1987836/creating-a-non-mapped-property-in-an-entity-entity-framework and http://stackoverflow.com/questions/5155853/adding-additional-properties-to-an-entity-framework-4-code-first-ctp-5-entity. ANd decide why not save aggregate results as [Not Mapped] objects. Please, offer your solution — Ray, Apr 12 '13 at 13:26
What I'm trying to say is it doesn't make sense to store aggregated results in the objects that comprise the aggregate. You'll end up with e.g. `GameResult.WinCount`. `GameResult` has two Contenders, so who's count is it? And `GameResult` is about 1 game, what's the meaning of a count of one game? So what I mean is: the properties shouldn't be there in the first place, you need an aggregated result class. It's not a problem of how to handle unmapped properties. — Gert Arnold, Apr 12 '13 at 13:39

Cyril Gandon · Accepted Answer · 2013-04-12T12:41:40.123

_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()
           })

Not include the countGames, since it is just the sum of wins, draws and loses.

For the second, you can just join with this query which looks like the first:

_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()
       })

I don't understand what do you want me to do? – Cyril Gandon Apr 12 '13 at 13:44 — Cyril Gandon, Apr 12 '13 at 13:44
I don't understand result type of your query – Ray Apr 12 '13 at 14:02 — Ray, Apr 12 '13 at 14:02

Entity Framework 4 difficult query with not mapped property

1 Answers1