由于的SQLException子查询返回多个值 [英] SqlException because Subquery returned more than 1 value
问题描述
我有我使用构造塞进一个JavaScript网格库，它无关紧要这个例子的结构下面的LINQ查询，但我想我仍然会解释说。
I have the following LINQ query that I am using to construct a structure to stuff into a JavaScript grid library which is irrelevant for this example, but I figured I would still explain that.
var output = myObjects.Select(
p => new RowModel
{
ID = p.LeadUID,
Cells =
new CellCollection(fields,
p.myDataDatas.Where(q => q.myField.ParentUID == null).Select(
q => new CellModel
{
Value = q.Value,
Name = q.myField.Description,
Display = q.myField.Description
}).ToList()
,
new CellModel
{
Name = "Campaign",
Display = "Campaign",
Value = p.Campaign.Name
}
,
new CellModel
{
Name = "CampaignEnabled",
Display = "CampaignEnabled",
Value = p.Campaign.IsActive.ToString()
},
new CellModel
{
Name = "Date Received",
Display = "Date Received",
Value = p.DateAdded.ToString()
}
,
new CellModel
{
Name = "Is Valid",
Display = "Is Valid",
Value = BooleanMap[p.IsValid]
}
,
new CellModel
{
Name = "Invalid Reason",
Display = "Invalid Reason",
Value = p.InvalidReason
}
,
new CellModel
{
Name = "Is Returned",
Display = "Is Returned",
Value = BooleanMap[p.IsReturned]
}
,
new CellModel
{
Name = "Return Reason",
Display = "Return Reason",
Value =
context.MYReturns.SingleOrDefault(
l => l.LeadUID == p.MyUID).ReturnReason
}
,
new CellModel
{
Name = "Workflow",
Display = "Workflow",
Value =
context.Stages.SingleOrDefault(
s => s.LifecycleStageUID == p.LifecycleStageUID).
Name
}
,
new CellModel
{
Name = "WorkflowEnabled",
Display = "WorkflowEnabled",
Value =
context.Stages.SingleOrDefault(
s => s.LifecycleStageUID == p.LifecycleStageUID).
IsActive.ToString()
}
,
new CellModel
{
Name = "Status",
Display = "Status",
Value = p.MyStatus.Name
}
,
new CellModel
{
Name = "StatusDeleted",
Display = "StatusDeleted",
Value = (p.MyStatus.Deleted).ToString()
}
,
new CellModel
{
Name = "LeadSource",
Display = "Lead Source",
Value = MySourcesMap[p.AccountSourceUID].Name
}
,
new CellModel
{
Name = "LeadSourceEnabled",
Display = "LeadSourceEnabled",
Value = AccountSoucesEnabledMap[p.AccountSourceUID].ToString()
}
)
}
);
var rows = output.ToList();
return rows;
我想指望我改变了我的大多数变量名中的代码不会影响大画面。
I would like to expect that my changing the names of most of my variables in the code will not affect the big picture.
这是我面对我的问题是 SOMETIMES 我正在以下的SQLException 信息
My problem that I am facing is SOMETIMES I am getting the following SQLException message:
子查询返回多个值。这当子查询跟随不允许 =，=，<中，<！=>中方式> = 或当子查询用作表达式
Subquery returned more than 1 value. This is not permitted when the subquery follows
=, !=, <, <= , >, >=or when the subquery is used as an expression.
我想知道，是哪里我的查询我做错了什么那会，有时（大部分时间）的工作，然后很少返回该错误消息。我怎样才能正确地防止这种情况发生？
What I am wondering, is where in my query am I doing something wrong that would sometimes(most of the time) work, and then very rarely returns this error message. How can I correctly prevent this from happening?
推荐答案
使用的 DataContext.Log 属性来显示您的查询生成的SQL。你最有可能有一个子查询时生成多个结果时，只有一个结果是有效的。例如下面的SQL将失败，如果超过一个结果返回的子查询：
use the DataContext.Log property to display the sql generated by your query. You most likely have a sub-query that is generating more than one result when only one result is valid. For example the following sql will fail if more than one result is returned in the sub-query:
Select * from orders where customer_id =
(select customer_id from customer where name ='bob')
的平等其中，在主查询子句是没有意义的，如果有从子查询返回多个结果。
The equality of the where clause in the main query makes no sense if there is more than one result returned from the sub-query.
您可能需要更改数据的一些列的唯一性在存储，以确保只有一排在子查询返回。另一种方法是改变你的类，以使特定问题的属性被分配给是一个集合而不是单个值
You may need to alter the uniqueness of some columns of data in your storage in order to ensure that only one row is returned in the sub-query. Another alternative is to alter your class so that the specific problem property being assigned to is a collection instead of a single value.
这篇关于由于的SQLException子查询返回多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
