sqlserver按日期分组统计数据，.net日期为空的补0——优化版

现在数据库中存在大量数据，我这里想要按天分组统计数据中的数量

select 
convert(varchar(7),Datetimes,120) as name,
count(Id) as value
from 表名 
group by convert(varchar(7),name,120)

这里的name是数据表中的时间字段名称，value是主键字段名称，最后分组也是按照日期分组，我这里进行的日期的转换
结果为：

但是有个问题，我想查询近7天的数据统计，但是有的日期为空，这里我就要在.net后台进行补0

 public DataTable GetDayCount(out int result)
    {
        try
        {
             string sql = $@" select 
convert(varchar(7),Datetimes,120) as name,
count(Id) as value
from 表名 
group by convert(varchar(7),name,120)";//字符串
            using (var conn = new SqlConnection(connStr))
            {
                DataTable dt = conn.GetDataTable(sql);

                DataTable dtNew = new DataTable();
                if (dt != null && dt.Rows.Count > 0)
                {
                    dtNew = new DataTable();
                    dtNew.Columns.Add("name", typeof(System.String));
                    dtNew.Columns.Add("value", typeof(System.Int32));

                    for (int i = -6; i <= 0; i++)
                    {
                        DataRow row = dtNew.NewRow();
                        var date = dt.AsEnumerable().Where(n => n.Field<string>("name") == DateTime.Now.AddDays(i).ToString("yyyy-MM-dd")).FirstOrDefault();
                        if (date != null)
                        {
                            row["name"] = date.Field<string>("name");
                            row["value"] = date.Field<int>("value");
                        }
                        else
                        {
                            row["name"] = DateTime.Now.AddDays(i).ToShortDateString();
                            row["value"] = 0;
                        }
                        dtNew.Rows.Add(row);
                    }
                }
                else
                {
                    dtNew = new DataTable();
                    dtNew.Columns.Add("name", typeof(System.String));
                    dtNew.Columns.Add("value", typeof(System.Int32));

                    for (int i = -6; i <= 0; i++)
                    {
                        DataRow row = dtNew.NewRow();
                        row["name"] = DateTime.Now.AddDays(i).ToString("yyyy-MM-dd");
                        row["value"] = 0;
                        dtNew.Rows.Add(row);
                    }
                }

                result = 0;
                return dtNew;
            }
        }
        catch (Exception ex)
        {
            result = 1;
            return new DataTable();
        }
    }

最终的返回数据的格式为：

  {
"errcode": 0,
"errmsg": "查询成功！",
"result": [
    {
        "name": "2021/1/6",
        "value": 0
    },
    {
        "name": "2021/1/7",
        "value": 0
    },
    {
        "name": "2021/1/8",
        "value": 0
    },
    {
        "name": "2021/1/9",
        "value": 0
    },
    {
        "name": "2021/1/10",
        "value": 0
    },
    {
        "name": "2021/1/11",
        "value": 0
    },
    {
        "name": "2021/1/12",
        "value": 0
    }
]
}