C# / Entity Framework - Columns without names

Hi guys,

I am developing a piece of software which integrates with my companies billing system. The creators of the software have allowed us to use all of their stored procedures however they are unwilling to change anything or allow us to unlock/edit them.

There is one procedure which outputs three "No column name" columns. It's not a big deal for me because I know what they do, however when I am trying to use Entity Framework it throws a fit. I have imported the stored procedure and it automatically created three columns named "Column1, Column2, Column3" respectively. I was excited when I saw this because I just referenced those column names in my code and thought all was well.

Well .. not so much. Anytime I execute my code I receive the following error:


"ExceptionMessage=The data reader is incompatible with the specified 'Sedona.Service_Tech_Schedule_Result'. A member of the type, 'Column1', does not have a corresponding column in the data reader with the same name."

Open in new window


I have done hours worth of researching on the web and can't seem to find anyone that has had this problem.

Here is a partial result of the procedure when run in SQL Management Studio
Capture.PNG
Here is the file that Visual Studio generated, Note the columns labeled Column1 and Column2
//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated from a template.
//
//     Manual changes to this file may cause unexpected behavior in your application.
//     Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace PMT.Models
{
    using System;
    
    public partial class Service_Tech_Schedule_Result
    {
        public string Column1 { get; set; }
        public string Column2 { get; set; }
        public Nullable<System.DateTime> Schedule_Time { get; set; }
        public Nullable<System.DateTime> Dispatch_Time { get; set; }
        public Nullable<System.DateTime> Arrival_Time { get; set; }
        public Nullable<System.DateTime> Departure_Time { get; set; }
        public Nullable<int> Estimated_Length { get; set; }
        public string Problem_Code { get; set; }
        public string Business_Name { get; set; }
        public string ge1_Description { get; set; }
        public int Dispatch_Id { get; set; }
        public string Requested_By { get; set; }
        public string Requested_By_Phone { get; set; }
        public string Address_1 { get; set; }
        public string Address_2 { get; set; }
        public string Address_3 { get; set; }
        public string ge2_Short { get; set; }
        public string ge3_Description { get; set; }
        public string ge4_Description { get; set; }
        public string ge5_Description { get; set; }
        public string Service_Company_Code { get; set; }
        public string Map_Code { get; set; }
        public string Cross_Street { get; set; }
        public string Column3 { get; set; }
        public string Resolution_Code { get; set; }
        public string IsGoBack { get; set; }
        public string Completed { get; set; }
        public int Country_Id { get; set; }
        public string Route_Code { get; set; }
    }
}

Open in new window

"ExceptionMessage=The data reader is incompatible with the specified 'Sedona.Service_Tech_Schedule_Result'. A member of the type, 'Column1', does not have a corresponding column in the data reader with the same name."

Open in new window

LVL 2
wpatterson82Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
Not being able to use unnamed columns is a limitation of EF. The only way I can think you're going to be able to get it to work would be to create a wrapper stored proc to pull the results into a temp table and then select from there.

Something like:
create procedure spSampleWrapper
as
begin

    /* table variable - column types and position to match result set of SP */
	declare @temp table ( ID int, [Name] varchar(21) )

	/* exec proc and store results in table variable */
	insert into @temp
		exec spGetNames

	/* return results from table variable */
	select * from @temp

end

Open in new window

A table variable will only suit a reasonably small result set, a proper temp table would suit a larger query. This will also give you additional overhead due to the fact you're now calling two procedures, but you may just have to suffer that.

Your only other option would be to replicate the stored proc and introduce your own column names, or use an in-line query in EF.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wpatterson82Author Commented:
Great idea! Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.