How to use 'Case' in Lightspeed

 Hi,

How can I acchive functionality like 'Case' in lightspeed.

I am able to do it in sql queries but not with lightspeed.

There is no CASE equivalent in LINQ.  CASE can be used from the core query API but with some limitations.  Specifically we support only the "searched CASE expression" (CASE WHEN...), not the "simple CASE expression" (CASE some_column WHEN...).

To use a searched CASE, use the .Function method, passing "CASE" as the function name, -1 as the thisPosition, and the when- and result-expressions in the order you want them to appear in the CASE:

// LightSpeed query expression

Entity.Attribute("MemberId").Function(-1, "CASE",
  Entity.Attribute("MemberId") == 41, "a",
  Entity.Attribute("MemberId") == 42, "b",
  "c")

// Resulting SQL

CASE
  WHEN Comments.MemberId = 41 THEN 'a'
  WHEN Comments.MemberId = 42 THEN 'b'
  ELSE 'c'
END

We're aware this is ugly -- it is intended only to support some internal LINQ scenarios and we currently have no plans to enhance it to improve usability.  There may be other ways of achieving your requirement depending on what you're trying to do.