Get relations with conditions in Entity Framework Core

.net-core c# entity-framework-core linq sql-server

Question

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:

  1. take only 2 messages at a time
  2. pass a parameter indicating I want to take only messages where messageID is less than a given parameter

I 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.IEnumerable1[Microsoft.EntityFrameworkCore.Storage.ValueBuffer]' cannot be used for parameter of type 'System.Collections.Generic.IEnumerable1[Dal.Models.Message]' of method 'System.Collections.Generic.IEnumerable1[Dal.Models.Message] _ToEnumerable[Message](System.Collections.Generic.IEnumerable1[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, IDiagnosticsLogger1 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, Func1 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](IQueryable1 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?

1
1
5/2/2018 8:41:33 PM

Accepted Answer

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();
1
5/8/2018 4:09:36 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow