How to join the OR condition?

Question

This is my LINQ code:

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 }; 

And this is the translation of LINQ to Entities:

  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] 

So, it completely ignores the OR condition in the join:

(t.ComponentCategoryCode == null || t.ComponentCategoryCode == b.Component.ComponentCategoryCode)

Can someone tell me why or what am I doing wrong?

UPDATE Here is a simplified version of my model:

 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; } } 

Answer 1

Is it possible to have AUXComponentTypes.ComponentCategoryCode == null? Is this field marked invalid?

Answer 2

Getting SQL from EF LINQ is a multi-step process, so it’s not always easy to see how certain operations are converted. LINQ creates an agnostic expression tree that is passed at run time to EF. Then EF creates a "canonical" tree of query expressions. The reason for this is that EF can use one of the many different ADO AD providers under the scenes, so for now it just gets a common expression tree that can be used in databases. He then passes this “cannatic” query expression to the EF ADO provider, which in turn issues the actual SQL statement.

During one of these passes, your OR condition was “optimized,” and I suspect that this is due to the way LINQ handles joins. In your case, without the actual JOIN clause in the LINQ statement, I suspect that by default it makes an inner join, which technically cannot have one side of the join with NULL (both sides of the join should match in the inner join).

What you really want is left in the OUTER JOIN, where one side is allowed to have NULL. If you request the Internet for LINQ and OUTER JOIN, you will get some examples of how to create a LINQ statement so that one of the parties is allowed to contain NULL.