实体框架计数子实体
问题描述：
我有一个实体框架查询，看起来像这样：实体框架计数子实体
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;
}
该查询本质上给我的字符各自的投票对决。如果你看看votes属性，你会发现它仅仅根据CharacterID筛选出了选票。这工作正如我所期望的那样。
但是，我想更进一步，让每个角色的选票数量。所以，如果我改变我的查询是这样的：
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;
}
在votes查询的末尾添加.Count()，我得到一个错误：
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
的此错误的兴趣点消息是在这里：
SQLite Error 1: 'no such column: v.Matchup.MatchupID'.
我不确定为什么我得到这个错误，如果前面的查询工作不.Count()。此外，我在查询中完全没有使用v.Matchup.MatchupID。我只能想象这是底层SQL正在做的事情。
这里是我的模型Video，Player，“对决, 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; }
}
所以，我的问题是，我怎么能使用.Count()方法这样得到我想要的投票计数为我查询中的每个字符？
我在这个项目中使用了.NET Core和Entity Framework Core。
答
下面是来自评论的答案，以防有人绊倒这一点。
_DBContext.MatchupVotes.Where(mv => mv.CharacterID == mp.CharacterID).Count()
就性能而言，我不认为它会产生差异，因为底层SQL正在执行被过滤的不同计数。如果你对性能高度关注，我会检查性能分析器，但由于它是过滤计数，所以我不认为性能会受到影响。
您的表格结构？视频课程和播放器课程！用它更新帖子 – Aravind
@Aravind我更新了。 – Sethen
这里你指的是游戏类和用户类中的外键。检查您是否在这两个类中配置了反向导航属性。如果可能的话更新他们 – Aravind