CODEWITHSUNDEEP
c#entity-frameworkstored-procedures
RoboKozo
RoboKozo

Reputation: 5062

Stored procedure enumeration not yielding any results

I'm trying to execute a stored procedure in my project, but the enumeration is not yielding any results.

Why? What am I doing wrong?

Info about my project:

.NET Framework 4.5
<package id="EntityFramework" version="5.0.0" targetFramework="net45" />
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)

Manually using stored procedure works and returns the correct data:

DECLARE @return_value int

EXEC    @return_value = [dbo].[GetFeatures]
        @XMLDoc = N'<ArrayOfInt><int>280</int><int>286</int><int>279</int><int>292</int><int>277</int></ArrayOfInt>',
        @ReleaseId = 340

SELECT  'Return Value' = @return_value

Here's where I execute the stored procedure in my code:

public ObjectResult<GetFeatures_Result> GetFeatures(List<int> featureIds, int releaseId, int? timeboxId, int? teamId)
{
    string xmlFeatureIds = null;   

    var xs = new XmlSerializer(typeof(List<int>)); 
    var ms = new MemoryStream(); 
    xs.Serialize(ms, featureIds.ToList()); 

    //xmlFeatureIds = Encoding.UTF8.GetString(ms.ToArray());
    xmlFeatureIds = "<ArrayOfInt><int>280</int><int>286</int><int>279</int><int>292</int><int>277</int></ArrayOfInt>";
    //releaseId = 340;
    var release = _rmContext.Releases.FirstOrDefault(x => x.Id == releaseId);//works, Releases is just a table
    var obj = _rmContext.GetFeatures(xmlFeatureIds, releaseId, null, null); //enumeration didn't yield any results
    var obj2 = _rmContext.GetFeatures(xmlFeatureIds, releaseId, null, null).ToList(); //length of this is zero.

    return obj;
}

Model browser and visual studio setup: enter image description here

EDIT:

From the SQL profiler:

declare @p1 xml
set @p1=convert(xml,N'<ArrayOfInt><int>280</int><int>286</int><int>279</int><int>292</int><int>277</int></ArrayOfInt>')
exec [dbo].[GetFeatures] @XMLDoc=@p1,@ReleaseId=340,@TimeBoxId=NULL,@TeamId=NULL

Upvotes: 1

Views: 935

Answers (1)

Martin Smith
Martin Smith

Reputation: 453233

The query sent from your application passes an explicit value of NULL for the parameters @TimeBoxId and @TeamId.

The one you are testing in SSMS omits these parameters entirely.

If the parameters have defaults these are only applied when the parameters are omitted or the DEFAULT keyword is specified.

This explains the difference in behaviour.

I'm not sure if there is any way to get the EF generated query to do that but you can either alter the application to pass the desired default values of 0 instead of NULL or alter the stored procedure so the first step is.

SET @TimeBoxId = ISNULL(@TimeBoxId,0)
SET @TeamId = ISNULL(@TeamId,0)

Upvotes: 1

Related Questions