Column contains NULL data

使用Entity Framework时，当某些不能为空的列数据丢失（如：int、DateTime、long），EF查询时将报错：Column contains NULL data。

这里我们需要定位哪一行，哪一列导致的错误。

思路 ：

找出实体类哪些属性的数据类型不允许为NULL，再逐列生成SQL分别连接数据库查找为空的数据。

实现：

        /// <summary>
        /// 获取空有空值的行
        /// </summary>
        /// <returns></returns>
        public IActionResult GetNullRows(string modelName, string fieldName) {
            Type type = ToolEF.GetModelType(modelName);
            IEnumerable<string> keys = ToolEF.GetKeys(modelName);
            string tableName = ToolEF.GetTableName(modelName);
            string sql= $"select {string.Join(",",keys)}  from  {tableName} where {fieldName} is null and rownum<11";
            DataTable rows= db.Database.SqlQuery(sql);
            return Ok(new{state=true,  sql, rows });
        }

 /// <summary>
        /// 获取表名
        /// </summary>
        /// <param name="modelName"></param>
        /// <returns></returns>
        public static string GetTableName(string modelName)
        {
            Type type = GetModelType(modelName);
            object[] tableAttribute = type.GetCustomAttributes(typeof(TableAttribute), false);
            object firstAttribute = tableAttribute.FirstOrDefault();
            if (firstAttribute != null)
            {
                return (firstAttribute as TableAttribute).Name;
            }
            return modelName;
        }

//获取有空值的列
        public static JsonReturn GetNullCell(string className)
        {
            string tableName = GetTableName(className);
            DataTable table = OracleDbHelper.GetDataTable($"select * from {tableName}");
            Type tp = GetModelType(className);
            var ps = tp.GetProperties(BindingFlags.Public | BindingFlags.Instance);
            string[] pns = ps.Where(property =>
              property.PropertyType == typeof(int)
            || property.PropertyType == typeof(DateTime)
            || property.PropertyType == typeof(decimal)
            || property.PropertyType == typeof(bool)
            || property.PropertyType == typeof(short)
            || property.PropertyType == typeof(long)
            || property.PropertyType.BaseType.Name == "Enum"
              ).Select(s => s.Name).ToArray();


            string keys = string.Join(",", GetKeys(className));
            string sql = string.Empty;
            foreach (string field in pns)
            {
                sql = $"select count(1) from {tableName} where  {field} is null";
                int res = OracleDbHelper.GetIntScalar(sql);
                if (res > 0)
                {
                    return new JsonReturn() { state = false, msg = $"select {keys},{field} from {tableName} where  {field} is null" };
                }
            }
            return new JsonReturn() { state = true, msg = "无空列" };
        }

        public static IEnumerable<string> GetKeys(string tableName)
        {
            Type type = GetModelType(tableName);
            //实体类中的信息
            var properrties = type.GetProperties();
            foreach (var p in properrties)
            {
                //object[] tableAttribute = type.GetCustomAttributes(typeof(KeyAttribute), false);
                bool hasKey = p.IsDefined(typeof(KeyAttribute), false);
                if (hasKey)
                    yield return p.Name;
            }
            yield break;
        }

        public static Type GetModelType(string className)
        {
            Assembly assembly = Assembly.Load("IOTMaas_Models");
            string fullName = $"RCSCloud.Models.{className}";
            return assembly.GetType(fullName);
        }