C#•2y ago

❔ Replacing joins to a table with a CTE in EF Core

Say I generate my sql with ef core. Is there a way to make it do joins on the CTE rather than the original table? So for example if I do a

context.Owners
   .Select(o => new
   {
       Id = o.Id,
       Projects = o.Projects,
   });

context.Owners
   .Select(o => new
   {
       Id = o.Id,
       Projects = o.Projects,
   });

Can I make the join with projects use a CTE, so it looks kinda like this:

context.Owners
   .SubstituteJoins(context.Projects, p => p.Where(p => p.Name.Contains(" ")))
   .Select(o => new
   {
       Id = o.Id,
       Projects = o.Projects,
   })

context.Owners
   .SubstituteJoins(context.Projects, p => p.Where(p => p.Name.Contains(" ")))
   .Select(o => new
   {
       Id = o.Id,
       Projects = o.Projects,
   })

Generating a query that's similar to this:

WITH view AS (
  SELECT *
  FROM projects
  WHERE name LIKE '% %'
)
SELECT *
FROM owner
LEFT JOIN view ON view.ownerId = owner.id;

WITH view AS (
  SELECT *
  FROM projects
  WHERE name LIKE '% %'
)
SELECT *
FROM owner
LEFT JOIN view ON view.ownerId = owner.id;

To be clear, what I want is for all joins with the projects table to be substituted for a join to a CTE. Since I think EF Core cannot generate CTE's, is there a way to just force it to replace joins with the Projects table to view, which I would then append to the SQL query as text? I guess an idea is to replace the strings which reference the Projects table after it generates the SQL, but is there a way to do this at one higher level of abstraction? Is my idea flawed, or is there a simpler way, you think? The same can kinda be achieved with nested queries on all nested fields, but my problem with this is that it requires redoing the projections. Thinking of the Projects table as a view feels simpler in this case.

2 Replies

AntonOP•2y ago

I figure it should be possible by hooking into the expression processing / sql generation pipeline.

Accord•2y ago

Looks like nothing has happened here. I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Replacing joins to a table with a CTE in EF Core