DataContext.ExecuteQuery <TEntity> and Null Values

Question

So my problem is this. When using Data Context ExecuteQuery (string query, params object [] parameters) ( here ), I can not go through the form or form zero. The parameter that ran this was the nvarchar SQL parameter, which should resolve zeros.

I read this article about a stackoverflow that "used DBNull.Value" is marked as an answer, so I tried this and nothing (so I wonder how this could be the answer if it doesn't work ?!).

Here is an example of the code I'm trying to run (note this is just a concept check):

var db = new Test1DataContext(Properties.Settings.Default.TestConnectionString); var query = "EXEC UpInsertTest4 {0}, {1}"; // Works fine var list1 = new List<object> { 1, "1" }; db.ExecuteQuery<UpInsertTest4Result>(query, list1.ToArray()); // Doesn't work var list2 = new List<object> { 1, DBNull.Value }; db.ExecuteQuery<UpInsertTest4Result>(query, list2.ToArray()); 

Here are the various errors I get:

 null : A query parameter cannot be of type 'System.Object'. default(string) : A query parameter cannot be of type 'System.Object'. DBNull.Value : Unexpected type code: DBNull (which give this as a stack trrace) 

 at System.Data.Linq.SqlClient.SqlTypeSystem.Sql2005Provider.From(Type type, Nullable`1 size) at System.Data.Linq.SqlClient.SqlTypeSystem.Sql2008Provider.From(Type type, Nullable`1 size) at System.Data.Linq.SqlClient.SqlTypeSystem.ProviderBase.From(Type type) at System.Data.Linq.SqlClient.SqlTypeSystem.ProviderBase.From(Object o) at System.Data.Linq.SqlClient.SqlFactory.ValueFromObject(Object value, Type clrType, Boolean isClientSpecified, Expression sourceExpression) at System.Data.Linq.SqlClient.QueryConverter.VisitConstant(ConstantExpression cons) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression exp) at System.Data.Linq.SqlClient.QueryConverter.VisitUserQuery(String query, Expression[] arguments, Type resultType) at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node) at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters) at System.Data.Linq.DataContext.ExecuteQuery[TResult](String query, Object[] parameters) at ConsoleApplication1.Program.Main(String[] args) in E:\TestBed\Testbed\ConsoleApplication1\Program.cs:line 36 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()
at System.Data.Linq.SqlClient.SqlTypeSystem.Sql2005Provider.From(Type type, Nullable`1 size) at System.Data.Linq.SqlClient.SqlTypeSystem.Sql2008Provider.From(Type type, Nullable`1 size) at System.Data.Linq.SqlClient.SqlTypeSystem.ProviderBase.From(Type type) at System.Data.Linq.SqlClient.SqlTypeSystem.ProviderBase.From(Object o) at System.Data.Linq.SqlClient.SqlFactory.ValueFromObject(Object value, Type clrType, Boolean isClientSpecified, Expression sourceExpression) at System.Data.Linq.SqlClient.QueryConverter.VisitConstant(ConstantExpression cons) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression exp) at System.Data.Linq.SqlClient.QueryConverter.VisitUserQuery(String query, Expression[] arguments, Type resultType) at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node) at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters) at System.Data.Linq.DataContext.ExecuteQuery[TResult](String query, Object[] parameters) at ConsoleApplication1.Program.Main(String[] args) in E:\TestBed\Testbed\ConsoleApplication1\Program.cs:line 36 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() 

From what I read, I can fight this battle! This is my last ditch attempt!

Many thanks,
Chris.

Answer 1

You can do it something like this:

 db.ExecuteQuery<UpInsertTest4Result>(string.Format(query,1,"null")); 

Update

Yes, I tested the solution. It will not contain @p1=N'null' . Because I use sql as a string directly. So, a professional concept.

Save procedure:

 CREATE PROCEDURE testas @test1 INT, @test2 INT AS SELECT 1 test 

to test the output:

 public class testout { public int test { get; set; } } 

Linq query with db context:

 var query="exec testas {0}, {1}"; db.ExecuteQuery<testout>(string.Format(query,"0","null")); 

If you look at linqpad , then the sql output looks like this:

 exec testas 0, null 

The format of the string simply changes the string. This would be the same as writing this:

 db.ExecuteQuery<testout>("exec testas 0, null") 

Answer 2

I had the same problem and I found a solution on this forum: https://social.msdn.microsoft.com/Forums/en-US/20b318bc-32cf-466b-972e-6cd37e625cd6/a-query-parameter-cannot -be-of-the-type-systemobject? forum = linqprojectgeneral

In principle, the goal is to create an extension method that will analyze the parameters and replace all zero parameters directly in the request.
Example: "EXEC MyStoredProcedure {0}, {1}, {2}, {3}" with the second null parameter value will become "EXEC MyStoredProcedure {0}, NULL, {1}, {2}", and the second parameter will be deleted from array parameters.

Here is a way:

 internal static IEnumerable<TResult> ExecuteQueryNullSafe<TResult>(this System.Data.Linq.DataContext context, string command, params object[] parameters) { var list = new List<object>(); var listVals = new List<bool>(); for (int x = 0; x < parameters.Count(); x++) { if (parameters[x] == null || parameters[x] is System.DBNull) { command = command.Replace("{" + x + "}", "NULL"); listVals.Add(false); } else { list.Add(parameters[x]); listVals.Add(true); } } int nextId = 0; for (int i = 0; i < listVals.Count; i++) { var isUsed = listVals[i]; if (!isUsed) continue; if (nextId != i) command = command.Replace("{" + i.ToString() + "}", "{" + nextId.ToString() + "}"); nextId++; } return context.ExecuteQuery<TResult>(command, list.ToArray()); } 

Answer 3

try this Chris:

 void Main() { var db = new Test1DataContext(Properties.Settings.Default.TestConnectionString); db.UpInsertTest4Result(1,"1"); db.UpInsertTest4Result(1,null); db.UpInsertTest4Result(null,"1"); db.UpInsertTest4Result(null,null); } public class Test1DataContext : DataContext { public Test1DataContext(string connStr) : base(connStr) { } [Function(Name = "UpInsertTest4")] public IEnumerable<UpInsertTest4Result> UpInsertTest4Result( [Parameter(Name = "par1", DbType = "Int")] int? par1, [Parameter(Name = "par2", DbType = "VarChar")] string par2) { var result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), par1,par2); return (IEnumerable<UpInsertTest4Result>)result.ReturnValue; } }