我正在嘗試使用帶有 GroupBy 和 Sum 的查詢。首先我用 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();
我收到此錯誤：“InvalidOperationException：'FromSql' 操作的結果中不存在所需的列 'FacturenID'。” “FacturenID”是 Facturens 表中的第一列。直接使用 SQL 查詢可以正常作業。
然后我嘗試了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();
這會導致錯誤：“InvalidOperationException：Nullable 物件必須有一個值。”
事實：
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; }
}
}
uj5u.com熱心網友回復：
當使用 SQL 對模型執行聚合查詢時，結果不會并且通常不能輕易地與原始模型具有相同的結構形式Set<T>.FromSqlRaw()，您使用的方法需要 SQL 決議指定型別的所有屬性T
FromSqlRaw 限制
- SQL 查詢必須回傳物體型別的所有屬性的資料。
- 結果集中的列名必須與屬性映射到的列名匹配。請注意，此行為與 EF6 不同。EF6 忽略了原始 SQL 查詢的屬性到列映射，結果集列名必須與屬性名匹配。
- SQL 查詢不能包含相關資料。但是，在許多情況下，您可以使用 Include 運算子在查詢之上進行組合以回傳相關資料（請參閱包括相關資料）。
對于聚合查詢，我們通常會定義一個新型別來保存來自 SQL 聚合的回應。在 C# LINQ GroupBy 中，GroupBy 的行為與 SQL 非常不同，在 SQL 中，詳細行被排除在外，只回傳聚合集。在 LINQ 中，所有行都被保留，但它們被鍵投影到組中，根本沒有特定的聚合，在 LINQ groupby 之后，您將它們執行您可能需要的任何聚合分析。
我們需要做的第一件事是定義回應的結構，如下所示：
public class FactuurSamenvatting
{
public int? Jaar { get; set; }
public int? Maand { get; set; }
public int? Total { get; set; }
}
那么如果這種型別被注冊為DBContext一個新的DbSet：
/// <summary>Summary of Invoice Totals by Month</summary>
public Set<FactuurSamenvatting> FacturenOmmen { get;set; }
然后，您可以使用這個原始 SQL 查詢：
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();
臨時通用解決方案
盡管鼓勵使用上述解決方案，但無需正式將聚合型別直接添加到 DbContext 即可實作相同的目標。根據@ErikEj 的建議和他在 Github 上的更新參考，我們可以創建一個動態背景關系，其中明確包含任何泛型型別的設定
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);
}
}
}
現在我們根本不需要預先注冊聚合型別，你可以簡單地使用這個語法來執行你的查詢：
然后，您可以使用這個原始 SQL 查詢：
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();
原始回復
Updated after
Factuurmodel 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
Datumarenull, that would invalidateDatum.Value.- this syntax is allowed even if the field is
nullif the expression is being evaluated in SQL, the result will simply benull.
- this syntax is allowed even if the field is
When a SQL result is projected into a c# type, when a value in one of the columns in the result set is
nullbut 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
nullfor both ofjaarandmaand
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/438778.html
上一篇：加入兩個簡單的SQL查詢