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?

@SvyatoslavDanyliv The usual foreign keys. I didn't write them for brevity. Consider adding/changing whatever you want if that helps — user628661, Jun 27 '23 at 18:28
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jun 27 '23 at 18:37
@NetMage Thanks. I see two big differences between that query and mine: it uses GroupBy and it returns a collection. My SQL returns only one row. I can't figure out how to apply your recipe to my query — user628661, Jun 27 '23 at 18:51

score 1 · Answer 1 · answered Jun 27 '23 at 19:48

You use join and Group by (It is always 1 ,.GroupBy(i => 1)) Considering the difference between linq and Tsql, I tried to generate similar code. But I have to use groupby(because grouping needed) that The grouping is not based on the fields linq

var query = context.Products.Join(context.Marks,
                    m => m.MarkId,
                    p => p.MarkId,
                    (m, p) => new  // result selector
                    {
                        product = m,
                        Marks = p
                    }).GroupBy(i => 1).Select(d => new
                    {
                        AVG = d.Average(d => d.Marks.Value)

                        ,
                        max = d.Max(d => d.product.Score)
                    }).ToList();

i qut query with Profiler

SELECT AVG([t].[Value]) AS [AVG], MAX([t].[Score]) AS [max]
FROM (
    SELECT [p].[Score], [m].[Value], 1 AS [Key]
    FROM [Products] AS [p]
    INNER JOIN [Marks] AS [m] ON [p].[MarkId] = [m].[MarkId]
) AS [t]
GROUP BY [t].[Key]

2.Query 2(three tables) linq

 var query2 = context.Products.Join(context.Marks,
                 m => m.MarkId,
                 p => p.MarkId,
                 (m, p) => new  // result selector
                 {
                     product = m,
                     Marks = p
                 }).Join(context.SuperMarks,
                 m => m.Marks.SuperMarkId,
                 p => p.SuperMarkId,
                 (m, p) => new  // result selector
                 {
                     product = m.product,
                     Marks = m.Marks,
                     SuperMarks = p


                 }).GroupBy(i => 1).Select(d => new
                 {
                     AVG = d.Average(d => d.Marks.Value.HasValue ? d.Marks.Value : d.SuperMarks.DefaultValue)
                     ,max = d.Max(d => d.product.Score)
                 }).ToList();

i qut query with Profiler

SELECT AVG(CASE
    WHEN [t].[Value] IS NOT NULL THEN [t].[Value]
    ELSE [t].[DefaultValue]
END) AS [AVG], MAX([t].[Score]) AS [max]
FROM (
    SELECT [p].[Score], [m].[Value], [s].[DefaultValue], 1 AS [Key]
    FROM [Products] AS [p]
    INNER JOIN [Marks] AS [m] ON [p].[MarkId] = [m].[MarkId]
    INNER JOIN [SuperMarks] AS [s] ON [m].[SuperMarkId] = [s].[SuperMarkId]
) AS [t]
GROUP BY [t].[Key]

Wow. That's much much more complicated that I thought. I will test it later and come back to mark it as an answer. Thank a lot! — user628661, Jun 27 '23 at 20:10

score 1 · Accepted Answer · answered Jun 27 '23 at 20:39

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
           };

That's it. Big bonus for using query syntax which I'm much more familiar. Thanks a lot — user628661, Jun 27 '23 at 20:53

EF Core 7 aggregate for joined tables

2 Answers2