1

I can I generate the below SQL query select using entity framework core 3.1.8 ?

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t1.Id = t2.table1Id AND t2.Status IN (1,2,3)

I tried the code below

var statuses = new List<int> { 1, 2, 3 };
var result = await (from t1 in _context.Table1
                    join t2 in _context.Table2
                    on t1.Id equals t2.TableId
                    into results
                    from m in results.Where(x => statuses.Contains((int)x.Status)).DefaultIfEmpty()
                    select new ResultDto
                    {
                    }).ToListAsync();

But the query generates a suquery

UPDATED

The query suggested by @Svyatoslav Danyliv generated the SQL below

SELECT [a].[Id], [a].[ActionWith], [a].[CreatedBy], [a].[DateCreated], [t].[table1Id], [t].[Comment], [t].[CreatedBy], [t].[DateCreated]
FROM [Table1] AS [a]
LEFT JOIN (
    SELECT [c].[Id], [c].[table1Id], [c].[Comment], [c].[CreatedBy], [c].[DateCreated]
    FROM [Table2] AS [c]
    WHERE [c].[Status] IN (1, 2)
) AS [t] ON [a].[Id] = [t].[table1Id]
roroinpho21
  • 732
  • 1
  • 11
  • 27
  • 1
    Yes, you can [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) – Svyatoslav Danyliv Dec 15 '22 at 14:39
  • 1
    This documentation section: [Collection selector references outer in a where clause](https://learn.microsoft.com/en-us/ef/core/querying/complex-query-operators#collection-selector-references-outer-in-a-where-clause) – Svyatoslav Danyliv Dec 15 '22 at 15:00
  • 1
    EF deals with entities and relations, not tables and joins. If you have a `Customer` entity with an `Orders` collection, `ctx.Customers.Include(x=>x.Orders).Where(c=>statues.Contains(c.Status));` will generate `SELECT ... FROM Customer LEFT JOIN Orders where Customer.ID IN (@id1,@id2,..)`. Don't try to write LINQ that mimics SQL – Panagiotis Kanavos Dec 15 '22 at 15:03
  • If you add a `Select` at the end that combines customer and order data, you won't need `Include()`. That's used for eagerly loading related objects, not for JOINing. For example, `ctx.Customers.Where(...).Select(c=>new ResultDto { c.Name, Orders=c.Orders.Select(o=>new OrderDTO{ o.Product,o.Price}).ToList() }).ToList()`. – Panagiotis Kanavos Dec 15 '22 at 15:06
  • @PanagiotisKanavos, you do not need `Include` for filter. – Svyatoslav Danyliv Dec 15 '22 at 15:07
  • BTW .NET Core 3.1 reached End Of Life 2 days ago. This means no more patches, not even security patches. You should consider migrating to .NET 6, the current Long Term Support version. It's supported until 2024 – Panagiotis Kanavos Dec 15 '22 at 15:08
  • @SvyatoslavDanyliv I never said it's needed for `Filter`. I said `you won't need Include(). That's used for eagerly loading related objects,` – Panagiotis Kanavos Dec 15 '22 at 15:08

1 Answers1

1

As documented in Collection selector references outer in a where clause, you can rewrite your query in the following way:

var query =
    from t1 in _context.Table1
    from t2 in _context.Table2
        .Where(t1 => t1.Id == t2.TableId && statuses.Contains((int)x.Status))
        .DefaultIfEmpty()
    select new ResultDto
    {
    }

Note that GroupJoin has limited translation in EF Core, only simple LEFT JOIN.

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32