ABP framework LINQ to Entities for subquery 'count'

Question

Query for GetAllForumMember, any advice?

System.NotSupportedException:“LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[Weixin.Kia.Community.ThreadComment] GetAll()' method, and this method cannot be translated into a store expression.”

Answer 1

At the moment your code is calling your repository class and Entity framework cannot translate that into SQL.

I have presumed how your models are structured but you can resolve your issue by using the tables that are in the database rather than your repository.

public class ForumMember
{
    public int Id { get; set; }
}

public class Member
{
    public int MemberId { get; set; }
}

public class ThreadComment
{
    public int CreatorUserId { get; set; }
}


public class ForumContext : DbContext
{
    public DbSet<ForumMember> ForumMembers { get; set; }
    public DbSet<Member> Members { get; set; }
    public DbSet<ThreadComment> ThreadComments { get; set; }
}

public class Repository
{
    public IEnumerable<Something> GetAllForumMember()
    {
        using (var context = new ForumContext())
        {
            var query = from fm in context.ForumMembers
                        join m in context.Members on fm.Id equals m.MemberId
                        //where add where clause
                        select new Something
                        {
                            memberId = m.MemberId,
                            commentCount = context.ThreadComments.Count(x => x.CreatorUserId == m.MemberId)
                            //... add other properties that you want to get.
                        };

             return query.ToList();

        }
    }
}

Please note this is untested code. There is a learning curve to using Entity Framework & Linq-to-SQL so I would advise you go through tutorials. You can also get tools like LinqPad, which may help you get used to writing Linq queries on your database.

I hope this is somewhat helpful. If not feel free to update your question with more information, e.g. including the code for your models or database context.