CODEWITHSUNDEEP
c#linq-to-sql
Macros
Macros

Reputation: 7119

Linq-To-Sql SP won't map to class

I have a class in my Linq-To-Sql model and am trying to map a Stored Procedure to it. Whatever I try I get the message:

one or more selected database objects return a schema that does not match

The schema definitely does match, I have even resorted to just doing a auto generated 'select top 100 rows' in SSMS and putting this in the SP, nothing else, and I still get this message.

Is there anything else I should be looking at?

My table schema is as follows:

CREATE TABLE [dbo].[Booking](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ClientID] [int] NULL,
    [BookingTypeID] [int] NULL,
    [LinkedBookingID] [int] NULL,
    [DateCreated] [smalldatetime] NULL,
    [DateUpdated] [smalldatetime] NULL,
    [BookingDateTime] [smalldatetime] NULL,
    [BookingStatusID] [int] NULL,
    [ConfirmationRequired] [bit] NOT NULL,
    [Confirmed] [bit] NOT NULL,
    [InProgress] [bit] NOT NULL,
    [ServiceID] [int] NULL,
    [EmployeeID] [int] NULL,
    [Duration] [int] NULL,
    [ProcessingDuration] [int] NULL,
    [IsPartOfCourse] [bit] NULL,
    [CancellationReason] [int] NULL,
    [Timestamp] [timestamp] NULL,
    [IsLinked] [bit] NULL,
 CONSTRAINT [PK_Booking] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

and SP:

CREATE PROCEDURE booking_test


AS
BEGIN


SELECT TOP 1000 [ID]
      ,[ClientID]
      ,[BookingTypeID]
      ,[LinkedBookingID]
      ,[DateCreated]
      ,[DateUpdated]
      ,[BookingDateTime]
      ,[BookingStatusID]
      ,[ConfirmationRequired]
      ,[Confirmed]
      ,[InProgress]
      ,[ServiceID]
      ,[EmployeeID]
      ,[Duration]
      ,[ProcessingDuration]
      ,[IsPartOfCourse]
      ,[CancellationReason]
      ,[Timestamp]

  FROM [Booking]

END
GO

Upvotes: 3

Views: 612

Answers (2)

user3301175
user3301175

Reputation: 11

I have also gone through this issue.

The default method returns a generic type called ISingleResult. This type is defined in the System.Data.Linq namespace and enables you to represent the results of a mapped function that has a single return sequence.

This can be helpful when you build custom queries that do not return all the fields, but table entity is already defined in our Designer and that is what we would like to work with.

To change the method to return the table entity, you have to remove the stored procedure from the Methods pane of the ORM Designer. Drag the stored procedure from the Server Explorer onto the table instead of the Methods pane. The procedure will appear in the Methods pane again but now look at the code that was generated. The select method now returns ISingleResult entities, and the tableselectall class was removed:

You need to check if table fields and fields you are selecting in storedprocedure is maching ( No filed is missing) I have added missing field within stored procedure and error resolved.

Upvotes: 0

shaunmartin
shaunmartin

Reputation: 3919

Add [IsLinked] to the queried columns in your stored procedure and it should work. (I just tested with your table, etc, and adding [IsLinked] to the stored procedure allowed me to drop the stored procedure onto the table in the designer without an error.)

I know that if you're using a stored procedure to query objects which are not LINQ to SQL entities (but you're using LINQ to SQL to auto-populate the objects), you can skip columns like you did with [IsLinked]. But if you're querying objects that are LINQ to SQL entities, you have to query all the columns. I think that's how it works.

Upvotes: 1

Related Questions