2

I'm trying to write in C# a query with left join using Lambda Expression.

How can i get the wanted SQL query when i have a filter in the joined table.

I mean without subquery, with multiple verifications on 'ON' statement

public class Article
{
     public int Id { get; set; }
     public string ArticleName { get; set; }
     // EF navigation property 
     public ICollection<UserArticleNames> UserArticleNames { get; set; }
}

public class User
{
    public int Id { get; set; }
    public string UserName { get; set; }
}

public class UserArticleName
{
     // FK Users
     public int UserId { get; set; }
     // FK Articles
     public int ArticleId { get; set; }
     public int UserArticleName { get; set; }
}

EF - LINQ query via navigation property: (get Articles for an user)

var join = context.Articles
    .SelectMany(a => a.UserArticleNames
        .Where(ua => ua.UserId == 1)
        .DefaultIfEmpty(), (a, ua) => new {
            Id = a.Id,
            ArticleName = a.ArticleName ,
            UserArticleName = ua.UserArticleName,
        }
    );

Resulting SQL code:

SELECT [a].[Id],
       [a.ArticleName],
       [ua].[UserArticleName]
FROM [dbo].[Articles] AS [a]
LEFT JOIN (
    SELECT [a0].[UserId],
          [a0].[ArticleId],
          [a0].[UserArticleName],
   FROM [dbo].[UserArticleName] AS [a0]
   WHERE [a0].[UserId] = 1
) AS [ua] ON [a].[Id] = [ua].[Id]

Wanted SQL code:

SELECT [a].[Id],
       [a.ArticleName],
       [ua].[UserArticleName]
FROM [dbo].[Articles] AS [a]
LEFT JOIN [dbo].[UserArticleName] AS [ua]
    ON [a].[Id] = [ua].[Id] AND [ua].[UserId] = 1
vernou
  • 6,818
  • 5
  • 30
  • 58
Ruben Birsan
  • 31
  • 3
  • Is this what you're after - https://stackoverflow.com/questions/9500103/converting-a-left-outer-join-to-entity-framework? If so, you could probably close this question as a duplicate. If that's not what you need, perhaps give reasons why not to isolate the issue. HTH. – Adam Benson Oct 21 '20 at 09:20
  • 1
    This is how currently EF Core expands navigations with additional filters. The two SQL queries are equivalent, what's the problem? In general eliminating inline subqueries is not EF Core query translation concern - the sql query optimizer should be able to do that, it's their job. – Ivan Stoev Oct 21 '20 at 11:42
  • I think that if the navigation properties are setup correct, that you can do something like `var join = context.UserArticleNames.Include(x => x.Article)` and get the results you are looking for. I am not sure exactly what the actual SQL would look like, but that would return all of your User Articles, with access to each User Article's Article information. – Cardi DeMonaco Jr Oct 21 '20 at 12:44
  • @cardi-demonaco-jr Your sugested approach will return only the Articles with links in the UserArticleName table. I need all Articles, but with different UserArticleName values based on the userId provided in request – Ruben Birsan Oct 21 '20 at 13:32

0 Answers0