C# – How to handle a NULLable Int column returned from a stored procedure in LINQ to SQL

asp.netclinq-to-sqlstored-procedures

I have a stored procedure that returns two int Values. The first can not be null but the second can actually return a null value. When I try to execute my stored procedure using LINQ to SQL it fails with the statement:

The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.

I tried to change my designer file in my DBML and add in: CanBeNull= true to the Column mapping of the Stored Procedures Result.

I'm can seem to figure out the correct way to handle this, not sure if I can change my LINQ/C# code to account for the null (which would be preferable) or if I will have to update my Stored Procedure in some fashion.

Best Answer

You need to declare the variable to be nullable. In the designer, click the property in your model that needs to be nullable, and then in the Properties window, set the "Nullable" property to True. This will tell the generator to create your property as Nullable<int> or int?

Related Solutions

C# – How to cast int to enum

From an int:

YourEnum foo = (YourEnum)yourInt;

From a string:

YourEnum foo = (YourEnum) Enum.Parse(typeof(YourEnum), yourString);

// The foo.ToString().Contains(",") check is necessary for enumerations marked with an [Flags] attribute
if (!Enum.IsDefined(typeof(YourEnum), foo) && !foo.ToString().Contains(","))
{
    throw new InvalidOperationException($"{yourString} is not an underlying value of the YourEnum enumeration.")
}

Update:

From number you can also

YourEnum foo = (YourEnum)Enum.ToObject(typeof(YourEnum) , yourInt);

Sql-server – SQL Server: Query fast, but slow from procedure

I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure.

I found another answer here "Parameter Sniffing", Thanks Omnibuzz. Boils down to using "local Variables" in your stored procedure queries, but read the original for more understanding, it's a great write up. e.g.

Slow way:

CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
    SELECT * 
    FROM orders
    WHERE customerid = @CustID
END

Fast way:

CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
    DECLARE @LocCustID varchar(20)
    SET @LocCustID = @CustID

    SELECT * 
    FROM orders
    WHERE customerid = @LocCustID
END

Hope this helps somebody else, doing this reduced my execution time from 5+ minutes to about 6-7 seconds.

Best Answer

Related Solutions

C# – How to cast int to enum

Sql-server – SQL Server: Query fast, but slow from procedure

Related Topic