Linq : mapping proc to fuction with 2 different statement

Question

4.50/5 (2 votes)

See more:

I make stored procedure that select data with condition but the problem is :-

when RequiredIDID =2 ( Select come with some data from join table and if not I am not select it in else statement ) and after mapping to linq function this problem appears

if I'm pass requirdID = 2 to function no problem occurs coz the first select in proc executed but if i pass other requirdID ( ex. = 3 ) this message appears

The data reader is incompatible with the specified 'RealEstateCenterModel.SearchForRealState_Reader'. A member of the type, 'FloorTypeCategory', does not have a corresponding column in the data reader with the same name.

USE [RealEstateCenter]
GO
/****** Object:  StoredProcedure [dbo].[TestOptionalParameter]    Script Date: 08/23/2012 07:29:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[TestOptionalParameter]
@RequiredIDID int =NULL 
as

if @RequiredIDID=2
begin
SELECT     FloorSpace.SpaceFrom, RealEstateCategoryDetails.RequiredCategoryTypeName, FloorType.FloorTypeCategory, FloorType.FloorNotes, 
                      RealEstateTypes.RequiedType
FROM         PersonalRealStateDetails INNER JOIN
                      FloorSpace ON PersonalRealStateDetails.FloorSpaceID = FloorSpace.FloorSpaceID INNER JOIN
                      RealEstateCategoryDetails ON PersonalRealStateDetails.RequiredCategoryTypeID = RealEstateCategoryDetails.RequiredCategoryTypeID INNER JOIN
                      FloorType ON PersonalRealStateDetails.FloorTypeID = FloorType.FloorTypeID INNER JOIN
                      RealEstateTypes ON RealEstateCategoryDetails.RequiredID = RealEstateTypes.RequiredID

end

else
begin
SELECT     FloorSpace.SpaceFrom, RealEstateCategoryDetails.RequiredCategoryTypeName, RealEstateTypes.RequiedType
FROM         PersonalRealStateDetails INNER JOIN
                      FloorSpace ON PersonalRealStateDetails.FloorSpaceID = FloorSpace.FloorSpaceID INNER JOIN
                      RealEstateCategoryDetails ON PersonalRealStateDetails.RequiredCategoryTypeID = RealEstateCategoryDetails.RequiredCategoryTypeID INNER JOIN
                      RealEstateTypes ON RealEstateCategoryDetails.RequiredID = RealEstateTypes.RequiredID
end

Posted 22-Aug-12 23:26pm

Ahmed R El Bohoty

Add a Solution

2 solutions

Solution 2

It's two different queries, based on a parameter you're passing in. You should just do two different queries and switch between them in the C# based on the parameter, e.g.

if(RequiredID == 2){
 var answer = select from ... (first query)
} else {
 var answer = select from ... (other query)
}

I guess one will have to return a list of a type that is a subclass of the other, as there are extra properties. Then you have to think about how you can access those properties later in your application.

Posted 22-Aug-12 23:59pm

BobJanova

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Martijn Kok · Accepted Answer · 2012-08-22T23:57:00

I would say that the error is raised, because your resulting query supplies only 3 columns when the @RequiredIDID != 2. C# has analyzed the stored procedure and always expects 5 columns.

This could be solved by supplying 2 extra columns when the @RequiredIDID is not equal to 2. For instance change the ELSE part of your code to:

SQL

else
begin
SELECT     FloorSpace.SpaceFrom, RealEstateCategoryDetails.RequiredCategoryTypeName, 
NULL as FloorTypeCategory, NULL as FloorNotes,
RealEstateTypes.RequiedType
FROM         PersonalRealStateDetails INNER JOIN
                      FloorSpace ON PersonalRealStateDetails.FloorSpaceID = FloorSpace.FloorSpaceID INNER JOIN
                      RealEstateCategoryDetails ON PersonalRealStateDetails.RequiredCategoryTypeID = RealEstateCategoryDetails.RequiredCategoryTypeID INNER JOIN
                      RealEstateTypes ON RealEstateCategoryDetails.RequiredID = RealEstateTypes.RequiredID
end

This should make the code work. I hope it give the desired result.