指定的类型转换无效 C# Entity Framework

Question

你好，我在代码中遇到了“指定的转换无效”错误，但是当我连接到备份数据库时，就不会出现这个错误。我不确定发生了什么。

同样的，这段代码在一个数据库上完美运行，在另一个数据库上却给我报错。

我的控制器：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.Linq.SqlClient;
using System.Text.RegularExpressions;
using MvcPaging;
using lookups.Models;
using lookups.Helpers;


namespace lookups.Controllers
{
public class MPController : Controller
{
    // GET: /Lookups/
    public LpAppsDataDataContext db = new LpAppsDataDataContext();
    public const int DefaultPageSize = 30;


    //get MP Data
         if (countSpace == 1 && v.Count(char.IsLetter) > 3)
                            {
                                if (SqlString.CheckStringForComma(v))
                                {
                                    mpq = db.mps
                                         .Where(c => c.NAME_LAST.ToLower().Contains(SqlString.GetSplitCommaString(v.ToLower(), 0))).OrderBy(c => c.NAME_LAST).ThenBy(f => f.NAME_FIRST);
                                }
                                else
                                {
                                    mpq = db.mps
                                       .Where(c => c.NAME_LAST.ToLower().Contains(SqlString.GetSplitString(v.ToLower(), 1)) || c.NAME_LAST.Contains(SqlString.GetSplitString(v.ToLower(), 0))).OrderBy(c => c.NAME_LAST).ThenBy(f => f.NAME_FIRST);
                                }
                            }
                            else
                            {
                                mpq = db.mps
                                       .Where(c => c.NAME_L
                ViewBag.count = mpq.Count();
            }
        }
        else { ViewBag.count = 0; }
        @ViewBag.col = "Unclaimed Pensions";

        return View(mpq.ToPagedList(currentPageIndex, DefaultPageSize));
    }

}
}

我的模型

[global::System.Data.Linq.Mapping.TableAttribute(Name = "dbo.mp")]
public partial class mp
{

    private string _COMPANY;

    private string _STATE;

    private System.Nullable<System.DateTime> _DOPT;

    private string _SP_STATE;

    private string _PRT_NAME;

    private string _NAME_LAST;

    private string _NAME_FIRST;

    private string _LASTADDRESS;

    private System.Nullable<double> _ID;

    private System.Nullable<System.DateTime> _DATE;

    private string _PLAN_TYPE;

    public mp()
    {
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_COMPANY", DbType = "NVarChar(255)")]
    public string COMPANY
    {
        get
        {
            return this._COMPANY;
        }
        set
        {
            if ((this._COMPANY != value))
            {
                this._COMPANY = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_STATE", DbType = "NVarChar(255)")]
    public string STATE
    {
        get
        {
            return this._STATE;
        }
        set
        {
            if ((this._STATE != value))
            {
                this._STATE = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_DOPT", DbType = "DateTime")]
    public System.Nullable<System.DateTime> DOPT
    {
        get
        {
            return this._DOPT;
        }
        set
        {
            if ((this._DOPT != value))
            {
                this._DOPT = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_SP_STATE", DbType = "NVarChar(255)")]
    public string SP_STATE
    {
        get
        {
            return this._SP_STATE;
        }
        set
        {
            if ((this._SP_STATE != value))
            {
                this._SP_STATE = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_PRT_NAME", DbType = "NVarChar(255)")]
    public string PRT_NAME
    {
        get
        {
            return this._PRT_NAME;
        }
        set
        {
            if ((this._PRT_NAME != value))
            {
                this._PRT_NAME = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_NAME_LAST", DbType = "NVarChar(255)")]
    public string NAME_LAST
    {
        get
        {
            return this._NAME_LAST;
        }
        set
        {
            if ((this._NAME_LAST != value))
            {
                this._NAME_LAST = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_NAME_FIRST", DbType = "NVarChar(255)")]
    public string NAME_FIRST
    {
        get
        {
            return this._NAME_FIRST;
        }
        set
        {
            if ((this._NAME_FIRST != value))
            {
                this._NAME_FIRST = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_LASTADDRESS", DbType = "NVarChar(255)")]
    public string LASTADDRESS
    {
        get
        {
            return this._LASTADDRESS;
        }
        set
        {
            if ((this._LASTADDRESS != value))
            {
                this._LASTADDRESS = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_ID", DbType = "Float")]
    public System.Nullable<double> ID
    {
        get
        {
            return this._ID;
        }
        set
        {
            if ((this._ID != value))
            {
                this._ID = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_DATE", DbType = "DateTime")]
    public System.Nullable<System.DateTime> DATE
    {
        get
        {
            return this._DATE;
        }
        set
        {
            if ((this._DATE != value))
            {
                this._DATE = value;
            }
        }
    }

    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_PLAN_TYPE", DbType = "NVarChar(255)")]
    public string PLAN_TYPE
    {
        get
        {
            return this._PLAN_TYPE;
        }
        set
        {
            if ((this._PLAN_TYPE != value))
            {
                this._PLAN_TYPE = value;
            }
        }
    }
}

堆栈跟踪

Line 257:            @ViewBag.col = "Unclaimed Pensions";
Line 258:
Line 259:            return View(mpq.ToPagedList(currentPageIndex,      DefaultPageSize));
Line 260:        }
Line 261:

Stack Trace: 


[InvalidCastException: Specified cast is not valid.]
 System.Data.SqlClient.SqlBuffer.get_Double() +51
System.Data.SqlClient.SqlDataReader.GetDouble(Int32 i) +62
 Read_mp(ObjectMaterializer`1 ) +2057
 System.Data.Linq.SqlClient.ObjectReader`2.MoveNext() +32
   System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection) +392
   System.Collections.Generic.List`1.AddRange(IEnumerable`1 collection) +10
   MvcPaging.PagedList`1..ctor(IQueryable`1 source, Int32 index, Int32    pageSize, Nullable`1 totalCount) +2014
   MvcPaging.PagingExtensions.ToPagedList(IQueryable`1 source, Int32 pageIndex, Int32 pageSize, Nullable`1 totalCount) +134
   lookups.Controllers.MPController.Mp(Nullable`1 page, String sortOrder) in 

- Ren44

Answer 1

将备份数据库与不工作的数据库进行比较，特别是ID列。确保它们在Type（Float）和Nullable（NULL）能力方面匹配。

抛出的异常表明其中一个列（类型为“double”）与模型不匹配。

“mp”表上的ID列是模型中唯一的双精度列。

- leemac

---

谁知道检查两个数据库会解决我的问题，这么简单。谢谢，我很感激。 - Ren44

那是我犯过的最愚蠢的错误，浪费了一整天的时间才弄清楚。后来我意识到我使用的是错误的数据库连接，它实际上指向的是旧版本的数据库，而不是与 EF 模型相匹配的最新版本。注意：始终检查您的配置文件，以在您的工作环境（调试、测试或任何其他可能的环境）中使用最新的数据库。 - Jabez

Answer 2

在 SQL Server Developer 2019 中，我使用 sys 表格创建了一个 SQL 脚本，以确定 SQL Server 认为每个列的类型是什么。

select
    obj.name as [Table]
    , col.name as [Column]
    , case
        when type.[precision] > 0 and type.scale = 0 then type.name + '(' + CONVERT(nvarchar(8), type.precision) + ')'
        when type.[precision] > 0 and type.scale > 0 then type.name + '(' + CONVERT(nvarchar(8), type.precision) + ',' + CONVERT(nvarchar(8), type.scale) + ')'
        else type.name end as [FullType]
from
    sys.objects as [obj]
    inner join sys.columns as [col]
        on obj.object_id = col.object_id
    inner JOIN sys.types as [type]
        on col.system_type_id = type.system_type_id
where 
    obj.type = N'U'

然后我可以在公共表达式（CTE）或其他类似结构中使用脚本来过滤特定列。

- gregsonian