使用与实体集的一对多关系时,指定的强制转换无效
本文关键字:无效 转换 关系 实体 一对多 | 更新日期: 2023-09-27 18:36:51
我正在开发一个面向.net 4的Windows应用程序,该应用程序从多个平台提取订单信息。我需要将数据存储在本地数据库中,并决定使用 LINQ to SQL,目前我的 Order 类有相当数量的代码来处理每个平台发送的数据,所以我一直在使用装饰器向类添加属性,而不是使用 Visual Studio 中的设计器。
它似乎大部分工作,但是当查询数据库以获取订单时,它只能成功返回主订单数据并尝试枚举 Items EntitySet 它会引发"指定的强制转换无效"。 异常。程序将订单正确插入订单表,将订单行/项目插入订单行表,它还将读取订单行表并返回正确填充的订单行对象。
数据库布局 我对链接到 Orders.orderID 的订单行有一个外键约束
类:
[Table(Name = "Orders")]
public class Order
{
[Column(Name="orderID",IsPrimaryKey=true, IsDbGenerated=false)]
public string OrderID;
[Column(Name = "sourceID")]
public int SourceID;
[Column(Name = "statusID")]
public int StatusID = 1;
[Column(Name = "externalOrderID")]
public string ExternalOrderID;
[Column(Name = "deliveryName")]
public string Recipient = "";
[Column(Name = "deliveryCompany")]
public string Company = "";
[Column(Name = "deliveryAddress1")]
public string Address1 = "";
[Column(Name = "deliveryAddress2")]
public string Address2 = "";
[Column(Name = "deliveryAddress3")]
public string Address3 = "";
[Column(Name = "deliveryTown")]
public string Town = "";
[Column(Name = "deliveryCounty")]
public string County = "";
[Column(Name = "deliveryCountry")]
public string Country = "";
[Column(Name = "deliveryCountryISO")]
public string CountryISO = "";
public Country RMCountryData;
[Column(Name = "deliveryPostcode")]
public string Postcode = "";
public Int64 TotalWeight = 0;
public ShippingType ShippingType = ShippingType.Standard;
public ShippingDestination ShippingDestination = ShippingDestination.International;
[Column(Name = "email")]
public string Email = "";
private EntitySet<OrderLine> _Items = new EntitySet<OrderLine>();
[Association(Name = "FK_OrdersLines_Orders", Storage = "_Items")]
public EntitySet<OrderLine> Items
{
set
{
this._Items.Assign(value);
}
get
{
return this._Items;
}
}
public Order() { }
public void Save()
{
OrderDataContext context = new OrderDataContext(String.Format(Settings.Default.ConnectionString, Settings.Default.DBUser, Settings.Default.DBPassword, Settings.Default.DBServer, Settings.Default.DBName));
context.Log = Console.Out;
var query = from o in context.Order where o.OrderID.Equals(this.OrderID) select o;
var line = from l in context.OrderLine where l.OrderID.Equals(this.OrderID) select l;
if (query.Count() == 0)
{
context.Order.InsertOnSubmit(this);
context.SubmitChanges();
}
else
{
foreach (var customerOrder in query)
{
Console.WriteLine(customerOrder.OrderID);
foreach (var orderItem in customerOrder.Items)
{
Console.WriteLine(orderItem.Name);
}
}
}
}
}
[Table(Name = "OrdersLines")]
public class OrderLine
{
[Column(Name = "localLineID", IsPrimaryKey = true, IsDbGenerated = true)]
public int LocalLineID;
[Column(Name = "orderID")]
public string OrderID;
[Column(Name = "lineID")]
public Int64 LineID;
public Int64 ProductID;
[Column(Name = "sku")]
public Int64 SKU;
[Column(Name = "name")]
public string Name;
[Column(Name = "quantity")]
public int Quantity;
[Column(Name = "weight")]
public Int64 Weight;
public OrderLine() { }
public OrderLine(Order Parent, Int64 SKU, Int64 LineID, string Name, int Quantity)
{
this.OrderID = Parent.OrderID;
this.LineID = LineID;
this.ProductID = 0;
this.SKU = SKU;
this.Name = Name;
this.Quantity = Quantity;
this.Weight = 0;
}
}
public partial class OrderDataContext : DataContext
{
public Table<Order> Order;
public Table<OrderLine> OrderLine;
public OrderDataContext(String connString) : base(connString) { }
}
异常在"foreach (var orderItem in customerOrder.Items)"行上引发
堆栈跟踪:
at System.Data.Linq.IdentityManager.StandardIdentityManager.SingleKeyManager`2.TryCreateKeyFromValues(Object[] values, V& v)
at System.Data.Linq.IdentityManager.StandardIdentityManager.IdentityCache`2.Find(Object[] keyValues)
at System.Data.Linq.CommonDataServices.DeferredSourceFactory`1.TryGetCachedObject(Object[] keyValues, T& cached)
at System.Data.Linq.CommonDataServices.DeferredSourceFactory`1.Execute(Object instance)
at System.Data.Linq.EntitySet`1.Load()
at System.Data.Linq.EntitySet`1.GetEnumerator()
at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()
从我在数据库布局中看到的内容(SQL Server Management Studio 的屏幕截图?),"权重"和"lineID"列被定义为"允许空值"。
但是在您发布的OrderLine
类定义中,LineID
和Weight
属性不是Nullable
:
[Table(Name = "OrdersLines")]
public class OrderLine
{
// ...
[Column(Name = "lineID")]
public Int64 LineID;
// ...
[Column(Name = "weight")]
public Int64 Weight;
Linq2SQL 当然希望这些属性是Nullable
的,它可以解释为什么你会得到一个Specified cast is not valid
:赋值可能会失败,因为列数据库类型与 C# 属性类型不匹配。
尝试改用此代码:
[Table(Name = "OrdersLines")]
public class OrderLine
{
// ...
[Column(Name = "lineID")]
public Int64? LineID;
// ...
[Column(Name = "weight")]
public Int64? Weight;
编辑
我还注意到orderId
是nvarchar(30)
的,localLineId
是int
的。在生成连接 SQL 语句时,Linq2SQL 可能会遇到问题,当然值得尝试将它们设置为相同的类型。
最后,这就是问题所在:
[Association(Name = "FK_OrdersLines_Orders", Storage = "_Items")]
应如下所示:
[Association(Name = "FK_OrdersLines_Orders", Storage = "_Items", OtherKey = "OrderID")]