Linq LEFT JOIN, if the count is 0 it returns 1

Question

I'm trying to write this query in linq

SELECT        dbo.Tbl_Users.ID, COUNT(dbo.Tbl_Inputs.ID) AS Inputs
FROM            dbo.Tbl_Users LEFT JOIN
                          dbo.Tbl_Inputs ON dbo.Tbl_Inputs.User_ID = dbo.Tbl_Users.ID
GROUP BY dbo.Tbl_Users.ID

this is my linq code, I made a class User() which holds the userid and the count of inputs the user made in Tbl_Inputs:

List<User> GetAllUsers()
        {
            List<User> userlist = new List<User>();
            userlist = (from u in Data.Tbl_Users
                        join i in Data.Tbl_Inputs on u.ID equals i.User_ID
                        into Joined
                        from p in Joined.DefaultIfEmpty()
                       group p by new { u.ID }
                       into grp select new User()
                       {
                            Id = grp.Key.ID,
                            Inputs = grp.Count()
                       }).ToList();

            return userlist;
        }

The problem is if the input is 0 it returns 1.

Can't figure it out.

Thanks

Does `User` have a navigation property to `Tbl_Inputs`? – James Nov 15 '13 at 10:28 — James, Nov 15 '13 at 10:28

Shital Shah · Accepted Answer · 2013-11-15T10:37:12.137

3

I think you need to check for null child when getting count. Try replacing your line with below:

Inputs = grp.Count(t=>t.User_ID != null)

edited Nov 15 '13 at 10:37

answered Nov 15 '13 at 10:30

Shital Shah

63,284
17
238
185

score 1 · Answer 2 · answered Nov 29 '17 at 13:06

1

I know it's been a long time, but for registration, you can do the following to bring the value 0:

inputs = grp.Count(t => t > 0);

answered Nov 29 '17 at 13:06

Daniel Jorge de Souza

11
2

Value 1 is brought because the count function counts null values. So, if your result is null, the value will be 1. You can see more here: https://stackoverflow.com/a/44636034/2607793 – Daniel Jorge de Souza Nov 29 '17 at 13:12

Linq LEFT JOIN, if the count is 0 it returns 1

2 Answers2