locked
How to count the Rows from second table in Entity Framework RRS feed

  • Question

  • User2046726303 posted

    Hello Guys,

    I am creating the blog module in MVC5 with Entity Framework 6. I have two tables in my database one is BlogPost and second one BlogComment.  BlogComment table hold the ID of the BlogPost table but there is not any foreign key relationship in it.

    Now my problem is in my gird I have show the blog name with the number of comment. How we count the number of rows from the second table in entity framework with the BlogPost table ID.

    Blog Name ----------------------------------------- Comments

    LDAP not working on IIS -------------------------- 23

    LDAP not working on IIS-1 -------------------------- 26

    LDAP not working on IIS-2 -------------------------- 27

    The above output I have to show. Please give me some code sample for the same.

    Thanks

    JellySaini

    Sunday, September 6, 2015 12:31 AM

Answers

  • User-271186128 posted

    Hi jellysaini,

    According to your description, I suggest you could try to use Group By clause, and try to use the following code:

                    var query = (from bb in context.BlogPosts
                                join cc in context.BlogComments
                                on bb.BlogID equals cc.BlogID
                                group new { bb, cc } by new { bb.BlogName, bb.BlogID } into newgroup
                                select new
                                {
                                    BlogID = newgroup.Key.BlogID,
                                    BlogName = newgroup.Key.BlogName,
                                    Count = newgroup.Count()
                                }).ToList();

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 7, 2015 1:54 AM

All replies

  • User-698989805 posted

    You could use the following to count:

    var r = (from q in context.QualityApproval
                             select new
                             {
                               Count = (from c in context.OrderPurchaseLine
                                        join d in context.OrderPurchaseLine on c.OrderID equals d.OrderID
                                        join f in context.EmployeeDetails on d.StoreID equals f.StoreID
                                        join i in context.Category on c.CategoryID equals i.CategoryID
                                        join j in context.Item on c.ItemID equals j.ItemID
                                        where d.StoreID == f.StoreID && f.EmployeeID == (g) && c.Status == 3 && c.OrderLine != null
                                        select new { c.OrderLine }).Distinct().Count()
                             }).Distinct().ToList();

    Sunday, September 6, 2015 10:03 AM
  • User-271186128 posted

    Hi jellysaini,

    According to your description, I suggest you could try to use Group By clause, and try to use the following code:

                    var query = (from bb in context.BlogPosts
                                join cc in context.BlogComments
                                on bb.BlogID equals cc.BlogID
                                group new { bb, cc } by new { bb.BlogName, bb.BlogID } into newgroup
                                select new
                                {
                                    BlogID = newgroup.Key.BlogID,
                                    BlogName = newgroup.Key.BlogName,
                                    Count = newgroup.Count()
                                }).ToList();

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 7, 2015 1:54 AM
  • User2046726303 posted

    Hi,

    Thanks for your help.

    It works perfect for me.

    Thanks

    Tuesday, September 8, 2015 2:57 AM