1

I'm trying to use one of these namespaces 

//using Microsoft.Data.SqlClient;
using System.Data.SqlClient;

My model 

public class Get_Data_Scholar{
    [Key]
    public Int32 ID_Transcript { get; set; }
    public string Year_Semester { get; set; }
    public string Period { get; set; }
    public string Status { get; set; }
}

My controller

        public JsonResult Get_GPA_Tuition (Int32 id)
        {
            var ID_NCS = new SqlParameter("@ID_NCS", id);
            List<Get_data> get_Data = new List<Models.Get_Data_Scholar>();
            get_Data = _applicationDbContext.Get_Data_Scholar.FromSql(
                            "EXEC [dbo].[Get_Data_Scholar]  @ID"

                            , id
                            ).ToList();

            return Json(get_Data );
        }

When I'm using System.Data.SqlClient, everything works well. But when I change different namespace to Microsoft.Data.SqlClient, it produce error message like this:

System.InvalidCastException HResult=0x80004002 Message=The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects. Source=System.Data.SqlClient
StackTrace: at System.Data.SqlClient.SqlParameterCollection.ValidateType(Object value) at System.Data.SqlClient.SqlParameterCollection.Add(Object value) at Microsoft.EntityFrameworkCore.Storage.Internal.DynamicRelationalParameter.AddDbParameter(DbCommand command, Object value) at Microsoft.EntityFrameworkCore.Storage.Internal.CompositeRelationalParameter.AddDbParameter(DbCommand command, Object value) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalParameterBase.AddDbParameter(DbCommand command, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.CreateCommand(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.BufferlessMoveNext(Boolean buffer) at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__172.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext() at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

When I'm using System.Data.SqlClient, return query from SQL server with null value can be bypass without any error message. But when it changes to Microsoft.Data.SqlClient, return query from SQL Server must be treated with special treatment. So, How do I do/overcome this error message ? I knew this question kind of old style but how to treat non-null SqlParameterbecause of some return null column ?

The reason I'm trying to change Microsoft.Data.SqlClient namespace because near in the future some sensitive column will be encrypted using AKV (AlwaysEncrypted).

Improve this question
7
  • You could check if your parameter is null and based on that you can handle your situation accordingly: if(ID_NCS != null){ // your logic} else {//handle null condition} Commented Dec 27, 2019 at 10:35
  • ID_NCS never be null because it's a feed parameter. What will be null is the return query from SQL Server after execute Store procedure. Commented Dec 27, 2019 at 10:41
  • Which version of .NET CORE are you on? Commented Dec 27, 2019 at 10:50
  • it's .NET CORE v 2.2 Commented Dec 27, 2019 at 10:51
  • 2
    Well, I believe that you should upgrade to .NET CORE 3.0. Read this article for more information: visualstudiomagazine.com/articles/2019/12/19/… and github.com/aspnet/EntityFrameworkCore/issues/16812 Commented Dec 27, 2019 at 10:59

2 Answers 2

Reset to default
0

If I am understanding your question correctly you're saying that when the value of your sql param is the literal null, this blows up, and you do not want this to happen. You need to detect null and instead use System.DBNull.Value.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thank for your curious my question, how to detect return null value by using SystemDBNull.Value ?
new SqlParameter("@ID_NCS", id == null ? DBNull.Value : id) although in your example id is a non-nullable int, so I see now that my answer is fairly irrelevant ><
-1

You can try like change to FromSqlRaw and also change System.Data.SqlClient.SqlParameter to Microsoft.Data.SqlClient.SqlParameter

 get_Data = _applicationDbContext.Get_Data_Scholar.FromSql(
                        "EXEC [dbo].[Get_Data_Scholar] {0}", 
                         id).ToList();
Improve this answer

4 Comments

what kind of assembly should I use ? Error message: dbset <Get_Data_Scholar> does not contain a definition for 'FromSqlRaw' and no accessible extension methot 'FromSqlRaw' accepting a first argument of type 'DbSet<Get_Data_Scholar>' could be found (are you missing a using directive or an assembly reference ?)
Microsoft.Data.SqlClient, it's already installed. Or Microsoft.Data.SqlClient only works on .Net Core v.3 ?
try adding using Microsoft.EntityFrameworkCore; at the top of namespaces
By adding this namespace Microsoft.Data.SqlClient.SqlParameter, using directive is unnecessary.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.