locked
LINQ Subquery for Count

    Question

  • My problem is not new but somehow I am unable to find a solution

    I have a News List and a News Comments List

    News Comments list contains comments against a News Item. I want to query all news items depending you a certain condition and count comments for that news item.

    here is the code

    using (var dc = new OptusCRPEntitiesDataContext(SPContext.Current.Web.Url))
    var news = from newsItem in dc.News
                               where
                                   newsItem.NewsCategory.Title == "International News"
                               select new
                                   {
                                       Title = newsItem.Title,
                                       ID = newsItem.Id,
                                       PublishDate = newsItem.PublishDate,
                                   CommentCount =
                                (from c in dc.NewsComments
                                             where c.NewsId.Id == newsItem.Id
                                             select c.Comments.Count().ToString() + " Comments")
                    };
    Repeater1.DataSource = news;
    Repeater1.DataBind();

    but I get "The query uses unsupported elements, such as references to more than one list, or the projection..." at Repeater1.DataBind()

    Any suggestions?

    Friday, March 09, 2012 4:19 AM

Answers

  • Hi,

    The you, may be could perform it in this way:

    var news = from newsItem in dc.News
                from c in dc.NewsComments
                where newsItem.Id == c.NewsId.Id && newsItem.NewsCategory.Title == "International News"
                select new
                {
                     Title = newsItem.Title, 
                     ID = newsItem.Id, 
                     PublishDate = newsItem.PublishDate,
                     CommentCount = c.Comments.Count()
                };
    

    Or also as follows:

    var news = from newsItem in dc.News
                join c in dc.NewsComments on newsItem.Id equals c.NewsId.Id
                where newsItem.NewsCategory.Title == "International News"
                select new
                {
                     Title = newsItem.Title, 
                     ID = newsItem.Id, 
                     PublishDate = newsItem.PublishDate,
                     CommentCount = c.Comments.Count()
                };
    

    Best regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.

    Monday, March 12, 2012 9:43 AM

All replies

  • Is my outer and inner LINQ queries correct?
    Friday, March 09, 2012 6:55 AM
  • Hi,

    If your both entities are related, as they should be, your query would be something like this:

    var news = from newsItem in dc.News
                where newsItem.NewsCategory.Title == "International News"
                select new
                {
                     Title = newsItem.Title, 
                     ID = newsItem.Id, 
                     PublishDate = newsItem.PublishDate,
                     CommentCount = newsItem.NewsComments.Count()
                };
     

    Best regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.

    Friday, March 09, 2012 8:58 AM
  • Comments are stored in an entirely different list
    Sunday, March 11, 2012 10:47 PM
  • Hi,

    The you, may be could perform it in this way:

    var news = from newsItem in dc.News
                from c in dc.NewsComments
                where newsItem.Id == c.NewsId.Id && newsItem.NewsCategory.Title == "International News"
                select new
                {
                     Title = newsItem.Title, 
                     ID = newsItem.Id, 
                     PublishDate = newsItem.PublishDate,
                     CommentCount = c.Comments.Count()
                };
    

    Or also as follows:

    var news = from newsItem in dc.News
                join c in dc.NewsComments on newsItem.Id equals c.NewsId.Id
                where newsItem.NewsCategory.Title == "International News"
                select new
                {
                     Title = newsItem.Title, 
                     ID = newsItem.Id, 
                     PublishDate = newsItem.PublishDate,
                     CommentCount = c.Comments.Count()
                };
    

    Best regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.

    Monday, March 12, 2012 9:43 AM