你做错了：tbl2应该包含ServiceID，而不是名字：

You are doing that wrong: tbl2 should contain the ServiceID, not the name:

ServiceID RollNumber
1          13
1          12
3           2
3          16
2          15

并使用JOIN访问服务名称。



这确实使您想要的最终查询稍微复杂一点，但不是很多：

And use a JOIN to access the Service name.

That does make the final query you want slightly more complex, but not a lot:

SELECT a.Service, ISNULL(b.CNT, 0) 
FROM tbl1 a
LEFT JOIN (SELECT ServiceID, COUNT(RollNumber) AS CNT 
           FROM tbl2
           GROUP BY ServiceID) b 
       ON a.ID = b.ServiceID

自己做两个好处：

1）使用合理的表名！它使您的查询更具可读性，因此更可靠...

2）向tbl2添加IDENTITY id列，以防止重复。您不需要在大多数查询中使用它，但SQL不允许您有重复的行。

And do yourself two favours:
1) Use sensible table names! It makes your queries so much more readable, and thus reliable...
2) Add an IDENTITY id column to tbl2, to prevent duplication. You don't need to use it in most of your queries, but SQL will not allow you to have duplicate rows.

试试这个：

Try this:

var data = from t1 in tbl1
    join t2 in tbl2 on t1.Service.Trim() equals t2.Service.Trim() into eGroup
    from together in eGroup.DefaultIfEmpty()
    select new 
    {
       Service = together.?Service,
       Count = together.Count()
    };

有关详细信息，请参阅：执行左外连接（C＃中的LINQ）| Microsoft Docs [ ^ ]



另一种方法是根据服务名称计算表＃2中的记录数：

For further details, please see: Perform left outer joins (LINQ in C#) | Microsoft Docs[^]

Another way is to calculate count of records in a table #2 based on service name:

var result = tbl1
	.Select(x=> new
	{
		Service = x.Service,
		Count = tbl2.Count(y=> y.Service.Trim()==x.Service.Trim()))
	});

祝你好运！

Good luck!