LINQ & CASE

SecretSoftware
Code to live, but Live to code.

HI
Is there an equivalent CASE Operator in LINQ?

As in @S = CASE....

If not, is it going to be implemented?

SecretSoftware
Code to live, but Live to code.

at least support for selection based on multiple where conditions?

So I can say the following:

from a in b
from c in d
where a.CustomerID != c.CustomerID
select new {a,c}
where a.CustomerID == c.CustomerID
select new {a.CusttomerID, c.ProductType}
where...
select ...;

This is so you can select different things based on the condition given.

I want something to assume values based on the where clause, or based on a CASE like in SQL.

mattwar

C# and VB already have an equivalent operator to the SQL CASE operator. C# has the ternary/conditional operator which is the 'x ? y : z' operator. VB has the IIF operator, 'IIF(x,y,z)'

SecretSoftware
Code to live, but Live to code.

mattwar wrote:
C# and VB already have an equivalent operator to the SQL CASE operator. C# has the ternary/conditional operator which is the 'x ? y : z' operator. VB has the IIF operator, 'IIF(x,y,z)'

Yes, I am aware of that, but what If you want to select from the same source based on cases.

select new { x = Case customerID ==1: assign, Case ProductValue == 20: assign, etc...}

so I can give X values based on the date in the source.

Now you have to do this, get a new anonnymous type, and then query the query again. I want to do this stuff in 1 query rather than multiple queries.

var q1

var q2 that queries q1.

i want only q1 and to be able to select different results based on the data that is there in the data source.

One way to do this is to allow us to use multiple where selects, so

from ..
where..
select ..
where ..
select ..

just like we can do multiple froms, we want multiple selects, or some other way to select based on some multiples of conditions. and then the appropriate where..select, would get triggered based on the data matching.

Just suggestion. Perhaps MS has other syntactically better ways of doing this.

odujosh
Need Microsoft SUX now!

Do a Group By Query.

Foreach over the set of groups pass the group through a standard switch statement that handles a ienumerable<T> where T is the type of the object you are grouping.

SecretSoftware
Code to live, but Live to code.

odujosh wrote:

Do a Group By Query.

Foreach over the set of groups pass the group through a standard switch statement that handles a ienumerable<T> where T is the type of the object you are grouping.

I want to do it with in the query. Just like SQL CASE . in effect send SQl Server SQL Statement with CASE In it.

odujosh
Need Microsoft SUX now!

While I have never used Case since usually don't handle items in batch. There is no V1 plans that I have seen for doing Case. Although there are several work arounds for this.

The main reason I think is every domain would have to implement the feature. I am very sure this feature is an extreme edge case. There are plenty of ways in SQL to get around not using a CASE as I am sure you know.

When thinking about LINQ and why did they not add X. Ask yourself does this make sense in LINQ to XML, LINQ to Amazon, or LINQ to your domain.

SecretSoftware
Code to live, but Live to code.

odujosh wrote:

While I have never used Case since usually don't handle items in batch. There is no V1 plans that I have seen for doing Case. Although there are several work arounds for this.

The main reason I think is every domain would have to implement the feature. I am very sure this feature is an extreme edge case. There are plenty of ways in SQL to get around not using a CASE as I am sure you know.

When thinking about LINQ and why did they not add X. Ask yourself does this make sense in LINQ to XML, LINQ to Amazon, or LINQ to your domain.

Is it not the case that LINQ is extensible because Each LINQ TO X is indipendent of the other? So I want to be able to Sumbit a SQL String with CASE init.

For now I had to write a SProc that does that for me, but I had to rearrange stuff in C# code.

CASE would have been good. I wanted to be able to Assign stuff based on the actual values.

select new { x = Case y assing d , case m assing y end, etc..}

that way Its more robust for me. It makes sense in LINQ To XML , where I can select to assing annonymus type properites, based on the data.

edmund

Re: lack of Case or Conditional statements in LINQ

I have been using XQuery and XSLT2.0 for some very complex transformations and recently tried to translate these into LINQ. But the lack of Case or If statements forced me into some very ugly constructs.

For me this limitation is a complete show-stopper for LINQ. Anyone similarly frustrated will find that XQuery1.0 is much easier to learn and much more versatile for XML querying.

The Saxonica implementation of XQuery is especially good: it has extensions for indexing that give massive performance enhancements.

phreaks

JChung2006 wrote:

You can use lambda expressions in lieu of CASE syntax.

IEnumerable values = Enumerable.Range(1, 3);
Func switchFunc = x =>
{
    switch (x)
    {
        case 1: return "ONE";
        case 2: return "TWO";
        default: return "***";
    }
};
var switchedValues = values.Select(value => switchFunc.Invoke(value));
foreach (string value in switchedValues)
{
    Console.WriteLine(value);
}

A POF (Plain Ol' Function) would also suffice.

++

JChung2006

You can use lambda expressions in lieu of CASE syntax.

IEnumerable<int> values = Enumerable.Range(1, 3);
Func<int, string> switchFunc = x =>
{
    switch (x)
    {
        case 1: return "ONE";
        case 2: return "TWO";
        default: return "***";
    }
};
var switchedValues = values.Select(value => switchFunc.Invoke(value));
foreach (string value in switchedValues)
{
    Console.WriteLine(value);
}

A POF (Plain Ol' Function) would also suffice.

edmund

Thanks for your suggestion. This is an option for simple cases but an extremely complicated option if you want to branch to more code within the query.

Until Microsoft get this deficiency fixed, LINQ cannot be a serious alternative to XQuery or XSLT2.0.

evildictaitor
if( !succeed( try() ) ) { while(true) try(); }

edmund wrote:
Thanks for your suggestion. This is an option for simple cases but an extremely complicated option if you want to branch to more code within the query.

Until Microsoft get this deficiency fixed, LINQ cannot be a serious alternative to XQuery or XSLT2.0.

If you're having much more complex switches over your database that is suggestive that you should be splitting your query into smaller sub-queries (or a stored procedure) or should be refactoring your database into something more logical.

JChung2006

edmund wrote:
Until Microsoft get this deficiency fixed, LINQ cannot be a serious alternative to XQuery or XSLT2.0.

Sorry, but I disagree. Being able to invoke an arbitrary function as part of your LINQ expression makes it rather, well, expressive. You could even write an LINQ to XSLT or LINQ to XQuery provider if you absolutely must have your angle brackets though.

lancefisher

It's possible to effectively write CASE statements in LINQ using the ternary operator like this:

var qry = from c in db.Customers
   select new {
      CustomerName = c.CustomerId == 1 ? "Joe" :
                                    c.CustomerId ==2 ? "Bob" :
                                    c.CustomerId == 3 ? "Tom" :
                                    "Unknown"
   };

This will actually generate a SQL CASE statement like:

SELECT
CASE
      WHEN c.CustomerId = 1 THEN 'Joe'
      WHEN c.CustomerId = 2 THEN 'Bob'
      WHEN c.CustomerId = 3 THEN 'Tom'
      ELSE 'Unknown'
END CustomerName
FROM
Customers c

I wrote up a blog post on it here:
http://lancefisher.net/blog/archive/2008/05/07/linq-to-sql---case-statements.aspx

Sampy
This will be the sixth time we have destroyed it and we have become exceedingly efficient at it

Maybe this is just the examples I'm seeing but this looks like you should be joining to a look up table. You can do that in the DB or after you get the items back to the client.

lancefisher

Sampy wrote:
Maybe this is just the examples I'm seeing but this looks like you should be joining to a look up table. You can do that in the DB or after you get the items back to the client.

Right, I knew that was coming. This is just an example of how to generate the CASE statement, not the best practice in this situation. It is useful in other situations where you might not have a lookup table, or a lookup table doesn't make sense.

Suppose you wanted to show different text based on some numerical range:

var qry = from o in orders
                select new {
                   Message = o.TotalAmt < 10 ? "Cheapo" :
                                     10 <= o.TotalAmt && o.TotalAmt < 100 ? "Moderate Spender":
                                     "Big Spender" //for orders $100 or more
                }

That kind of problem is not easily solved with a lookup table. Also, there are times when you might not be able to add lookup tables, and you just have to deal with the database as it is. CASE is a good tool to have.

Sampy
This will be the sixth time we have destroyed it and we have become exceedingly efficient at it

You can always perform the join with LINQ to Objects after you have retrieved the items from the database. You can even use an array of anonymous types to do so.

I don't want to try and say "CASE is useless! You don't need it!" but rather that I don't think the lack of CASE as an obvious part of LINQ to SQL (or just LINQ in general) is a huge problem. If you really need it, you can use the trinary operator trick mentioned above but there are other ways to achieve the same effect.

RomeInterrupted

In reply to Sampy

#May 8th, 2008 @ 10:59 AM

In VB, use Microsoft.VisualBasic.Switch function; LINQ will translate into CASE statement.

LINQ &amp; CASE