I need Linq to sql queries with join and count

Question

i need to count how many singles of one matchmaker in tbluserregistration table. i use following code,in this code if codition for filtering grid record. please replay me fastly.

private IQueryable chanims
{
    get
    {

        EntitiesModelMX dbContext = new EntitiesModelMX();


        //var query = from p in dbContext.TblShadChanims
        //            where p.Confirmed == true || p.Status != null
        //            orderby p.ShadChanimID descending
        //            select p;
        var query = from p in dbContext.TblShadChanims
                    join u in dbContext.TblUserRegistrations
                    on p.ShadChanimID equals u.ShadChanID into usercount
                    orderby p.ShadChanimID descending
                    select new
                    {
                        p,
                        Singles = usercount.Where(usr => usr.ShadChanID !=0).Count()
                    };

        if (txtFirstNameFilter.Text.Trim().Length > 0)
        {
            query =                  (IOrderedQueryable<MainDataMX.TblShadChanim>)query.Where(p => p.p.FirstName.Contains(txtFirstNameFilter.Text.Trim()));
        }


        if (txtLastNameFilter.Text.Trim().Length > 0)
        {
            query = (IOrderedQueryable<MainDataMX.TblShadChanim>)query.Where(p => p.LastName.Contains(txtLastNameFilter.Text.Trim()));
        }

        if (txtPhoneFilter.Text.Trim().Length > 0)
        {
            query = (IOrderedQueryable<MainDataMX.TblShadChanim>)query.Where(p => p.Phone1.Contains(txtPhoneFilter.Text.Trim()));
        }

        if (!ddlStatus.SelectedValue.Equals("ALL"))
        {
            query = (IOrderedQueryable<MainDataMX.TblShadChanim>)query.Where(p => p.Status == (int?)int.Parse(ddlStatus.SelectedValue));
        }

        if (txtEmailFilter.Text.Trim().Length > 0)
        {
            query = (IOrderedQueryable<MainDataMX.TblShadChanim>)query.Where(p => p.Email.Contains(txtEmailFilter.Text.Trim()));
        }


        if (!drpGender.SelectedValue.Equals("ALL"))
        {
            query = (IOrderedQueryable<MainDataMX.TblShadChanim>)query.Where(p => p.IsMale == (bool?)bool.Parse(drpGender.SelectedValue));
        }

        return query;
    }
}

above code is not working.please reply back.

Answer 1

just use Count with Grouping, please check the following links:

Group and count items linq with groupby and count

Hope to be in use :)