CODEWITHSUNDEEP
c#entity-frameworkjoinlinq-to-entitiesef-code-first
Cristian Grisolia
Cristian Grisolia

Reputation: 157

How do I join with OR condition?

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 LINQ to Entities translation:

    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's totally ignoring the OR condition in the join:

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

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

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

}

Upvotes: 4

Views: 121

Answers (2)

Robert McKee
Robert McKee

Reputation: 21487

Is it ever possible to have a AUXComponentTypes.ComponentCategoryCode == null? Is that field marked as not nullable?

Upvotes: 1

DevPrime
DevPrime

Reputation: 911

Getting SQL out of EF LINQ is a multi-step process, so it's not always easy to see where certain operations are getting converted. LINQ creates a language-agnostic expression tree, which gets passed to the EF runtime. EF then creates a "cannonical" query expression tree. The reason for this is that EF can use one of many different DB ADO providers under the scenes, so at this point, it's just getting a generic tree of expressions that can be used on databases. It then passes this "cannonical" query expression to the EF ADO Provider, which in turn produces the actual SQL statement.

During one of these passes, your OR condition was "optimized" away, and I suspect it has to do with how LINQ handles joins. In your case, without having an actual JOIN clause in the LINQ statement, I suspect it's by default doing an inner join, which technically cannot have one side of the join with NULLs (both sides of the join must match in an inner join).

What you really want is left OUTER JOIN, where one side is allowed to have NULLs. If you query the internet for LINQ and OUTER JOIN, you'll get some examples of how to create the LINQ statement such that one of the sides is allowed to contain NULLs.

Upvotes: 2

Related Questions