none
null return FirstOrDefault RRS feed

  • Question

  • Got a return from a SqlQuery that resulted in a null instead of a double value as expected?  I found the error in the data that was causing this but when I tried to put is null check into code it wouldn't let me check for it.  the sql is 

    var sumDept = pse.Database.SqlQuery<double>("Select Sum(w.Weight * d.Value) as Sumproduct FROM DeptIntermediate d join Weights w on w.Node = d.Node and d.Dept = " + department.ToString());

    Think it is because the code executes the query when called 

     return sumDept.FirstOrDefault();
    What would be the proper way to check for null around this line of code?  

    TIA

    Harry


    • Edited by Mimosa Arts Wednesday, October 30, 2019 7:29 PM more info
    Wednesday, October 30, 2019 7:28 PM

Answers

  • You could use ?? null coalescing operator.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by cheong00Editor Thursday, October 31, 2019 4:40 AM
    • Marked as answer by cheong00Editor Friday, November 8, 2019 6:51 AM
    Wednesday, October 30, 2019 10:24 PM
    Moderator
  • Karen you were close to the answer.  

      private double CalcSumPerDept(int department, int count)
            {
                 //Select the 23 intermediate values 
                //multiply the wt x intermediate value x count
                //and add to the running sum for that dept
               var sumDept = pse.Database.SqlQuery<double>("Select Sum(w.Weight * d.Value) as Sumproduct FROM DeptIntermediate d join Weights w on w.Node = d.Node and d.Dept = " + department.ToString());
                double? result = sumDept.FirstOrDefault();
    
                if (result.HasValue)
                {
                    return result.Value;
                }
                else
                { 
                    utilities.WriteLog("CalcSumPerDept problem", "Department " + department.ToString() + " Not Found");
                    return 0;               
                }
    
            }

    This handles value or null correctly without error.

    Thanks

    Harry

    Thursday, October 31, 2019 6:55 PM

All replies

  • If the SQLQuery resulted in a null value being retuned, then you can just check sumDept for null value.

    If (sumDept != null)
    {
        return sumDept.First();
    } 
    else
    {
      //do something else
    }


    Wednesday, October 30, 2019 8:29 PM
  • You could use ?? null coalescing operator.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by cheong00Editor Thursday, October 31, 2019 4:40 AM
    • Marked as answer by cheong00Editor Friday, November 8, 2019 6:51 AM
    Wednesday, October 30, 2019 10:24 PM
    Moderator
  • Hi Mimosa Arts, 

    Thank you for posting here.

    According to your question, we can use the following code the check if query result is null.

                SqlCommand cmd = new SqlCommand("select...", con);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        // The command returns Row(s)
                    }
                }
                else
                {
                    // There is no row in query result.
                }

    Hope it can help you.

    Besides, If I have any misunderstanding, please provide some code about ‘pse.Database.SqlQuery<double>()’ ? It will help us to make a test.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Thursday, October 31, 2019 8:10 AM
    Moderator
  • This allowed it to compile but actually returns true even though the result of the query is null.  

    I ran this query: 


    var sumDept = pse.Database.SqlQuery<double>("Select Sum(w.Weight * d.Value) as Sumproduct FROM DeptIntermediate d join Weights w on w.Node = d.Node and d.Dept =  900");
                if (sumDept != null)
                {
                    return sumDept.FirstOrDefault();
                }
                else
                {
                    return 0;
                }     

    ?sumDept != null
    true
    ?sumDept
    {Select Sum(w.Weight * d.Value) as Sumproduct FROM DeptIntermediate d join Weights w on w.Node = d.Node and d.Dept =  900}
        InternalQuery: {Select Sum(w.Weight * d.Value) as Sumproduct FROM DeptIntermediate d join Weights w on w.Node = d.Node and d.Dept =  900}
        System.ComponentModel.IListSource.ContainsListCollection: false
        _internalQuery: {Select Sum(w.Weight * d.Value) as Sumproduct FROM DeptIntermediate d join Weights w on w.Node = d.Node and d.Dept =  900}
        Results View: Expanding the Results View will enumerate the IEnumerable

      
    Thursday, October 31, 2019 3:33 PM
  • pse is the EF object pointing to the Entity Framework model.

    I tried to go ahead and get the value into a double variable but received  this error

    System.InvalidOperationException: 'The cast to value type 'System.Double' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.'

    With the following line green highlighted. 

    double result = sumDept.FirstOrDefault();

    Ideas?

    result if not null looks like a double ex: .102923

    Harry

    Thursday, October 31, 2019 3:55 PM
  • https://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx

    The link shows about DB.SQLQuery usinf FirstOrDefault(). You will still need to do to the null value check on the result 

    Thursday, October 31, 2019 5:54 PM
  • That's an excellent resource - thanks for sharing.
    Thursday, October 31, 2019 6:53 PM
  • Karen you were close to the answer.  

      private double CalcSumPerDept(int department, int count)
            {
                 //Select the 23 intermediate values 
                //multiply the wt x intermediate value x count
                //and add to the running sum for that dept
               var sumDept = pse.Database.SqlQuery<double>("Select Sum(w.Weight * d.Value) as Sumproduct FROM DeptIntermediate d join Weights w on w.Node = d.Node and d.Dept = " + department.ToString());
                double? result = sumDept.FirstOrDefault();
    
                if (result.HasValue)
                {
                    return result.Value;
                }
                else
                { 
                    utilities.WriteLog("CalcSumPerDept problem", "Department " + department.ToString() + " Not Found");
                    return 0;               
                }
    
            }

    This handles value or null correctly without error.

    Thanks

    Harry

    Thursday, October 31, 2019 6:55 PM
  • Karen you were close to the answer.  

      private double CalcSumPerDept(int department, int count)
            {
                 //Select the 23 intermediate values 
                //multiply the wt x intermediate value x count
                //and add to the running sum for that dept
               var sumDept = pse.Database.SqlQuery<double>("Select Sum(w.Weight * d.Value) as Sumproduct FROM DeptIntermediate d join Weights w on w.Node = d.Node and d.Dept = " + department.ToString());
                double? result = sumDept.FirstOrDefault();
    
                if (result.HasValue)
                {
                    return result.Value;
                }
                else
                { 
                    utilities.WriteLog("CalcSumPerDept problem", "Department " + department.ToString() + " Not Found");
                    return 0;               
                }
    
            }

    This handles value or null correctly without error.

    Thanks

    Harry

    That was not next step, ask if the column was Nullable and if so .HasValue would had been my recommendation :-)

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, October 31, 2019 7:20 PM
    Moderator