我如何加入OR条件
本文关键字:条件 OR 何加入 | 更新日期: 2023-09-27 18:14:54
这是我的LINQ代码:
from b in dbContext.SAPBillOfMaterials
from t in dbContext.AUXComponentTypes
where t.ParentId == b.Parent.Id &&
t.MaterialType == b.Component.MaterialType &&
(t.ComponentCategoryCode == null || t.ComponentCategoryCode == b.Component.ComponentCategoryCode)
select new
{
ComponentCode = b.Component.Model_ComponentCode,
Grid = b.Component.Grid ,
ComponentType = t.ComponentType,
ConfigurationId = configId,
ParentSKUId = b.Parent.Id ,
SKUId = b.Component.Id
};
这是LINQ到实体的翻译:
SELECT
[Extent2].[ParentId] AS [ParentId],
[Extent4].[Model_ComponentCode] AS [Model_ComponentCode],
[Extent4].[Grid] AS [Grid],
[Extent2].[ComponentType] AS [ComponentType],
[Extent1].[Parent_Id] AS [Parent_Id],
[Extent1].[Component_Id] AS [Component_Id]
FROM [dbo].[SAPBillOfMaterial] AS [Extent1]
INNER JOIN [dbo].[AUXComponentTypes] AS [Extent2] ON [Extent1].[Parent_Id] = [Extent2].[ParentId]
INNER JOIN [dbo].[SAPMasterMaterialSKU] AS [Extent3] ON ([Extent2].[MaterialType] = [Extent3].[MaterialType])
AND ([Extent1].[Component_Id] = [Extent3].[Id])
**AND ([Extent2].[ComponentCategoryCode] = [Extent3].[ComponentCategoryCode])**
LEFT OUTER JOIN [dbo].[SAPMasterMaterialSKU] AS [Extent4] ON [Extent1].[Component_Id] = [Extent4].[Id]
因此,它完全忽略了连接中的OR条件:
(t。ComponentCategoryCode == null || t.p onentcategorycode ==b.Component.ComponentCategoryCode)
谁能告诉我为什么或我做错了什么?
下面是我的模型的简化版本:
public class AUXComponentType
{
[Key]
public int Id { get; set; }
[Required, ForeignKey("SAPMasterMaterialSKU")]
public int ParentId { get; set; }
public virtual SAPMasterMaterialSKU SAPMasterMaterialSKU { get; set; }
[Required,StringLength(4)]
public string MaterialType { get; set; }
[Required, StringLength(1)]
public string ComponentType { get; set; }
[Required, StringLength(20)]
public string ComponentCategoryCode { get; set; }
}
public class SAPBillOfMaterial
{
[Key, Column(Order = 1)]
public int Id { get; set; }
[InverseProperty("SAPBOMChilds"), Column(Order = 2)]
public virtual SAPMasterMaterialSKU Parent { get; set; }
[InverseProperty("SAPBOMs"), Column(Order = 3)]
public virtual SAPMasterMaterialSKU Component { get; set; }
public decimal Quantity { get; set; }
}
public class SAPMasterMaterialSKU
{
[Key]
public int Id { get; set; }
[Required,MaxLength(18)]
public string Model_ComponentCode { get; set; }
[MaxLength(8)]
public string Grid { get; set; }
[Required,MaxLength(4)]
public string MaterialType { get; set; }
[Required, MaxLength(20)]
public string ComponentCategoryCode { get; set; }
public virtual ICollection<SAPBillOfMaterial> SAPBOMChilds { get; set; }
public virtual ICollection<SAPBillOfMaterial> SAPBOMs { get; set; }
public virtual ICollection<AUXComponentType> AUXComponentTypes { get; set; }
}
从EF LINQ中获取SQL是一个多步骤的过程,因此并不总是容易看到某些操作在哪里被转换。LINQ创建了一个与语言无关的表达式树,它被传递给EF运行时。然后EF创建一个"规范"查询表达式树。这样做的原因是EF可以在场景下使用许多不同的DB ADO提供程序之一,所以在这一点上,它只是获得一个可以在数据库上使用的通用表达式树。然后,它将这个"规范"查询表达式传递给EF ADO提供程序,后者反过来产生实际的SQL语句。
在其中一次传递期间,您的OR条件被"优化"了,我怀疑这与LINQ处理连接的方式有关。在您的例子中,在LINQ语句中没有实际的JOIN子句,我怀疑这是默认情况下执行的内部连接,从技术上讲,连接的一侧不能为null(连接的两侧必须在内部连接中匹配)。
您真正想要的是左OUTER JOIN,其中一侧允许为null。如果您在internet上查询LINQ和OUTER JOIN,您将获得一些如何创建LINQ语句的示例,以便允许其中一侧包含null。
有可能有一个AUXComponentTypes吗?ComponentCategoryCode == null?该字段是否标记为不可空?