I am working on an app with .net core 2 and Entity Framework Core 2.
I have the following models:
Topic {
int TopicID,
List<Message> Messages,
…
}
Message {
int MessageID,
int TopicID,
Topic Topic,
int UserID,
User User,
…
}
User {
int UserID,
…
}
I want to fetch all data for a topic and its relations, so that if I have a topic with 3 messages, each written by a different user, I would get the following object:
var topic = new Topic() {
TopicID: 1,
Messages: new Message[] {
new Message() { MessageID: 1, TopicID: 1, UserID: 1, User: new User() { UserID: 1 } },
new Message() { MessageID: 2, TopicID: 1, UserID: 2, User: new User() { UserID: 2 } },
new Message() { MessageID: 3, TopicID: 1, UserID: 1, User: new User() { UserID: 3 } }
}
}
This was simple and I did it with the following implementation:
return context.Topics
.Include(t => t.Messages)
.ThenInclude(m => m.User)
.AsNoTracking()
.SingleOrDefault(t => t.TopicID == topicId);
but now I want to add some conditions to the messages:
messageID
is less than a given parameterI checked and found out that Include()
does not have the ability to use Take()
and Where()
, so I tried to implement this with Select()
:
return context.Topics
.Where(t => t.TopicID == topicId)
.Select(t => new {
TopicId = t.TopicID,
Messages = t.Messages
.Where(m => m.MessageID < messageId)
.Take(2)
})
.AsNoTracking()
.SingleOrDefault();
When I remove the Where()
and Take()
from inside the Select()
, it works (but without the conditions I want to implement), but if I keep them, it returns errors:
{System.ArgumentException: Expression of type 'System.Collections.Generic.IEnumerable
1[Microsoft.EntityFrameworkCore.Storage.ValueBuffer]' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable
1[Dal.Models.Message]' of method 'System.Collections.Generic.IEnumerable1[Dal.Models.Message] _ToEnumerable[Message](System.Collections.Generic.IEnumerable
1[Dal.Models.Message])'Parameter name: arg0 at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index) at System.Linq.Expressions.Expression.Call(MethodInfo method, Expression arg0) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ProjectionExpressionVisitor.VisitSubQuery(SubQueryExpression expression) at Remotion.Linq.Clauses.Expressions.SubQueryExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.Visit(Expression expression) at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](ReadOnlyCollection1 nodes, String callerName) at Remotion.Linq.Parsing.RelinqExpressionVisitor.VisitNew(NewExpression expression) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.VisitNew(NewExpression newExpression) at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.RelationalProjectionExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitSelectClause(SelectClause selectClause, QueryModel queryModel) at Remotion.Linq.Clauses.SelectClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel) at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database, IDiagnosticsLogger
1 logger, Type contextType) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_01.<Execute>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func
1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable
1 source) at Features.Topic.TopicMessagesQueries.getTopic(RbbContext context, Int32 entitiesFetchLimit, Int32 topicId) in TopicQueries.cs:line 20 at Features.Topic.TopicService.getTopic(RbbContext context, ITopicQueries topicQueries, Int32 topicId) in TopicService.cs:line 43 at Features.Topic.TopicController.Get(Int32 id) in TopicController.cs:line 34}
I also want to get the user relation from the messages, I tried adding another Select()
inside t.Messages
, but that also did not work.
I can't find similar references in google/stackoverflow of queries with 2-level relationships and conditions on the relationships.
Can anyone here can direct me to get this query right?
You are returning an anonymous type. You will have to project the final output to your Topic
object. This might look a bit ugly, but it should hit the database just once and get just the required records. Do run a profiler to see the resulting query.
return context.Topics
.Where(t => t.TopicID == topicId)
.Select(t => new {
TopicId = t.TopicID,
Messages = t.Messages
.Where(m => m.MessageID < messageId)
.Take(2)
}).AsNoTracking()
.AsEnumerable().Select(x => new Topic
{
TopicId = x.TopicId ,
Bookings = x.Messages.ToList()
}).SingleOrDefault();