LINQ to SQL- [英] LINQ to SQL -

问题描述

我正在尝试使用LINQ将记录插入子表中， 收到与"w/ 涉及的关键.堆栈跟踪为:

I'm attempting to use LINQ to insert a record into a child table and I'm receiving a "Specified cast is not valid" error that has something to do w/ the keys involved. The stack trace is:

消息:指定的转换无效.

Message: Specified cast is not valid.

类型:System.InvalidCastException 来源:System.Data.Linq TargetSite: 布尔型 TryCreateKeyFromValues(System.Object []， V ByRef)HelpLink:空堆栈:at System.Data.Linq.IdentityManager.StandardIdentityManager.SingleKeyManager 2.TryCreateKeyFromValues(Object[] values, V& v) at System.Data.Linq.IdentityManager.StandardIdentityManager.IdentityCache 2.Find(Object [] keyValues) System.Data.Linq.IdentityManager.StandardIdentityManager.Find(MetaType 类型，Object [] keyValues)位于 System.Data.Linq.CommonDataServices.GetCachedObject(MetaType 类型，Object [] keyValues)位于 System.Data.Linq.ChangeProcessor.GetOtherItem(MetaAssociation assoc，对象实例)在 System.Data.Linq.ChangeProcessor.BuildEdgeMaps() 在 System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) System.Data.Linq.DataContext.SubmitChanges()

Type: System.InvalidCastException Source: System.Data.Linq TargetSite: Boolean TryCreateKeyFromValues(System.Object[], V ByRef) HelpLink: null Stack: at System.Data.Linq.IdentityManager.StandardIdentityManager.SingleKeyManager2.TryCreateKeyFromValues(Object[] values, V& v) at System.Data.Linq.IdentityManager.StandardIdentityManager.IdentityCache2.Find(Object[] keyValues) at System.Data.Linq.IdentityManager.StandardIdentityManager.Find(MetaType type, Object[] keyValues) at System.Data.Linq.CommonDataServices.GetCachedObject(MetaType type, Object[] keyValues) at System.Data.Linq.ChangeProcessor.GetOtherItem(MetaAssociation assoc, Object instance) at System.Data.Linq.ChangeProcessor.BuildEdgeMaps() at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges()

(.....)

此错误将引发以下代码:

This error is being thrown on the following code:

 ResponseDataContext db = new ResponseDataContext(m_ConnectionString);
 CodebookVersion codebookVersion = db.CodebookVersions.Single(cv => cv.VersionTag == m_CodebookVersionTag);
 ResponseCode rc = new ResponseCode()
    {
       SurveyQuestionName = "Q11",
       Code = 3,
       Description = "Yet another code"
    };
 codebookVersion.ResponseCodes.Add(rc);
 db.SubmitChanges(); //exception gets thrown here

有问题的表在两个表之间具有FK关系.
父表的列称为"id"，是PK，其类型为:INT NOT NULL IDENTITY
子表的列称为"responseCodeTableId"，其类型为:INT NOT NULL.

The tables in question have a FK relationship between the two of them.
The parent table's column is called 'id', is the PK, and is of type: INT NOT NULL IDENTITY
The child table's column is called 'responseCodeTableId' and is of type: INT NOT NULL.

codebookVersion(父类)映射到表tblResponseCodeTable
responseCode(childClass)映射到表tblResponseCode

codebookVersion (parent class) maps to table tblResponseCodeTable
responseCode (childClass) maps to table tblResponseCode

如果我直接执行SQL，它将起作用.例如

If I execute SQL directly, it works. e.g.

INSERT INTO tblResponseCode 
(responseCodeTableId, surveyQuestionName, code, description)
VALUES (13683, 'Q11', 3, 'Yet another code')

对同一个类的更新正常工作.例如

Updates to the same class work properly. e.g.

codebookVersion.ResponseCodes[0].Description = "BlahBlahBlah";
db.SubmitChanges(); //no exception - change is committed to db

我已经在.Add()操作之后检查了变量rc，的确确实收到了正确的responseCodeTableId，正如我期望的那样，因为我将其添加到该集合中.

I've examined the variable, rc, after the .Add() operation and it does, indeed, receive the proper responseCodeTableId, just as I would expect since I'm adding it to that collection.

tblResponseCodeTable's full definition:
COLUMN_NAME TYPE_NAME
id                  int identity
responseCodeTableId int
surveyQuestionName  nvarchar
code                smallint
description         nvarchar
dtCreate            smalldatetime

dtCreate的默认值为GetDate().

dtCreate has a default value of GetDate().

我能想到的唯一有用的信息是没有SQL 曾经针对数据库进行过尝试，因此LINQ在它之前就被炸死了 尝试(因此错误不是SqlException).我已经剖析并验证 不会尝试在数据库上执行任何语句.

The only other bit of useful information that I can think of is that no SQL is ever tried against the database, so LINQ is blowing up before it ever tries (hence the error not being a SqlException). I've profiled and verified that no attempt is made to execute any statements on the database.

当您与非PK字段有关系时，我已经阅读并看到了问题，但这不适合我的情况.

I've read around and seen the problem when you have a relationship to a non PK field, but that doesn't fit my case.

有人能为我阐明这种情况吗?我在这里想念的是什么明显的东西?

Can anyone shed any light on this situation for me? What incredibly obvious thing am I missing here?

非常感谢.
保罗·普雷威特

Many thanks.
Paul Prewett

推荐答案

这是错误?如果是这样，请在Beta发布后尝试在.NET 4.0中运行代码.

Is this an example of this bug? If so, try running your code in .NET 4.0 now that the beta is out.

如果像我一样，您还没有准备好开始使用Beta，则可以解决该问题.问题似乎是LINQ无法正确支持在非主键字段上定义的关系.但是，术语主键"不是指在SQL表上定义的主键，而是指在LINQ设计器中定义的主键.

If, like me, you aren't ready to start using the beta, you may be able to work around the problem. The issue seems to be that LINQ does not properly support relationships defined on non-primary key fields. However, the term "primary key" does not refer to the primary key defined on the SQL table, but the primary key defined in the LINQ designer.

如果将表拖到设计器中，则Visual Studio会自动检查数据库中定义的主键，并将相应的类字段标记为主键".但是，这些不需要彼此对应.您可以删除Visual Studio为您选择的键，然后选择另一个字段(或字段组).当然，您需要确保这是合乎逻辑的(在数据库中您选择的一个或多个字段上应该有唯一的约束.)

If you dragged your tables into the designer, then Visual Studio automatically inspects the primary key defined in the database and marks the corresponding class field(s) as "primary keys". However, these do not need to correspond to each other. You can remove the key Visual Studio chose for you, and pick another field (or group of fields). Of course, you need to make sure this is logical (you should have a unique constraint in the database on the field/fields you choose).

所以我有2个使用替代键彼此相关的表/类.父表具有2个键:代理主键定义为int，备用自然键定义为字符串.在LINQ设计器中，我使用替代键定义了关联，并且每当尝试更新子对象上的关联字段时，都会遇到InvalidCastException. 要解决此问题，我进入了LINQ设计器，选择了int，然后将Primary Key属性从True更改为False.然后，我选择了字符串，并将其主键"属性设置为True.重新编译，重新测试，并且InvalidCastException消失了.

So I had 2 tables/classes related to eachother using an alternative key. The parent table had 2 keys: a surrogate primary key defined as an int, and an alternative natural key defined as a string. In the LINQ designer, I had defined the association using the alternative key, and I experienced the InvalidCastException whenever trying to update that association field on the child object. To work around this, I went into the LINQ designer, selected the int, and then changed the Primary Key property from True to False. Then I chose the string, and set it's Primary Key property to True. Recompiled, retested, and the InvalidCastException is gone.

通过查看屏幕快照，您似乎可以通过将ResponseCode上的LINQ主键从ResponseCode.ID更改为ResponseCode.ResponseCodeTableID来解决问题

Looking at your screen shot it looks like you may be able to fix your issue by changing the LINQ primary key on ResponseCode from ResponseCode.ID to ResponseCode.ResponseCodeTableID

这篇关于LINQ to SQL-的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！