C# Linq to SQL 无效转换异常

Question

所以我使用 DataContext 作为与数据库的连接。我想从 DB 中获取所有公司，但已删除的公司除外。我的公司对象:

[Table(Name = "Companies")]
class Company {
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id { get; set; }
    [Column]
    public bool Deleted { get; set; }
    [Column]
    public DateTime DateCreated { get; set; }
    [Column]
    public DateTime? DateModified { get; set; }
    [Column]
    public string Name { get; set; }

    private EntitySet<Platform> _Platforms;
    [Association(Storage = "_Platforms", OtherKey = "CompanyId")]
    public virtual EntitySet<Platform> Platforms {
        get {
            return this._Platforms;
        }
        set {
            this._Platforms.Assign(value);
        }
    }

    public Company() {
        this._Platforms = new EntitySet<Platform>();
    }

}

我的 DataContext 对象:

class DataManager : DataContext {

    public Table<Company> Companies {
        get {
            return this.GetTable<Company>();
        }
    }
//other tables emited
}

当我想获得公司(除已删除的以外的所有公司)时，我使用这个:

List<Company> comp = _db.Companies.Where(x => !x.Deleted).ToList();

_db是单独创建的，此时不为NULL。

我在“!x.Deleted”部分遇到异常:“InvalidCastException:指定的转换无效。”

堆栈跟踪:

   at System.Data.SqlClient.SqlBuffer.get_Boolean()
   at System.Data.SqlClient.SqlDataReader.GetBoolean(Int32 i)
   at Read_Company(ObjectMaterializer`1 )
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at GameShop.Form1.UpdateCompanyList() in c:\Users\Xpym\Documents\Visual Studio 2012\Projects\GameShop\GameShop\Form1.cs:line 117
   at GameShop.Form1..ctor() in c:\Users\Xpym\Documents\Visual Studio 2012\Projects\GameShop\GameShop\Form1.cs:line 20
   at GameShop.Program.Main() in c:\Users\Xpym\Documents\Visual Studio 2012\Projects\GameShop\GameShop\Program.cs:line 16
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

在数据库中(与在对象中相同)，Deleted 字段不可为空。

此外，我可能还会说我没有使用任何自动代码/表生成器(如 EF)。

请帮助我解决这个问题，因为我自己找不到解决方案。

更新在另一种方法中，我使用以下代码并且它执行时没有任何问题:

_db.Companies.Any(x=>!x.Deleted && x.Name == textBoxCompanyName.Text)

更新SQL 中的表是这样创建的:

CREATE TABLE [dbo].[Companies](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Deleted] [tinyint] NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateModified] [datetime] NULL,
[Name] [varchar](50) NOT NULL)

此外，在调试时，我注意到 LINQ 创建的 SQL 查询很奇怪:

SELECT [t0].[Id], ... FROM [Companies] as [t0]

通常我会看到以下内容:

SLECT [t0].[Id] as [Id], ... FROM [Companies] as [t0]

也许这将有助于找到解决方案。

Answer 1

您的 DbMapping 不正确。您需要将 [dbo].[Companies].[Deleted] 更改为 bit 或将 Company.Deleted 更改为 byte。

ADO.Net 将 tinyint 转换为 System.Byte ( http://msdn.microsoft.com/en-us/library/cc716729%28v=vs.110%29.aspx )。

实际上，您正在尝试将该字节直接转换为 bool 值。这就是您的异常的来源。