Using FromSqlRaw or Linq query to Group and Sum data-CodePudding

I am trying to use a query with GroupBy and Sum. First I tried it with SQL:

          string query = $"SELECT Year(Datum) AS y, Month(Datum) AS m, SUM(Bedrag) AS Total FROM Facturens GROUP BY Year(Datum), Month(Datum) ORDER BY y, m";
          Grafiek = await _db.Facturens.FromSqlRaw(query).ToListAsync();

I get this error: "InvalidOperationException: The required column 'FacturenID' was not present in the results of a 'FromSql' operation." "FacturenID" is the first column in the Facturens table. The SQL query works fine when used directly.

I then tried Linq:

        Grafiek = (IEnumerable<Factuur>)await _db.Facturens
         .GroupBy(a => new { a.Datum.Value.Year, a.Datum.Value.Month }, (key, group) => new
           {
               jaar = key.Year,
               maand = key.Month,
               Total = group.Sum(b => b.Bedrag)
            })
        .Select(c => new { c.jaar, c.maand, c.Total })
        .ToListAsync();

This results in error: "InvalidOperationException: Nullable object must have a value."

Factuur:

using System.ComponentModel.DataAnnotations;

namespace StallingRazor.Model
{
    public class Factuur
    {
        [Key]
        public int FacturenID { get; set; }
        public int EigenarenID { get; set; }

        [Display(Name = "Factuurdatum")]
        [DataType(DataType.Date)]
        [DisplayFormat(NullDisplayText = "")]
        public DateTime? Datum { get; set; }
        public decimal? Bedrag { get; set; }
        public decimal? BTW { get; set; }
        [DataType(DataType.Date)]
        [DisplayFormat(NullDisplayText = "")]
        public DateTime? Betaaldatum { get; set; }
        [Display(Name = "Betaald bedrag")]
        public decimal? Betaald_bedrag { get; set; } 
        [Display(Name = "Totaal bedrag")]
        public decimal? Totaal_bedrag { get; set; }   
        public int ObjectenID { get; set; }    
        [DataType(DataType.Date)]
        public DateTime? Verzonden { get; set; }    
        public string? Mededeling { get; set; }
        [Display(Name = "Begindatum")]
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{dd-MM-yyyy}", NullDisplayText = "")]
        public DateTime? Begin_datum { get; set; }
        [Display(Name = "Einddatum")]
        [DataType(DataType.Date)]
        [DisplayFormat(NullDisplayText = "")]
        public DateTime? Eind_datum { get; set; }

    }
}

CodePudding user response：

When performing aggregate queries against a model using SQL, the result will not and in general cannot easily be the same structural form as the original model, the Set<T>.FromSqlRaw() method that you are using requires the SQL to resolve ALL of the properties for the specified type of T

FromSqlRaw Limitations

The SQL query must return data for all properties of the entity type.

The column names in the result set must match the column names that properties are mapped to. Note this behavior is different from EF6. EF6 ignored property to column mapping for raw SQL queries and result set column names had to match the property names.

The SQL query can't contain related data. However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

For aggregate queries, we would generally define a new type to hold the response from the SQL aggregate. In C# LINQ GroupBy behaves very differently to SQL, in SQL the detail rows are excluded and only the aggregate set is returned. In LINQ all of the rows are retained, but they are projected into groups by the key, there is no specific aggregation at all, after a LINQ groupby you would them perform any aggregate analysis you may require.

The first thing we need to do is define the structure of the response, something like this:

public class FactuurSamenvatting
{
    public int? Jaar { get; set; }
    public int? Maand { get; set; } 
    public int? Total { get; set; }
}

Then if this type is registered with the DBContext as a new DbSet:

/// <summary>Summary of Invoice Totals by Month</summary>
public Set<FactuurSamenvatting> FacturenOmmen { get;set; }

You can then use this raw SQL query:

string query = $"SELECT Year(Datum) AS Jaar, Month(Datum) AS Maand, SUM(Bedrag) AS Total FROM Facturens GROUP BY Year(Datum), Month(Datum) ORDER BY Jaar, Maand";
var grafiek = await _db.FacturenOmmen.FromSqlRaw(query).ToListAsync();

Ad-Hoc Generic Solution

Though the above solution is encouraged, it is possible to achieve the same thing without formally adding your aggregate type directly to your DbContext. Following this advice from @ErikEj and his updated reference on Github we can create a dynamic context that explicitly contains the setup for any generic type

public static class SqlQueryExtensions
{
    public static IList<T> SqlQuery<T>(this DbContext db, string sql, params object[] parameters) where T : class
    {
        using (var db2 = new ContextForQueryType<T>(db.Database.GetDbConnection()))
        {
            return db2.Set<T>().FromSqlRaw(sql, parameters).ToList();
        }
    }

    private class ContextForQueryType<T> : DbContext where T : class
    {
        private readonly DbConnection connection;

        public ContextForQueryType(DbConnection connection)
        {
            this.connection = connection;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(connection, options => options.EnableRetryOnFailure());

            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<T>().HasNoKey();
            base.OnModelCreating(modelBuilder);
        }
    }
}

Now we do not need to pre-register the aggregate types at all, you can simply use this syntax to execute you query:

You can then use this raw SQL query:

string query = $"SELECT Year(Datum) AS Jaar, Month(Datum) AS Maand, SUM(Bedrag) AS Total FROM Facturens GROUP BY Year(Datum), Month(Datum) ORDER BY Jaar, Maand";
var grafiek = _db.SqlQuery<FactuurSamenvatting>(query).ToList();

Original Response

Updated after Factuur model posted
Below is a general walk through responding to the original post and the specific exceptions that were raised. I had originally assumed that OP was using an aggregate type definition, I had forgotten that to do so is itself an advanced technique, the following response is still helpful if you define your aggregate type correctly but still observe the same exceptions.

LINQ expressions in general that project into a known type will throw two common errors:

InvalidOperationException: The required column 'FacturenID' was not present...

This error is reasonably obvious, the model Factuur that you are projecting into has a required column called FacturenID, which your output does not provide. Your projection in the first attempt is expecting these columns in Factuur:

public int y { get;set; }
public int m { get;set; }
public int? Total { get;set; }

If you change the first query to use the matching property names of those existing in Factuur then you will most likekly still encounter the next issue...

The error InvalidOperationException: Nullable object must have a value. is experienced in two situations:

When your LINQ expression is operating in memory and tries to access a property on an object that is null, most likely in the case of the second query this can occur if any values of Datum are null, that would invalidate Datum.Value.
- this syntax is allowed even if the field is null if the expression is being evaluated in SQL, the result will simply be null.
When a SQL result is projected into a c# type, when a value in one of the columns in the result set is null but the corresponding property of the type you are projecting into does not allow for nulls.

In this case one of the jaar,maand,Total columns needs to be null, usually it will be the result of the SUM aggregate but in this case that can only happen if Bedrag is nullable in your dataset.

Test your data by inspecting this recordset, notice that I am NOT casting the results to a specific type, we will leave them in the anonymous type form for this analysis, also we will exclude null datums. for this test.

var data = await _db.Facturens
                    .Where (f => f.Datum != null)
                    .GroupBy(a => new { a.Datum.Value.Year, a.Datum.Value.Month }, (key, group) => new
                        {
                            jaar = key.Year,
                            maand = key.Month,
                            Total = group.Sum(b => b.Bedrag)
                        })
                   .Select(c => new { c.jaar, c.maand, c.Total })
                   .ToListAsync();

In your original query, to account for the nulls and return zero for the Total instead of altering your model to accept nulls, then you could use this:

string query = $"SELECT Year(Datum) AS jaar, Month(Datum) AS maand, SUM(ISNULL(Bedrag,0)) AS Total FROM Facturens GROUP BY Year(Datum), Month(Datum) ORDER BY jaar, maand";
      Grafiek = await _db.Facturens.FromSqlRaw(query).ToListAsync();

In this SQL we didn't need to exclude the null datums, these will be returned with respctive values of null for both of jaar and maand

Given that the only case where jaar and maand might be null is if the column Datum has a null value so you could use this SQL to return the same columns as the expected type without modifying the model, as long as these were all the columns in the model. In this case I would recommend excluding those records from the results with a simple WHERE clause

SELECT 
     Year(Datum) AS jaar
   , Month(Datum) AS maand
   , SUM(ISNULL(Bedrag,0)) AS Total 
FROM Facturens 
WHERE Datum IS NOT NULL
GROUP BY Year(Datum), Month(Datum) ORDER BY jaar, maand