How to join with or condition? Cross Join is creating a IS NULL where clause-CodePudding

Basically I want to create an inner or join. In SQL:

SELECT COUNT(*) FROM Discounts
JOIN DiscountArticles ON Discounts.ID = DiscountArticles.Discount
JOIN Articles ON (DiscountArticles.Article = Articles.ID OR DiscountArticles.ArticleGroup = Articles.ArticleGroup)

This answer says, it's not possible using entity framework, however we can use an cross join, which works fine on SQL:

SELECT COUNT(*) AS GroupCount FROM Discounts
JOIN DiscountArticles ON Discounts.ID = DiscountArticles.Discount
CROSS JOIN Articles
WHERE (Articles.ID = DiscountArticles.Article OR Articles.ArticleGroup = DiscountArticles.ArticleGroup)

So I tried this:

var query = from discounts in _dbContext.Discounts
    join discountArticles in _dbContext.DiscountArticles on discounts.Id equals discountArticles.Discount
    from article in _dbContext.Articles
    where article.Id == discountArticles.Article || article.ArticleGroup.Value == discountArticles.ArticleGroup.Value
    select new
    {
        ArticleId = article.Id,
        DiscountId = discounts.Id
    };

However, this resolves into this this SQL query:

SELECT [a].[ID] AS [ArticleId], [d].[ID] AS [DiscountId]
FROM [Discounts] AS [d]
INNER JOIN [DiscountArticles] AS [d0] ON [d].[ID] = [d0].[Discount]
CROSS JOIN [Articles] AS [a]
WHERE ([a].[ID] = [d0].[Article]) OR (([a].[ArticleGroup] = [d0].[ArticleGroup]) OR (([a].[ArticleGroup] IS NULL) AND ([d0].[ArticleGroup] IS NULL)))

As you can see, there is an addtional check OR (([a].[ArticleGroup] IS NULL) AND ([d0].[ArticleGroup] IS NULL)), which is causing to return 6 time more results.

ArticleGroup is Nullable Guid? on both entities, so I guess it has something to do with it.

If I additional check it for null, I get the correct results:

where article.Id == discountArticles.Article ||
  article.ArticleGroup.HasValue && article.ArticleGroup == discountArticles.ArticleGroup

However, I also get an bigger where clause in SQL:

WHERE ([a].[ID] = [d0].[Article]) OR (([a].[ArticleGroup] IS NOT NULL) AND ([a].[ArticleGroup] = [d0].[ArticleGroup]))

Is it somehow possible to generate a Query, which is more like my second SQL example? Something like this:

WHERE (Articles.ID = DiscountArticles.Article OR Articles.ArticleGroup = DiscountArticles.ArticleGroup)

CodePudding user response：

Try this query, it will create appropriate join. I do not think that you need CROSS JOIN here.

var query = 
    from discounts in _dbContext.Discounts
    join discountArticles in _dbContext.DiscountArticles on discounts.Id equals discountArticles.Discount
    from article in _dbContext.Articles
        .Where(article => article.Id == discountArticles.Article || article.ArticleGroup.Value == discountArticles.ArticleGroup.Value)
    select new
    {
        ArticleId = article.Id,
        DiscountId = discounts.Id
    };

According to null comparison, check this option Using relational null semantics. Bad here that it will affect all queries:

services.AddDbContext<MyDbContext>(options =>
{
    options.UseSqlServer(sourceConnection, sqlOptions =>
    {
        sqlOptions.UseRelationalNulls();
    });
});