LINQ to SQL and Case statement

Working
with LINQ I realized that I had to use the simple case statement in my
SQL query. There is no special keyword for this. To create a case
statement like structure you will have to do it in the select section of
the query.
Below
is an example of the usage of the case statement in LINQ.
var t =
from n in idc.categories select
new {
catName =
(n.id==1 ? "Cat1" : n.id==2
? "Cat2" : n.id==3 ?
"Cat3" : "Unknown Category" )
};
Here
in the above code we are using multiple cases for value 1, 2 and 3

More Related Resource Links

I usually work with windows forms, and am just starting to work with ASP.Net, so appologies if this is a dumb question!

I have a master page which has a search button on it. The results of this are built up using a Linq statement and if there are any results, I want to display the results in a contents page.

On the masterpage, I have the following code:

Private _res
Public Overridable ReadOnly Property SearchRes()
Get
Return _res
End Get
End Property
Private Sub DoSearch()
'some code to get a linq result
'then
If res.Any Then
_res = res
Server.Transfer("SearchResults.aspx", True)
Else
'no results
LabelNoRes.Visible = True
End If
End Sub

SELECT CASE OT.ItemTypeID
WHEN 6 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12)
WHEN 7 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15)
WHEN 8 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 8
WHEN 9 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 9
WHEN 10 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 10
WHEN 11 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12)
WHEN 12 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12)
WHEN 14 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15)
WHEN 15 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15)
WHEN 18 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 18
WHEN 19 THEN SELECT SUM(ISNULL(&qu

Hi All,
I have table something like this:-
SELECT UserName, IsManager FROM Employee
IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager)
Now I want to write below query:-
DECLARE @IsManager INT
SET @IsManager = 2
SELECT
UserName,
IsManager
FROM
Employee
WHERE IsManager =
CASE
WHEN @IsManager = 1 THEN 1
WHEN @IsManager = 0 THEN 0
WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0"
END
Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2".
Thanks
Regards,
Kumar

I have some query logic that needs to return two dynamically created columns based on two conditional tests so that
- If Test1 is True then the values for columns 1 and 2 are always the same regardless of the results of Test2.
- If Test1 is False then Test2 determines the value of Column2
Because the CASE statement only produces one column I am having to repeat Test1 and then nest Test2 in the ELSE portion of the second iteration of Test1. I was hoping that someone might have a different approach that would reduce the amount of testing
being performed in the SQL. I did see the post at
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a0e45c7d-eedd-41ca-89f9-163c4dadc1a7/ that doesn't offer much hope but I thought I'd ask.
For what it's worth I'm posting the heart of my logic below. The test is performing bitwise comparisons to determine (Test1:) If the currently logged in user has access to a document and, if not (Test2) what they need to do to gain access. @Access
will end up as a parameter in the stored procedure once I finalize this.
DECLARE
@Access tinyint
= 1
SELECT
Holding.HoldingID,
ShortTitle, Access,
CASE
WHEN @Access
&[Access]
= @Access
THEN
(URL
+ [FileName])
ELSE
null
END
AS [HoldingURL],
CASE

Hi,I'm trying to create a dataset linking to Oracle, but I'm having problems getting a case statement to work in the query.It works fine in SQLPlus, Sql developer.When I try and add the statement it either puts single quotes around the statement, or throws the error 'Invalid or missing expression'.Here is the query in question:SELECT DISTINCT (CASE WHEN DEPARTMENT.DESCRIPTION LIKE '%Full Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Full Time') WHEN DEPARTMENT.DESCRIPTION LIKE '%Part Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Part Time') ELSE DEPARTMENT.DESCRIPTION END) AS DEPARTMENT
FROM COURSE, COURSE_INSTANCE, COURSE_LEVEL, DEPARTMENT
WHERE COURSE.OBJECT_ID = COURSE_INSTANCE.COURSE AND COURSE.COURSE_LEVEL = COURSE_LEVEL.OBJECT_ID AND
COURSE.DEPARTMENT = DEPARTMENT.OBJECT_ID

Hello,
I have a field A and B. I want A to be filterered based a condition
When B= 2 then A should not be in (1,2,3,4)
When B=1 then give me back all of A (no filters)
I am trying to do something like:
Case when B=2 then A Not in (1,2,3,4) else A
How do I incorporate this in my case statement? Or do I need some other way to do this? Please help.
Thanks to all in advance!
KJ

Hi all,
I have quite a few of the below in my query (not the same case statement but similar):
CASE WHEN [funcyion_key] <> 2 THEN SUM(portfolio_amt]) ELSE 0 END as portfolio_amt
Is there a better way of doing the above?
please help

i try to use a case-statement in the IN-Statement of my WHERE-Statement. The problem is, that my statement doesn't work when i want to return two values. When I return one value it works correctly. Here my code:

I have a sql datasource with the ControlParameter called ddlDropDownList.

2 questions:

1. Is my syntax below ok, I am getting a runttime error that the syntax is incorrect.

2. I have the select statement set up the control parameter to get the selected value from the dropdownlist as shown below, is that syntax correct? especially the "selectedItem.value" for the propertyname property. thx

select fieldOne, fieldTwo from someTable where fieldOne IN
CASE WHEN @ddlDropDownList='valueABC' THEN ('ABC') WHEN @ddlDropDownList='valueDEF' THEN ('DEF')
ELSE ('ABC','DEF') END