Entity Framework Core3.1ストアドプロシージャのオプションのパラメーターが機能しない
[解決済み](最後を参照)
私は一日のほとんどを運がなくてもうまくいく解決策を探して過ごしました。
- EFコア3.1
- Visual Studio 2019
- SQL Server 2017
- .Net Core 3.1
オプションのパラメーターを使用して.NetCore3.1からストアドプロシージャを呼び出すために機能するソリューションを見つけることができませんでした。SOからブログまで何十もの記事を読んだことがありますが、何も機能しません。
これは、これを機能させるためのすべての試みでストアドプロシージャを呼び出すメソッドのC#コードです。
public List<BudgetWorkflowStatusByDepartment> GetBudgetWorkflowStatusByDepartment(int? companyId, int? departmentId, int? locationId, int? sublocationId)
{
//var compId = new SqlParameter("@CompanyId", companyId);
//compId.Value = (object)companyId ?? SqlInt32.Null;
//var deptId = new SqlParameter("@DepartmentId", departmentId);
//deptId.Value = (object)departmentId ?? SqlInt32.Null;
//var locId = new SqlParameter("@LocationId", locationId);
//locId.Value = (object)locationId ?? SqlInt32.Null;
//var subId = new SqlParameter("@SubLocationId", sublocationId);
//subId.Value = (object)sublocationId ?? SqlInt32.Null;
var compId = new SqlParameter("@CompanyID", companyId);
compId.Value = (object)companyId ?? DBNull.Value;
var deptId = new SqlParameter("@DepartmentID", departmentId);
deptId.Value = (object)departmentId ?? DBNull.Value;
var locId = new SqlParameter("@LocationID", locationId);
locId.Value = (object)locationId ?? DBNull.Value;
var subId = new SqlParameter("@SubLocationID", sublocationId);
subId.Value = (object)sublocationId ?? DBNull.Value;
//var result = context.BudgetWorkflowStatusByDepartment
// .FromSqlRaw("EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID, @DepartmentID, @LocationID, @SubLocationID", compId, deptId, locId, subId).ToList();
//var result = context.BudgetWorkflowStatusByDepartment
// .FromSqlRaw("EXEC mis.BudgetWorkflowStatusByDepartment {0}, {1}, {2}, {3}", companyId, departmentId, locationId, companyId).ToList();
// Error Must declare the scalar variable "@CompanyID".
//var result = context.BudgetWorkflowStatusByDepartment
// .FromSqlRaw($"EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID={compId}, @DepartmentID={deptId}, @LocationID={locId}, @SubLocationID={subId}").ToList(); // Error Must declare the scalar variable "@CompanyID". var result = context.BudgetWorkflowStatusByDepartment .FromSqlRaw($"EXEC mis.BudgetWorkflowStatusByDepartment {compId}, {deptId}, {locId}, {subId}").ToList();
//var result = context.BudgetWorkflowStatusByDepartment
// .FromSqlInterpolated($"EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID={compId}, @DepartmentID={deptId}, @LocationID={locId}, @SubLocationID={subId}").ToList();
return result;
}
これらの試みはどれも機能しません。エラーの範囲は次のとおりです。
データがNullです。このメソッドまたはプロパティは、Null値では呼び出すことができません。
...から..。
スカラー変数「@compId」を宣言する必要があります。
...それを機能させるための必死の試みでおそらく間違っている何かを試したときの他のいくつかのエラーに。
これは、すべてのパラメーターがNULL可能であることを示すストアード・プロシージャー自体の署名です。
ALTER PROCEDURE [mis].[BudgetWorkflowStatusByDepartment]
(@CompanyID int = null,
@DepartmentID int = null,
@LocationID int = null,
@SubLocationID int = null)
苛立たしい部分は、このストアドプロシージャが、SQL Serverで、パラメータの有無にかかわらずLinqPadで機能することです。そして、それはすべてのパラメーターでのみ私のコードで機能しますが、いずれかがnullの場合、エラーをスローします。
関数をオプションのパラメーターで動作させる方法についての洞察をいただければ幸いです。
編集:2020年8月24日
新しい提案に基づいて、私はそれらを試しましたが、何らかの理由でまだ機能しません。
4つのパラメーターすべてを使用してSSMSでsprocを直接テストしましたが、機能します。次に、最後のパラメーターを1つずつ削除し、それぞれが削除されて正しいデータを返すようにテストしました。
Apiとこのメソッドのサービス呼び出しを使用して同じテストを実行すると、4つのパラメーターが機能し、3つのパラメーターが機能しますが、2つのパラメーター、1つのパラメーター、およびパラメーターなしは機能しません。なぜだかわかりません。
これが私の新しい試みです:
/// New attempts as of: 8/24/2020
// Error Data is Null. This method or property cannot be called on Null values.
//var compId = new SqlParameter("@CompanyID", companyId);
//compId.Value = (object)companyId ?? DBNull.Value;
//var deptId = new SqlParameter("@DepartmentID", departmentId);
//deptId.Value = (object)departmentId ?? DBNull.Value;
//var locId = new SqlParameter("@LocationID", locationId);
//locId.Value = (object)locationId ?? DBNull.Value;
//var subId = new SqlParameter("@SubLocationID", sublocationId);
//subId.Value = (object)sublocationId ?? DBNull.Value;
//var result = context.BudgetWorkflowStatusByDepartment
// .FromSqlInterpolated($"EXEC mis.BudgetWorkflowStatusByDepartment {compId}, {deptId}, {locId}, {subId}").ToList();
// Error Data is Null. This method or property cannot be called on Null values.
//var result = context.BudgetWorkflowStatusByDepartment
// .FromSqlInterpolated($"EXEC mis.BudgetWorkflowStatusByDepartment {companyId}, {departmentId}, {locationId}, {sublocationId}").ToList();
//var compId = new SqlParameter("@CompanyID", companyId) { IsNullable = true };
//compId.Value = (object)companyId ?? DBNull.Value;
//var deptId = new SqlParameter("@DepartmentID", departmentId) { IsNullable = true };
//deptId.Value = (object)departmentId ?? DBNull.Value;
//var locId = new SqlParameter("@LocationID", locationId) { IsNullable = true };
//locId.Value = (object)locationId ?? DBNull.Value;
//var subId = new SqlParameter("@SubLocationID", sublocationId) { IsNullable = true };
//subId.Value = (object)sublocationId ?? DBNull.Value;
//// Error Data is Null. This method or property cannot be called on Null values.
//var result = context.BudgetWorkflowStatusByDepartment
// .FromSqlRaw("EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID = @CompanyID, @DepartmentID = @DepartmentID, @LocationID = @LocationID, @SubLocationID = @SubLocationID", compId, deptId, locId, subId)
// .ToList();
var compId = new SqlParameter("@CompanyID", companyId) { IsNullable = true };
compId.Value = (object)companyId ?? DBNull.Value;
var deptId = new SqlParameter("@DepartmentID", departmentId) { IsNullable = true };
deptId.Value = (object)departmentId ?? DBNull.Value;
var locId = new SqlParameter("@LocationID", locationId) { IsNullable = true };
locId.Value = (object)locationId ?? DBNull.Value;
var subId = new SqlParameter("@SubLocationID", sublocationId) { IsNullable = true };
subId.Value = (object)sublocationId ?? DBNull.Value;
// Error Data is Null. This method or property cannot be called on Null values.
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlRaw("EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID, @DepartmentID, @LocationID, @SubLocationID", compId, deptId, locId, subId)
.ToList();
このsprocのDbContextプロパティ設定を追加情報として含める必要があると思いました。これと同じパターンが他の2つのsprocに使用され、それらは機能しますが、オプションのパラメーターは必要ありません。
public DbSet<BudgetWorkflowStatusByDepartment> BudgetWorkflowStatusByDepartment { get; set; }
そして、OnModelCreating()メソッドでメソッドを設定しましたHasNoKey()
。
modelBuilder.Entity<BudgetWorkflowStatusByDepartment>().HasNoKey();
皆様のご提案に感謝いたします。
これに対する私の回避策は、DBAにsprocを変更してパラメーターをすべて一緒に削除するように指示すること.Where()
であり、結果に対して句を実行するだけです。
幸いなことに、この特定の機能では200行を超えることはありませんが、多くの列が返されますが、それでも負担になることはありません。
解決した理由
私の責任です。int
null許容型のintである必要があるいくつかのプロパティがありました。私がそれをしたら、それはうまくいきました、そしてそれはほとんどの解決策でうまくいくようです。
nullになる可能性のあるプロパティに注意を払い、null許容にすることが重要です。そうすれば、解決に1日かかった私の問題の餌食にならないようにできます。
以前に行ったことのないこと(.Net Core 3.1ストアドプロシージャでオプションのパラメータを使用)でこれらすべての試みを行っていたので、これを処理するためのさまざまなオプションを使用して結果を文書化すると思いました。
これらは、.Net Core3.1からストアドプロシージャを実行するときに機能するものと機能しないものに関するいくつかのオプションです。
これらの例では、これが.NetCoreメソッドの署名になります。
public List<BudgetWorkflowStatusByDepartment> GetBudgetWorkflowStatusByDepartment
(int? companyId, int? departmentId, int? locationId, int? sublocationId)
{
...
}
すべてのパラメーターはオプションです。次の例は、このメソッドで使用できるコードです。
これは、ストアドプロシージャの署名です。
ALTER procedure mis.BudgetWorkflowStatusByDepartment_Filtered (
@CompanyID int = null
, @DepartmentID int = null
, @LocationID int = null
, @SubLocationID int = null
)
実例
次の例は、FromSqlRaw
またはFromSqlInterpolated
メソッドを使用してストアドプロシージャを実行する方法を示しています。
FromSqlRawメソッド
この例では、単にSqlParameter
オブジェクトを作成し、値を入力値またはに設定しますDbNull
。このnull
場合は機能しないため、これは重要です。(以下の非稼働例を参照してください)
// Works
var compId = new SqlParameter("@CompanyID", companyId);
compId.Value = (object)companyId ?? DBNull.Value;
var deptId = new SqlParameter("@DepartmentID", departmentId);
deptId.Value = (object)departmentId ?? DBNull.Value;
var locId = new SqlParameter("@LocationID", locationId);
locId.Value = (object)locationId ?? DBNull.Value;
var subId = new SqlParameter("@SubLocationID", sublocationId);
subId.Value = (object)sublocationId ?? DBNull.Value;
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlRaw("EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID, @DepartmentID, @LocationID, @SubLocationID", compId, deptId, locId, subId).ToList();
以下IsNullable=true
は、SqlParameter
オブジェクトのプロパティを含めることで機能します。
// Works
var compId = new SqlParameter("@CompanyID", companyId) { IsNullable = true };
compId.Value = (object)companyId ?? DBNull.Value;
var deptId = new SqlParameter("@DepartmentID", departmentId) { IsNullable = true };
deptId.Value = (object)departmentId ?? DBNull.Value;
var locId = new SqlParameter("@LocationID", locationId) { IsNullable = true };
locId.Value = (object)locationId ?? DBNull.Value;
var subId = new SqlParameter("@SubLocationID", sublocationId) { IsNullable = true };
subId.Value = (object)sublocationId ?? DBNull.Value;
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlRaw("EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID = @CompanyID, @DepartmentID = @DepartmentID, @LocationID = @LocationID, @SubLocationID = @SubLocationID", compId, deptId, locId, subId)
.ToList();
以下は上記と似ていますが、構文が少し簡略化されています。
// Works
var compId = new SqlParameter("@CompanyID", companyId) { IsNullable = true };
compId.Value = (object)companyId ?? DBNull.Value;
var deptId = new SqlParameter("@DepartmentID", departmentId) { IsNullable = true };
deptId.Value = (object)departmentId ?? DBNull.Value;
var locId = new SqlParameter("@LocationID", locationId) { IsNullable = true };
locId.Value = (object)locationId ?? DBNull.Value;
var subId = new SqlParameter("@SubLocationID", sublocationId) { IsNullable = true };
subId.Value = (object)sublocationId ?? DBNull.Value;
// Works
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlRaw("EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID, @DepartmentID, @LocationID, @SubLocationID", compId, deptId, locId, subId)
.ToList();
FromSqlInterpolated
以下は、.FromSqlInterpolated()
メソッドを使用し、プレースホルダー構文が機能するため、機能します。
// Works
var compId = new SqlParameter("@CompanyID", companyId);
compId.Value = (object)companyId ?? DBNull.Value;
var deptId = new SqlParameter("@DepartmentID", departmentId);
deptId.Value = (object)departmentId ?? DBNull.Value;
var locId = new SqlParameter("@LocationID", locationId);
locId.Value = (object)locationId ?? DBNull.Value;
var subId = new SqlParameter("@SubLocationID", sublocationId);
subId.Value = (object)sublocationId ?? DBNull.Value;
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlInterpolated($"EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID={compId}, @DepartmentID={deptId}, @LocationID={locId}, @SubLocationID={subId}").ToList();
以下は上記と似ていますが、少し簡略化された構文を使用しています。
// Works
var compId = new SqlParameter("@CompanyID", companyId);
compId.Value = (object)companyId ?? DBNull.Value;
var deptId = new SqlParameter("@DepartmentID", departmentId);
deptId.Value = (object)departmentId ?? DBNull.Value;
var locId = new SqlParameter("@LocationID", locationId);
locId.Value = (object)locationId ?? DBNull.Value;
var subId = new SqlParameter("@SubLocationID", sublocationId);
subId.Value = (object)sublocationId ?? DBNull.Value;
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlInterpolated($"EXEC mis.BudgetWorkflowStatusByDepartment_Filtered {compId}, {deptId}, {locId}, {subId}").ToList();
以下は、究極の速記構文です。内挿法を使用すると、入力変数を直接使用でき、SqlParameter
オブジェクトを作成する必要はありません。
// Works
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlInterpolated($"EXEC mis.BudgetWorkflowStatusByDepartment_Filtered {companyId}, {departmentId}, {locationId}, {sublocationId}").ToList();
動作しない例
次のコードは空の配列を返しますが、これは正しくありません。コードのこのパラメーター化は、「補間された」SQLメソッドでのみこの方法で使用できます。
// Returns empty array
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlRaw("EXEC mis.BudgetWorkflowStatusByDepartment_Filtered {0}, {1}, {2}, {3}", companyId, departmentId, locationId, companyId).ToList();
結果-空の配列。これは正しくありません。
[]
次のメソッドは、FromSqlRaw
メソッドの構文が正しくありません。文字列内のパラメータは補間を使用したいのですが、これはその構文に対して間違った方法です。
// Error Must declare the scalar variable "@CompanyID".
var compId = new SqlParameter("@CompanyID", companyId);
compId.Value = (object)companyId ?? DBNull.Value;
var deptId = new SqlParameter("@DepartmentID", departmentId);
deptId.Value = (object)departmentId ?? DBNull.Value;
var locId = new SqlParameter("@LocationID", locationId);
locId.Value = (object)locationId ?? DBNull.Value;
var subId = new SqlParameter("@SubLocationID", sublocationId);
subId.Value = (object)sublocationId ?? DBNull.Value;
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlRaw($"EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID={compId}, @DepartmentID={deptId}, @LocationID={locId}, @SubLocationID={subId}").ToList();
エラー:
Must declare the scalar variable "@CompanyID".
以下も正しくありません。文字列内のパラメータは、この方法で入力することはできません。
// Error Must declare the scalar variable "@CompanyID".
var compId = new SqlParameter("@CompanyID", companyId);
compId.Value = (object)companyId ?? DBNull.Value;
var deptId = new SqlParameter("@DepartmentID", departmentId);
deptId.Value = (object)departmentId ?? DBNull.Value;
var locId = new SqlParameter("@LocationID", locationId);
locId.Value = (object)locationId ?? DBNull.Value;
var subId = new SqlParameter("@SubLocationID", sublocationId);
subId.Value = (object)sublocationId ?? DBNull.Value;
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlRaw($"EXEC mis.BudgetWorkflowStatusByDepartment_Filtered {compId}, {deptId}, {locId}, {subId}").ToList();
エラー:
Must declare the scalar variable "@CompanyID".
そして最後に、??
演算子がのnull
代わりにnull値を設定しているため、これも機能しませんDbNull.Value
。
var compId = new SqlParameter("@CompanyID", companyId);
compId.Value = (object)companyId ?? null;
var deptId = new SqlParameter("@DepartmentID", departmentId);
deptId.Value = (object)departmentId ?? null;
var locId = new SqlParameter("@LocationID", locationId);
locId.Value = (object)locationId ?? null;
var subId = new SqlParameter("@SubLocationID", sublocationId);
subId.Value = (object)sublocationId ?? null;
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlRaw("EXEC mis.BudgetWorkflowStatusByDepartment_Filtered @CompanyID, @DepartmentID, @LocationID, @SubLocationID", compId, deptId, locId, subId).ToList();
エラー:
The parameterized query '(@CompanyID nvarchar(4000),@DepartmentID nvarchar(4000),@Locatio' expects the parameter '@CompanyID', which was not supplied.
回答
私はちょうど同様のセットアップ(あなたが持っているすべてのものの同じバージョン)を再作成しました、そして次のワンライナーは私のために働きます:
public List<BudgetWorkflowStatusByDepartment> GetBudgetWorkflowStatusByDepartment(int? companyId, int? departmentId, int? locationId, int? sublocationId)
=> context.BudgetWorkflowStatusByDepartment
.FromSqlInterpolated($"EXEC mis.BudgetWorkflowStatusByDepartment {companyId}, {departmentId}, {locationId}, {subLocationId}").ToList();
私はこの.FromSqlInterpolated()
呼び出しを使用しているので、SqlParameter
sを作成する必要はありません。int?
これはドキュメントにあるように安全な呼び出しであり、null
値は適切に処理されるため、直接渡すことができます。
この呼び出しも試したようですがSqlParameter
、データ自体ではなく、sを渡しているようです。私のバージョンを試してみてください。
さらにテストしたところ、SqlParameter.IsNullable
プロパティをtrue
次のように設定することで、わずかな変更を加えて、バージョンを機能させることができました。
public List<BudgetWorkflowStatusByDepartment> GetBudgetWorkflowStatusByDepartment(int? companyId, int? departmentId, int? locationId, int? sublocationId)
{
var compId = new SqlParameter("@CompanyID", companyId) { IsNullable = true };
compId.Value = (object)companyId ?? DBNull.Value;
var deptId = new SqlParameter("@DepartmentID", departmentId) { IsNullable = true };
deptId.Value = (object)departmentId ?? DBNull.Value;
var locId = new SqlParameter("@LocationID", locationId) { IsNullable = true };
locId.Value = (object)locationId ?? DBNull.Value;
var subId = new SqlParameter("@SubLocationID", sublocationId) { IsNullable = true };
subId.Value = (object)sublocationId ?? DBNull.Value;
var result = context.BudgetWorkflowStatusByDepartment
.FromSqlRaw("EXEC mis.BudgetWorkflowStatusByDepartment @CompanyID = @CompanyID, @DepartmentID = @DepartmentID, @LocationID = @LocationID, @SubLocationID = @SubLocationID", compId, deptId, locId, subId)
.ToList();
return result;
}
あなたの選択をしてください-私は個人的にワンライナーが好きです:)