Linq To SQL - 指定的转换无效 - SingleOrDefault()

Question

我正在尝试完成以下操作...

Request request = (
     from r in db.Requests 
     where r.Status == "Processing" && r.Locked == false 
     select r
).SingleOrDefault();

它抛出了以下异常...

Message:
Specified cast is not valid.

StackTrace:
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)
   at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source)
   at GDRequestProcessor.Worker.GetNextRequest()

有人能帮我吗？提前感谢！

---

模式细节可以在下面找到...

[Table(Name="dbo.Requests")]
public partial class Request : INotifyPropertyChanging, INotifyPropertyChanged
{

    private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);

    private int _RequestId;

    private string _LoanNumber;

    private string _ClientCode;

    private int _RequestTypeId;

    private bool _HasParameters;

    private string _Status;

    private bool _Locked;

    private string _ErrorMessage;

    private int _ReferenceId;

    private EntitySet<RequestParameter> _RequestParameters;

    private EntityRef<RequestType> _RequestType;

#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate(System.Data.Linq.ChangeAction action);
partial void OnCreated();
partial void OnRequestIdChanging(int value);
partial void OnRequestIdChanged();
partial void OnLoanNumberChanging(string value);
partial void OnLoanNumberChanged();
partial void OnClientCodeChanging(string value);
partial void OnClientCodeChanged();
partial void OnRequestTypeIdChanging(int value);
partial void OnRequestTypeIdChanged();
partial void OnHasParametersChanging(bool value);
partial void OnHasParametersChanged();
partial void OnStatusChanging(string value);
partial void OnStatusChanged();
partial void OnLockedChanging(bool value);
partial void OnLockedChanged();
partial void OnErrorMessageChanging(string value);
partial void OnErrorMessageChanged();
partial void OnReferenceIdChanging(int value);
partial void OnReferenceIdChanged();
#endregion

    public Request()
    {
        this._RequestParameters = new EntitySet<RequestParameter>(new Action<RequestParameter>(this.attach_RequestParameters), new Action<RequestParameter>(this.detach_RequestParameters));
        this._RequestType = default(EntityRef<RequestType>);
        OnCreated();
    }

    [Column(Storage="_RequestId", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
    public int RequestId
    {
        get
        {
            return this._RequestId;
        }
        set
        {
            if ((this._RequestId != value))
            {
                this.OnRequestIdChanging(value);
                this.SendPropertyChanging();
                this._RequestId = value;
                this.SendPropertyChanged("RequestId");
                this.OnRequestIdChanged();
            }
        }
    }

    [Column(Storage="_LoanNumber", DbType="VarChar(50) NOT NULL", CanBeNull=false)]
    public string LoanNumber
    {
        get
        {
            return this._LoanNumber;
        }
        set
        {
            if ((this._LoanNumber != value))
            {
                this.OnLoanNumberChanging(value);
                this.SendPropertyChanging();
                this._LoanNumber = value;
                this.SendPropertyChanged("LoanNumber");
                this.OnLoanNumberChanged();
            }
        }
    }

    [Column(Storage="_ClientCode", DbType="VarChar(50) NOT NULL", CanBeNull=false)]
    public string ClientCode
    {
        get
        {
            return this._ClientCode;
        }
        set
        {
            if ((this._ClientCode != value))
            {
                this.OnClientCodeChanging(value);
                this.SendPropertyChanging();
                this._ClientCode = value;
                this.SendPropertyChanged("ClientCode");
                this.OnClientCodeChanged();
            }
        }
    }

    [Column(Storage="_RequestTypeId", DbType="Int NOT NULL")]
    public int RequestTypeId
    {
        get
        {
            return this._RequestTypeId;
        }
        set
        {
            if ((this._RequestTypeId != value))
            {
                if (this._RequestType.HasLoadedOrAssignedValue)
                {
                    throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();
                }
                this.OnRequestTypeIdChanging(value);
                this.SendPropertyChanging();
                this._RequestTypeId = value;
                this.SendPropertyChanged("RequestTypeId");
                this.OnRequestTypeIdChanged();
            }
        }
    }

    [Column(Storage="_HasParameters", DbType="Bit NOT NULL")]
    public bool HasParameters
    {
        get
        {
            return this._HasParameters;
        }
        set
        {
            if ((this._HasParameters != value))
            {
                this.OnHasParametersChanging(value);
                this.SendPropertyChanging();
                this._HasParameters = value;
                this.SendPropertyChanged("HasParameters");
                this.OnHasParametersChanged();
            }
        }
    }

    [Column(Storage="_Status", DbType="VarChar(50) NOT NULL", CanBeNull=false)]
    public string Status
    {
        get
        {
            return this._Status;
        }
        set
        {
            if ((this._Status != value))
            {
                this.OnStatusChanging(value);
                this.SendPropertyChanging();
                this._Status = value;
                this.SendPropertyChanged("Status");
                this.OnStatusChanged();
            }
        }
    }

    [Column(Storage="_Locked", DbType="Bit NOT NULL")]
    public bool Locked
    {
        get
        {
            return this._Locked;
        }
        set
        {
            if ((this._Locked != value))
            {
                this.OnLockedChanging(value);
                this.SendPropertyChanging();
                this._Locked = value;
                this.SendPropertyChanged("Locked");
                this.OnLockedChanged();
            }
        }
    }

    [Column(Storage="_ErrorMessage", DbType="VarChar(255)")]
    public string ErrorMessage
    {
        get
        {
            return this._ErrorMessage;
        }
        set
        {
            if ((this._ErrorMessage != value))
            {
                this.OnErrorMessageChanging(value);
                this.SendPropertyChanging();
                this._ErrorMessage = value;
                this.SendPropertyChanged("ErrorMessage");
                this.OnErrorMessageChanged();
            }
        }
    }

    [Column(Storage="_ReferenceId", DbType="Int NOT NULL")]
    public int ReferenceId
    {
        get
        {
            return this._ReferenceId;
        }
        set
        {
            if ((this._ReferenceId != value))
            {
                this.OnReferenceIdChanging(value);
                this.SendPropertyChanging();
                this._ReferenceId = value;
                this.SendPropertyChanged("ReferenceId");
                this.OnReferenceIdChanged();
            }
        }
    }

- NullReference

---

请求的类型是什么？如果使用var request会怎样？SingleOrDefault的返回类型是什么？ - Richard Anthony Freeman-Hein

请求反映数据库表Requests中的记录...我尝试使用下面的代码，仍然出现错误... var query = (from r in db.Requests where r.Status.Equals("Processing") && r.Locked.Equals(false) select r).SingleOrDefault();SingleOrDefault的返回类型是Request。 - NullReference

通常出现这种情况是因为 C# 对象上一个或多个属性的类型与列的类型不匹配。您能发布请求表的架构以及请求类属性的类型吗？ - Mike Two

我认为可能会出现将boolean字段转换的问题。我曾经遇到过类似的问题 :) - Aleksej Vasinov

Answer 1

您需要确保表结构与.dbml图表及相关属性匹配。

也许最简单的方法是从dbml图表中删除相应的表。然后，从服务器资源管理器中将其拖回到图表中。

注意：这种方法不一定适用于视图。有时，在重新添加视图到DBML之前，您必须在SSMS中运行exec sp_refreshview MyViewName。

- Brian Webster

---

这种方法对我很有效，谢谢 - 节省了很多时间。一旦我在DBML中放置了新表与我的当前数据库匹配，我就会比较*.dbml文件中的表条目和新表以找到差异。使用Split Diff包的Atom给了我确切的细节。 - Michael12345

Answer 2

你应该写成：.SingleOrDefault<Request>()。

Request request = (
                      from r in db.Requests  
                      where r.Status == "Processing" && r.Locked == false  
                      select r 
                  )
                  .SingleOrDefault<Request>();

- morteza mohammadi

Answer 3

只需从您的 dbml 文件中删除相关表，然后重新添加即可。 如果这样不起作用，请尝试从 dbml 文件中删除关联。

- HasanAboShally