Sethen Sethen - 5 days ago 5
ASP.NET (C#) Question

Entity Framework count child entities

I have a Entity Framework query that looks like this:

public object GetMatchupByVideoId(int id)
{
var videoMatchup = _DBContext.Videos
.Where(v => v.VideoID == id)
.Select(v => new {
id = v.MatchupID,
players = v.Matchup.Players.Select(mp => new
{
character = new {
name = mp.Character.Name,
votes = v.Matchup.MatchupVotes
.Where(mv => mv.CharacterID == mp.CharacterID)
},
outcome = mp.Outcome
})
});

return videoMatchup;
}


This query essentially gives me a matchup of characters with their respective votes. If you look at the
votes
property, you will see it's merely filtering out the votes based on the
CharacterID
. This works as I expect it to.

However, I want to take this a step further and actually get the count of votes for each character. So, if I change my query to something like this:

public object GetMatchupByVideoId(int id)
{
var videoMatchup = _DBContext.Videos
.Where(v => v.VideoID == id)
.Select(v => new {
id = v.MatchupID,
players = v.Matchup.Players.Select(mp => new
{
character = new {
name = mp.Character.Name,
votes = v.Matchup.MatchupVotes
.Where(mv => mv.CharacterID == mp.CharacterID)
.Count()
},
outcome = mp.Outcome
})
});

return videoMatchup;
}


Adding a
.Count()
at the end of the query in
votes
, I get an error:

info: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
Request starting HTTP/1.1 GET http://localhost:5000/api/videos/1/matchup application/json
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
Executing action method Mindgame.Controllers.VideosController.GetMatchupByVideoId (mindgame-api) with arguments (1) - ModelState is Valid
info: Microsoft.AspNetCore.Mvc.Internal.ObjectResultExecutor[1]
Executing ObjectResult, writing value Microsoft.AspNetCore.Mvc.ControllerContext.
fail: Microsoft.AspNetCore.Server.Kestrel[13]
Connection id "0HL0OILHK80GT": An unhandled exception was thrown by the application.
Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'no such column: v.Matchup.MatchupID'.
at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeObject(JsonWriter writer, Object value, JsonObjectContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.SerializeList(JsonWriter writer, IEnumerable values, JsonArrayContract contract, JsonProperty member, JsonContainerContract collectionContract, JsonProperty containerProperty)
at Newtonsoft.Json.Serialization.JsonSerializerInternalWriter.Serialize(JsonWriter jsonWriter, Object value, Type objectType)
at Newtonsoft.Json.JsonSerializer.SerializeInternal(JsonWriter jsonWriter, Object value, Type objectType)
at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.WriteObject(TextWriter writer, Object value)
at Microsoft.AspNetCore.Mvc.Formatters.JsonOutputFormatter.<WriteResponseBodyAsync>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeResultAsync>d__32.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeResultFilterAsync>d__31.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeAllResultFiltersAsync>d__29.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeResourceFilterAsync>d__23.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeAsync>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Builder.RouterMiddleware.<Invoke>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Hosting.Internal.RequestServicesContainerMiddleware.<Invoke>d__3.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Server.Kestrel.Internal.Http.Frame`1.<RequestProcessingAsync>d__2.MoveNext()
info: Microsoft.AspNetCore.Hosting.Internal.WebHost[2]
Request finished in 787.4541ms 200 application/json; charset=utf-8


The point of interest in this error message is here:

SQLite Error 1: 'no such column: v.Matchup.MatchupID'.


I am unsure why I get this error if the previous query worked without the
.Count()
. Further, I am not using
v.Matchup.MatchupID
at all in my query. I can only imagine this is something the underlying SQL is doing.

Here are my models for
Video
,
Player
, 'Matchup
, and
MatchupVote`:

public class Video
{
[JsonPropertyAttribute("id")]
public int VideoID { get; set; }
[ForeignKeyAttribute("Game")]
public int GameID { get; set; }
public Game Game { get; set; }
[ForeignKeyAttribute("Matchup")]
public int MatchupID { get; set; }
public Matchup Matchup { get; set; }
[ForeignKeyAttribute("User")]
public int UserID { get; set; }
public User User { get; set; }
public string YoutubeID { get; set; }
public string Description { get; set; }
public string Title { get; set; }
}

public class Player
{
[JsonPropertyAttribute("id")]
public int PlayerID { get; set; }
public int CharacterID { get; set; }
public Character Character { get; set; }
public Players.Outcomes Outcome { get; set; }
}

public class Matchup
{
[JsonPropertyAttribute("id")]
[Required]
public int MatchupID { get; set; }
public List<MatchupVote> MatchupVotes { get; set; }
public List<Player> Players { get; set; }
}

public class MatchupVote
{
[JsonPropertyAttribute("id")]
public int MatchupVoteID { get; set; }
[ForeignKeyAttribute("Character")]
public int CharacterID { get; set; }
[ForeignKeyAttribute("Matchup")]
public int MatchupID { get; set; }
public Matchup Matchup { get; set; }
}


So, my question is, how can I use the
.Count()
method like this to get a count of the votes I want for each character in my query?

I am using .NET Core and Entity Framework Core for this project.

Answer

Here's the answer from the comments in case anyone stumbles across this.

_DBContext.MatchupVotes.Where(mv => mv.CharacterID == mp.CharacterID).Count()

As far as performance, I don't think it will make a difference as the underlying SQL is doing a distinct count that is filtered. I'd check the profiler if you were super concerned about performance, but since it is a filtered count, I wouldn't think there would be a performance hit versus what you were trying.

Comments