Entity Framework 6 get complext return value from a stored procedure

Question

My ASP.Net MVC app needs to execute a stored procedure in order to check for duplicate work orders based upon a set of parameters. The stored procedure is large and complex, so converting it to a Linq to SQL query is out of the question. Also, we do not use Entity Data Model (EDMX) files for this; we only have the domain models, view models and a data context. The stored procedure has the following signature:

CREATE PROCEDURE [dbo].[spGetDupWOs]
    -- Add the parameters for the stored procedure here
    @ProjectName VARCHAR(200) = '""',
    @City VARCHAR(100) = '',
    @State VARCHAR(100) = '',
    @ProjectNumber VARCHAR(50) = '',
    @County VARCHAR(50) = '',
    @Customer VARCHAR(400) = '',
    @QuoteRecipients VARCHAR(400) = NULL,
    @ProjectID BIGINT = NULL
    ...

    SELECT DuplicateID, ProjectNameMatchRank, ProjectNameMatch, ProjectName ...

I have tried to call it in my controller like:

IEnumerable<DuplicateProjects> duplicateCheckResults = db.Database.SqlQuery< DuplicateProjects>("spGetDupWOs", new { ProjectName = ProjectName, City = City, State = propal.State, ProjectNumber = ProjectNumber, County = County, Owner = Owner, BID_QuoteRecipients = QuoteRecipients, ProjectID = -1 });

And get an exception when I run this like:

No mapping exists from object type <>f__AnonymousType6`8[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] to a known managed provider native type.

What am I doing wrong?

UPDATE: When I change all of the passed parameters to ObjectParamter I still get the same error message.

UPDATE 2:

I refactored the code a bit. Here is what it looks like now:

            List<SqlParameter> spl = new List<SqlParameter>();

            var ProjectNameParameter = new SqlParameter("ProjectName", project.ProjectName);
            spl.Add(ProjectNameParameter);
            var CityParameter = new SqlParameter("City", project.City);
            spl.Add(CityParameter);
            var StateParameter = new SqlParameter("State", project.State);
            spl.Add(StateParameter);
            var ProposalNumberParameter = new SqlParameter("ProjectNumber", project.ProjectNumber);
            spl.Add(ProposalNumberParameter);
            var CountyParameter = new SqlParameter("County", project.County);
            spl.Add(CountyParameter);
            var OwnerParameter = new SqlParameter("Owner", project.Owner);
            spl.Add(OwnerParameter);
            var BidRecipientParameter = QuoteRecipients != null ?
                new SqlParameter("QuoteRecipients", QuoteRecipients) :
                new SqlParameter("QuoteRecipients", "");
            spl.Add(BidRecipientParameter);
            var ProjectIDParameter = new SqlParameter("ProjectID", typeof(long));
            spl.Add(ProposalIDParameter);

            IEnumerable<DuplicateProposals> duplicateCheckResults = db.Database.SqlQuery<DuplicateProposals>("spGetDupWOs ProjectName = {0} City = {1}, State = {2}, ProjectNumber = {3}, County = {4}, Owner = {5}, QuoteRecipients = {6}, ProjectID = {7}",
                spl.ToArray());

Now I get the error:

The SqlParameter is already contained by another SqlParameterCollection.

Have you tried this way? IEnumerable<DuplicateProjects> duplicateCheckResults = db.Database.SqlQuery< DuplicateProjects>("spGetDupWOs", new DuplicateProjects() { ProjectName = ProjectName, City = City, State = propal.State, ProjectNumber = ProjectNumber, County = County, Owner = Owner, BID_QuoteRecipients = QuoteRecipients, ProjectID = -1 }); Note the new DuplicateProjects() instead of the anonymous type. — Bradley Uffner
The only fly in the ointment is that DuplicateProjects is the model for the output, not the input. I'm a little slow right now. Do you think I should make the input variables into SQL parameters? — user6373040

Chris Pratt Chris Pratt · Accepted Answer · 2017-03-08T18:16:10

Further arguments to SqlQuery<T> after the query string constitute a params array, which the method will use to backfill the query string with numbered parameter arguments, much as String.Format does. Since you're passing an anonymous object, the method is trying to treat that as one single parameter and can't convert it into a string. So, yes, you either need to use SqlParameter to manually add the params or alter your call to handle it in a way compatible with this method:

db.Database.SqlQuery< DuplicateProjects>(
    "spGetDupWOs @ProjectName = {0}, @City = {1}, @State = {2}, @ProjectNumber = {3}, @Country = {4}, @Owner = {5}, @BID_QuoteRecipients = {6}, @ProjectID = {7}",
    ProjectName,
    City,
    propal.State,
    ProjectNumber,
    County,
    Owner,
    QuoteRecipients,
    -1
);

Liberal spacing added for better readability

Entity Framework 6 get complext return value from a stored procedure

1 Answers