4

I have this SQL query which works:

select sum(dbos.Points) as Points, dboseasons.Year 
    from dbo.StatLines dbos
    inner join dbo.Games dbog on dbog.GameId = dbos.GameId
    inner join dbo.Seasons dboseasons on dbog.Season_SeasonId = dboseasons.SeasonId
    where dbos.PlayerId = 3
    group by dboseasons.Year

It returns Points, Year (56, 2016)

I'm trying to convert this to a Linq query to use with EF.

I have

var query =
            from dbostats in _db.StatLines
            join dbogames in _db.Games on dbostats.GameId equals dbogames.GameId
            join dboseasons in _db.Seasons on dbogames.Season.SeasonId equals dboseasons.SeasonId
            where dbostats.PlayerId == player.PlayerId
            group dbostats.Points by dboseasons.Year into g
            select new
            {
                Year = g.Key,
                Points = g.Sum()
            };
            
        playerAndStatLines.StatLinesBySeason =
            query
            .ToList()
            .Select( r => new StatsBySeason
                {
                    Season = r.Year,
                    Points = r.Points
                });
                

Which returns an empty result set.

When I view the SQL its generating, it's this:

SELECT 
[GroupBy1].[K1] AS [Year], 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    [Extent3].[Year] AS [K1], 
    SUM([Extent1].[Points]) AS [A1]
    FROM   [dbo].[StatLines] AS [Extent1]
    INNER JOIN [dbo].[Games] AS [Extent2] ON [Extent1].[GameId] = [Extent2].[GameId]
    INNER JOIN [dbo].[Seasons] AS [Extent3] ON [Extent2].[Season_SeasonId] = [Extent3].[SeasonId]
    WHERE ([Extent1].[Discriminator] IN (N'StatsBySeason',N'StatLines')) AND ([Extent1].[PlayerId] = 3)
    GROUP BY [Extent3].[Year]
)  AS [GroupBy1]

Which, as expected, returns an empty result set when executed against my DB.

The problem seems to be this bit:

([Extent1].[Discriminator] IN (N'StatsBySeason',N'StatLines')) AND 

If I take this out and run the generated query then I get back my 2016, 56 result.

What is this Extent1.Discriminator, why is it generating it from my Linq query?

My Model classes:

public class PlayerAndStatLines
    {
        public PlayerWithTeam PlayerWithTeam { get; set; }
        public IEnumerable<StatsBySeason> StatLinesBySeason { get; set; }
    }


public class Season
    {
        public int SeasonId { get; set; }
        public int Year { get; set; }
    }


public class Game
    {
        public int GameId { get; set; }
        public int HomeTeamId { get; set; }
        public int AwayTeamId { get; set; }
        public int HomeScore { get; set; }
        public int AwayScore { get; set; }
        public DateTime DatePlayed { get; set; }
        public GameType GameType { get; set; }
        public int? PlayoffGameNumber { get; set; }
        public Season Season { get; set; }
    }

public class StatLines
    {
        public int StatLinesId { get; set; }
        public int GameId { get; set; }
        public int PlayerId { get; set; }
        public int TeamId { get; set; }
        public int Points { get; set; }
        public int DefensiveRebounds { get; set; }
        public int OffensiveRebounds { get; set; }
        public int Assists { get; set; }
        public int Turnovers { get; set; }
        public int Minutes { get; set; }
        public int Steals { get; set; }
        public int Blocks { get; set; }
        public int Fouls { get; set; }
        public int ThreePointFieldGoalsAttempted { get; set; }
        public int ThreePointFieldGoalsMade { get; set; }
        public int TwoPointFieldGoalsAttempted { get; set; }
        public int TwoPointFieldGoalsMade { get; set; }
        public int FreeThrowsMade { get; set; }
        public int FreeThrowsAttempted { get; set; }
        public bool Started { get; set; }
    }


public class StatsBySeason : StatLines
    {
        public int Season { get; set; }

        public string SeasonYears => Season + " / " + (Season + 1);
    }

If I run the following SQL:

select Discriminator from dbo.StatLines

I get 2 rows back, both empty.

Thank you.

7
  • 3
    Discriminator is a column added by Entity Framework to persist the concrete type of the object when your models form an inheritance hierarchy. Can you add your model classes to the question? Commented Jan 9, 2017 at 13:41
  • Looks like your StatsBySeason class is inheriting StatLines entity, correct? Commented Jan 9, 2017 at 13:41
  • @Ivan Stoev - this is correct. I'll add my model classes to the OP Commented Jan 9, 2017 at 13:45
  • What is the value of Discriminator column for the rows that are being omitted by [Extent1].[Discriminator] IN (N'StatsBySeason',N'StatLines')? Commented Jan 9, 2017 at 13:49
  • @chris-pickford, how do I find that out? It's not a column I've added to the table. Commented Jan 9, 2017 at 13:52

1 Answer 1

3

Be very careful (avoid if possible) of inheriting an entity class inside the same assembly. EF discovers the derived class and decides that you want to use TPH inheritance strategy and silently (in case you are using automatic migrations) creates and uses Discriminator column, which of course is empty for the existing data and breaks your queries.

I see two options:

  • Replace inheritance with containment:

    public class StatsBySeason
    {
        public int Season { get; set; }
        public StatLines StatLines { get; set; }
    
        public string SeasonYears => Season + " / " + (Season + 1);
    }
    
  • Let EF ignore the StatsBySeason class using Fluent configuration:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Ignore<StatsBySeason>();
        // ...
    }
    

    or data annotation:

    [NotMapped]
    public class StatsBySeason : StatLines
    {
        // ...
    }
    
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you, I wasn't aware of TPH inheritance. Your solutions work, I'll go with the first one I think :)
Gotcha gotcha gotcha.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.