0

I am trying to put a count inside select after a GroupJoin. You can find an example of what I am trying to do here. Basically I am trying to count how many times a player was used in home matches.

var playerMatches = players
            .GroupJoin(matches, p => p.Id, m => m.PlayerId, (p, m) => new {player = p, matches = m})
            .Select(x => new {
                PlayerId = x.player.Id,
                PlayerName = x.player.Name,
                Home = x.matches.Where(m => m.Home.StartsWith("Y")).Count()
            });

Given the following data

var players = new List<Player>{
    new Player{Id = 1, Name = "Player1"},
    new Player{Id = 2, Name = "Player2"},
    new Player{Id = 3, Name = "Player3"},
    new Player{Id = 4, Name = "Player4"},
    new Player{Id = 8, Name = "Player8"},
    new Player{Id = 11, Name = "Player11"}  
};

var matches = new List<Match>{
    new Match{Id = 10, Name = "Match10", PlayerId = 1, Home = "Y"},
    new Match{Id = 11, Name = "Match11", PlayerId = 1, Home = "Y"},
    new Match{Id = 12, Name = "Match12", PlayerId = 1, Home = "N"},
    new Match{Id = 13, Name = "Match13", PlayerId = 1, Home = "N"},
    new Match{Id = 14, Name = "Match14", PlayerId = 2, Home = "N"},
    new Match{Id = 15, Name = "Match15", PlayerId = 3, Home = "N"},
    new Match{Id = 16, Name = "Match16", PlayerId = 4, Home = "Y"},
    new Match{Id = 17, Name = "Match17", PlayerId = 4, Home = "N"},
    new Match{Id = 19, Name = "Match18", PlayerId = 11, Home = "Y"},
    new Match{Id = 18, Name = "Match19", PlayerId = 11, Home = "N"},
    new Match{Id = 20, Name = "Match20", PlayerId = 11, Home = "N"},
    new Match{Id = 21, Name = "Match21", PlayerId = 1, Home = "N"},
    new Match{Id = 22, Name = "Match22", PlayerId = 1, Home = "Y"},
    new Match{Id = 23, Name = "Match23", PlayerId = 8, Home = "N"},
    new Match{Id = 24, Name = "Match24", PlayerId = 1, Home = "Y"},
};

I am expecting the output to be something like the following

{ PlayerId = 1, PlayerName = Player1, Home = 4 }
{ PlayerId = 2, PlayerName = Player2, Home = 0 }
{ PlayerId = 3, PlayerName = Player3, Home = 0 }
{ PlayerId = 4, PlayerName = Player4, Home = 1 }
{ PlayerId = 8, PlayerName = Player8, Home = 0 }
{ PlayerId = 11, PlayerName = Player11, Home = 1 }

This works fine with list but not with actual database objects. I think it has to do with the Where.Count but whatever I try I can't manage to get rid of the error(actually I can if I remove the Where.Count clause completely).

Here is the error I am getting.

Unknown column 'Project6.id' in 'where clause' Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: MySql.Data.MySqlClient.MySqlException: Unknown column 'Project6.id' in 'where clause'

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

I am using EF6(6.1.3) and MySQL 5.7 (6.9.9).

kechap
  • 2,077
  • 6
  • 28
  • 50
  • 1
    Possible duplicate of [SQL to Entity Framework Count Group-By](http://stackoverflow.com/questions/11564311/sql-to-entity-framework-count-group-by) – Juan Carlos Oropeza Dec 18 '16 at 17:44
  • @JuanCarlosOropeza You seriously marked this as duplicate? Did you read the question or something? – kechap Dec 18 '16 at 17:46
  • Yes, I read. And see your sintaxis is wrong – Juan Carlos Oropeza Dec 18 '16 at 17:47
  • Maybe you want Include some sample data and expected output if you belive your query is different . Or show us your SQL query and we can give you the EF equivalent. Also dont think ppl here are against you, maybe your question wasnt that clear as you thought. – Juan Carlos Oropeza Dec 18 '16 at 17:48
  • What EF version and MySQL connector version are you using? – Ivan Stoev Dec 18 '16 at 17:53
  • @JuanCarlosOropeza I include an expected output in the .net fiddle link. That's what I expect to get. It doesn't work with actual database objects as I describe. I can't understand how this is related with the duplicate question you included. – kechap Dec 18 '16 at 17:56
  • @IvanStoev I am using MySQL 5.7 and EF6. – kechap Dec 18 '16 at 17:56
  • Well that is your first mistake, You should include everything in your question. External sources are welcome, but your question should be able to be complete even if external source is down. Otherwise we have lot of dead question. You also should show us what is your current output and your desire output – Juan Carlos Oropeza Dec 18 '16 at 17:57
  • @JuanCarlosOropeza Ok you got me. But marking something you didn't read or you didn't understand is no bueno. – kechap Dec 18 '16 at 18:00
  • Then I suggest you read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. And that is how this site work. I give my opinion I didnt close the question. If you disagree make it clear so other doesnt get confused too. – Juan Carlos Oropeza Dec 18 '16 at 18:02
  • Cannot reproduce (works correctly) with EF6.1.3 and MySQL 6.9.8.0 – Ivan Stoev Dec 18 '16 at 18:04
  • @JuanCarlosOropeza I will read thanks for pointing me to this but you were wrong here. Leaving a comment would actually work better. – kechap Dec 18 '16 at 18:06
  • You are wrong again, if that was the case the site wont offer that function. But back to your question. You offer a code and is working, but dont show us what is the result you want. Or the code giving you the error. So my suggestion is try to do the code in plan ANSI SQL first, then we can help you to convert to EF. – Juan Carlos Oropeza Dec 18 '16 at 18:09
  • Can you try this to see the generated SQL and show it to us? http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework – Juan Carlos Oropeza Dec 18 '16 at 18:30
  • After a lot of investigation I can declare that this is a bug. I managed to reproduce it with EF6(6.1.3) and MySQL 5.7 (6.9.9). Switching to MySQL 5.6 fixes the problem. – kechap Dec 28 '16 at 06:51
  • 1
    @kechapito if you are sure it is a bug, you can self-answer your question with that and link to the issue in an issue tracker if there is one. – juunas Dec 28 '16 at 07:36

0 Answers0