locked
Generating Unwanted OR conditions RRS feed

  • Question

  • User-221457683 posted

    Please help me out to remove OR while generating SQL Query from Linq in Entity Framework 6.2.0

    Example:

    Linq:  on p.PRODUCT_ID equals pd.PRODUCT_ID

    SQL:  on [Extent1].[PRODUCT_ID] = [Extent4].[PRODUCT_ID] OR (([Extent1].[PRODUCT_ID] IS NULL) AND ([Extent4].[PRODUCT_ID] IS NULL))

    Monday, October 1, 2018 2:00 PM

All replies

  • User475983607 posted

    Update PRODUCT_ID so that it is a primary key.   I assume  PRODUCT_ID accepts null which causes the SQL.

    Monday, October 1, 2018 3:04 PM
  • User1724605321 posted

    Hi kumar.guduri,

    You can Set UseDatabaseNullSemantics = true :

    Gets or sets a value indicating whether database null semantics are exhibited when comparing two operands, both of which are potentially nullable. The default value is false. For example (operand1 == operand2) will be translated as: (operand1 = operand2) if UseDatabaseNullSemantics is true, respectively (((operand1 = operand2) AND (NOT (operand1 IS NULL OR operand2 IS NULL))) OR ((operand1 IS NULL) AND (operand2 IS NULL))) if UseDatabaseNullSemantics is false.

    Reference  : 

    https://msdn.microsoft.com/en-us/library/system.data.entity.infrastructure.dbcontextconfiguration.usedatabasenullsemantics(v=vs.113).aspx

    https://stackoverflow.com/questions/38433594/why-is-ef-generating-sql-queries-with-unnecessary-null-checks

    Best Regards,

    Nan Yu

    Tuesday, October 2, 2018 3:07 AM
  • User-221457683 posted
    Still it is genearating OR CONDITIONS on joining tables even if I set "UseDatabaseNullSemantics = true"
    Tuesday, October 2, 2018 9:39 AM
  • User-221457683 posted
    When comparing nullable PRODUCT_ID with primary PRODUCT_ID we are casting PRODUCT_ID to non nullable like (long)PRODUCT_ID
    Tuesday, October 2, 2018 9:46 AM
  • User475983607 posted
    As stated, fix the dB table design. Product ID should not allow null.
    Tuesday, October 2, 2018 10:27 AM
  • User-221457683 posted
    it is not mandatory we are joining tables using left jion
    Tuesday, October 2, 2018 11:18 AM
  • User475983607 posted

    it is not mandatory we are joining tables using left jion

    The JOIN requirement has nothing to do with proper table design. 

    I don't see any indication in the code or your posts that you are building a left join.  It is always best to describe what you are trying to do rather than how you think the problem should be solved.  

    Linq Left join examples.

    https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins

    Tuesday, October 2, 2018 11:34 AM
  • User-221457683 posted

    Thanks for your information

    but we have PRODUCT(master) and PRODUCT_DETAILS(Transaction) tables. PRODUCT_DETAILS Table may have PRODUCT_CODE(master Table Column) or not. when we are are joining both tables with PRODUCT_CODE using left Join in Linq, it is generating OR Conditions in SQL.

    Linq: 

    from u in _dbContext.PRODUCT.AsNoTracking() join pc in _dbContext.PRODUCT_DETAILS.AsNoTracking() on new { a = uc.PRODUCT_CODE, b = (int)uc.PRODUCT_TYPE_ID } equals new { a = pc.PRODUCT_CODE, b = 1 } into pctemp
    from pctm in pctemp.DefaultIfEmpty()

    SQL:

    FROM [dbo].[PRODUCT] AS [Extent1]
    LEFT OUTER JOIN [dbo].[PRODUCT_DETAILS] AS [Extent4] ON (([Extent2].[PRODUCT_CODE] = [Extent4].[PRODUCT_CODE])
     OR (([Extent2].[PRODUCT_CODE] IS NULL) AND ([Extent4].[PRODUCT_CODE] IS NULL))) AND (([Extent2].[PRODUCT_TYPE_ID] = 1)
     OR (([Extent2].[PRODUCT_TYPE_ID] IS NULL) AND (1 IS NULL))) 
    Thursday, October 4, 2018 2:00 PM
  • User475983607 posted

    but we have PRODUCT(master) and PRODUCT_DETAILS(Transaction) tables. PRODUCT_DETAILS Table may have PRODUCT_CODE(master Table Column) or not. when we are are joining both tables with PRODUCT_CODE using left Join in Linq, it is generating OR Conditions in SQL.

    Correct

    Thursday, October 4, 2018 2:02 PM
  • User-221457683 posted

    how can we remove OR Conditions which are getting generated in SQL Query?

    FROM [dbo].[PRODUCT] AS [Extent1] LEFT OUTER JOIN [dbo].[PRODUCT_DETAILS] AS [Extent4] ON (([Extent2].[PRODUCT_CODE] = [Extent4].[PRODUCT_CODE]) OR (([Extent2].[PRODUCT_CODE] IS NULL) AND ([Extent4].[PRODUCT_CODE] IS NULL))) AND (([Extent2].[PRODUCT_TYPE_ID] = 1) OR (([Extent2].[PRODUCT_TYPE_ID] IS NULL) AND (1 IS NULL))

    Friday, October 5, 2018 5:38 AM