EF Core 7 aggregate for joined tables

Question

How can I translate these SQL queries into EF Core 7?

I have the following classes (tables, context etc not given for brevity):

    public class Product
    {
        public int ProductId { get; set; }
        public int Score { get; set; }
        public int MarkId { get; set; }

        [ForeignKey("MarkId")]
        public Mark Mark { get; set; } = null!;
    }

    public class Mark
    {
        public int MarkId { get; set; }
        public decimal? Value { get; set; }
        public int SuperMarkId { get; set; }

        [ForeignKey("SuperMarkId ")]
        public SuperMark SuperMark { get; set; } = null!;
    } 

This is the SQL query:

SELECT
    AVG(m.Value), MAX(p.Score)   
FROM
    Product p    
JOIN
    Mark m ON m.MarkId = p.MarkId

What would be the equivalent EF Core query?

Now let's add another class:

    public class SuperMark
    {
        public int SuperMarkId { get; set; }
        public decimal DefaultValue { get; set; }
    }

This is the new SQL query:

SELECT
    AVG(ISNULL(m.Value, sm.DefaultValue)), MAX(p.Score)
FROM
    Product p
JOIN
    Mark m ON m.MarkId = p.MarkId
JOIN 
    SuperMark sm ON sm.SuperMarkId = m.SuperMarkId

What would be the equivalent EF Core query for this SQL?

Answer 1

Since you should be using the Navigation properties and not joins when using EF (Core), you can simplify the queries to just refer to the properties (the comment numbers refer to my SQL Recipe rules):

var ans3 = from p in Products // #2, #3
           group new { p.Mark.Value, p.Score } by 1 into g // #22, #6
           select new { // #18
               Avg = g.Average(vs => vs.Value), // #22
               Max = g.Max(vs => vs.Score) // #22
           };

var ans4 = from p in Products // #2, #3
           group new { Value = p.Mark.Value ?? p.Mark.SuperMark.DefaultValue, p.Score } by 1 into g // #22, #6
           select new { // #18
               Avg = g.Average(vs => vs.Value), // #22
               Max = g.Max(vs => vs.Score) // #22
           };

(NOTE: This is untested with EF Core.)

You could translate the queries more literally using joins, but should not:

var ans = from p in Products // #2, #3
          join m in Marks on p.MarkId equals m.MarkId // #2, #3
          group new { m.Value, p.Score } by 1 into g // #22
          select new { // #18
              Avg = g.Average(vs => vs.Value), // #22
              Max = g.Max(vs => vs.Score) // #22
          };

var ans2 = from p in Products // #2, #3
           join m in Marks on p.MarkId equals m.MarkId // #2, #3
           join sm in SuperMarks on m.SuperMarkId equals sm.SuperMarkId // #2, #3
           group new { Value = m.Value ?? sm.DefaultValue, p.Score } by 1 into g // #22
           select new { // #18
                Avg = g.Average(vs => vs.Value), // #22
                Max = g.Max(vs => vs.Score) // #22
           };