none
How to Obtain Table Counts? RRS feed

  • Question

  • Hi,

    I'm new to linq and struggling with the syntax.  Trying to obtain the row counts in several tables.  The following syntax works unless the context.My01t_file table is empty.  Any ideas on how to solve this? 

            

    using (MyEntities context = new MyEntities())
        {
            var z =
                (
                from My01t in context.My01t_file
                .Take(1)
                select new {
                            FilCount = context.My01t_file.Count()
                           ,ErrCount = context.My16t_fileerr.Count() +
                                              context.My10t_filesuberr.Count() +
                                              context.My05t_fileminierr.Count()
                            }
                );        

        }

    Thanks in advance! 

    Thursday, November 27, 2014 1:50 AM

Answers

  • Hello Dave,

    Thanks for your clarification, you could use the .DefaultIfEmpty() as:

    using (DataClasses1DataContext db = new DataClasses1DataContext())
                {
                    var result = (from order in db.Orders.Take(1).DefaultIfEmpty()
                                  select new { FileCount = db.Orders.Count(), ErrorCount = db.OrderDetails.Count() }).ToList(); ;
                }

    Then if there are not records in Order table, there is a default record return so that the anonymous type would be executed.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by DaveIII Thursday, November 27, 2014 7:33 PM
    Thursday, November 27, 2014 6:07 AM
    Moderator

All replies

  • Hello Dave,

    >>  The following syntax works unless the context.My01t_file table is empty

    I do not quite understand what this means, as far as I know, the Count() method would return 0 if there is not records, is that what you means the context.My01t_file.Count() return 0? If it is, this should be that the My01t_file table contains no records in database, please check if it is.

    Regards,

    Fred.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 27, 2014 3:20 AM
    Moderator
  • Hi Fred,

    Thanks for the quick reply.

    When context.My01t_file is empty, the anonymous type is not executed so the Counts are not returned.  Instead, "nothing" is returned.  I want the Counts to be "0" when the table is empty.  Make sense? 

    Thursday, November 27, 2014 4:23 AM
  • Hello Dave,

    Thanks for your clarification, you could use the .DefaultIfEmpty() as:

    using (DataClasses1DataContext db = new DataClasses1DataContext())
                {
                    var result = (from order in db.Orders.Take(1).DefaultIfEmpty()
                                  select new { FileCount = db.Orders.Count(), ErrorCount = db.OrderDetails.Count() }).ToList(); ;
                }

    Then if there are not records in Order table, there is a default record return so that the anonymous type would be executed.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by DaveIII Thursday, November 27, 2014 7:33 PM
    Thursday, November 27, 2014 6:07 AM
    Moderator
  • Brilliant!  Thank you and enjoy your Thanksgiving! 
    Thursday, November 27, 2014 7:33 PM