linq case statement

Question

I need some help with CASE statements in linq (c#):

osc_products.products_quantity =
      CASE 
         WHEN itempromoflag <> 'N' THEN 100000
         WHEN itemcat1 IN ('1','2','31') AND itemsalestatus = 'S' THEN 100000
         WHEN itemsalestatus = 'O' THEN 0
         ELSE cds_oeinvitem.itemqtyonhand - cds_oeinvitem.itemqtycommitted 
      END

My start at converting to linq, (I'm still learning):

cdsDBDataContext db = new cdsDBDataContext();
  var query = from items in db.cdsItems
              where items.ItemHandHeldFlag.Equals("Y") && 
              items.ItemQtyOnHand -  items.ItemQtyCommitted > 0
  select items;

This query updates stock status from production to a commerce site.

score 134 · Accepted Answer · edited Aug 07 '17 at 15:44

134

If its just the CASE statement in LINQ your after (read your comment) then an example of this is...

Int32[] numbers = new Int32[] { 1, 2, 1, 3, 1, 5, 3, 1 };

var numberText =
(
    from n in numbers
    where n > 0
    select new
    {
        Number = n,
        Text = 
        (
            n == 1 ? "One" :
            n == 2 ? "Two" :
            n == 3 ? "Three" : "Unknown"
        )
    }
);

edited Aug 07 '17 at 15:44

Russia Must Remove Putin

374,368
89
403
331

answered Jun 01 '09 at 18:38

Worked perfectly to resolve duplicate dictionary key solution. Dictionary inputLookup = inputList .Select(x => new KeyValuePair(x.Split(','), x)) .Where(r => r.Key.Length > 2) .ToDictionary(y => ( y.Key.Length == 3 ? y.Key[1] : y.Key[1] + '#' + y.Key[2] ) , y => y.Value); – Galactic Aug 14 '21 at 18:38

score 5 · Answer 2 · edited Feb 24 '18 at 19:59

5

Here's my progress so far, not working at all yet, but is a start:

var query2 = from items in db.cdsItems
             where items.ItemTrackingCode.Equals("A") && (items.ItemQtyOnHand - items.ItemQtyCommitted) > 0
             select new  {
                           items,
                           qty =
                                 (
                                    items.ItemPromoFlag.Equals("1") ? "100000" :
                                    items.ItemCat1.Equals("1") ? "100000" :
                                    items.ItemSaleStatus.Equals("O") ? "0" :
                                    (items.ItemQtyOnHand - items.ItemQtyCommitted).ToString
                                 )
                         };

This syntax seems so awkward to me... I might just pass-thru sql.

edited Feb 24 '18 at 19:59

Eray Balkanli

7,752
11
48
82

answered Jun 01 '09 at 21:27

Scott Kramer

1,711
3
24
37

how do you do a not equals? items.ItemPromoFlag.Equals("1") != does not work – Scott Kramer Jun 01 '09 at 21:29
@Scott: I think you should use the == and != operators instead of .Equals(). It's way more readable. – R. Martinho Fernandes Jun 04 '09 at 22:16
i'll try, I think the compiler complains however-- – Scott Kramer Jun 04 '09 at 22:19

score 2 · Answer 3 · answered Jun 01 '09 at 18:33

First, select the Items that you want to update. Then, update them in regular C#. Submit changes.

    var q = from osc in MyDataContext.osc_products
            join cds in cds_oeinvitem on osc.products_model equals cds.itemno into p
            where osc.Itemwebflag == 'Y'
            select p;

    foreach (var item in q)
    {
        if (item.itempromoflag != "N")
            item.products_quantity = 100000;
        else if ((new[] { 1, 2, 31 }.Contains(item.itemcat1)) && (item.itemsalestatus == 'S'))
            item.products_quantity = 100000;
        else if (item.itemsalestatus == 0)
            item.products_quantity = 0;
        else
            item.products_quantity = item.itemqtyonhand - item.itemqtycommitted;
    }

    MyDataContext.SubmitChanges();

score 1 · Answer 4 · answered Jun 01 '09 at 18:35

1

use your single UPDATE statement in a stored procedure, will be better than doing a loop of updates on the application server.

answered Jun 01 '09 at 18:35

KM.

101,727
34
178
212

So you're saying don't use linq for this problem? – Nathan Koop Jun 01 '09 at 18:37
@Nathan Koop, if your db access is through linq, use linq to call the procedure. the performance difference of a Set based UPDATE vs. a looping linq UPDATE will be significant – KM. Jun 01 '09 at 18:39

score 0 · Answer 5 · answered Jun 01 '09 at 18:33

0

You are performing a bulk update, but link is purely a querying and object selection tool. Use the proper tool for the job...which in this case is definitely the database server.

answered Jun 01 '09 at 18:33

jrista

32,447
15
90
130

not really, this dumps to xml now, I'd leave the sql statement if that was the case!!, also it's not a complete sql statement, it's updating from sql2005 --> mysql – Scott Kramer Jun 01 '09 at 19:18
Well, regardless of how many database servers are involved or anything like that...if the UPDATE statement (fragment or otherwise) is what your trying to do...thats definitely a bulk job. OR mappers (which is what LINQ to SQL is) are designed to handle object to relational mapping. They are not designed to handle bulk processing, and can severely impact the performance of such operations. Its best to leave bulk processing to the tools that do them best...which, most of the time, is a database server (or in your case, two servers.) – jrista Jun 01 '09 at 19:24
ok, good info, but I'm not bulk UPDATEing, I removed that for clarity – Scott Kramer Jun 01 '09 at 19:26
Well, in that case, disregard my answer. :P – jrista Jun 01 '09 at 20:05
http://stackoverflow.com/questions/445033/use-linq-to-generate-direct-update-without-select – Spence Jun 04 '09 at 22:29

score 0 · Answer 6 · answered Jun 01 '09 at 18:41

0

There is no "Update" statement in Linq (whichever flavor you use, be it LinqToSQL or LinqToEntities).

Linq strictly provides a querying language.

If you are using LinqToSQL and want to update data, you need to first query the context for the items you need to update, then loop over them to change their property and finally to call SubmitChanges to save the changes to the database.

answered Jun 01 '09 at 18:41

Denis Troller

7,411
1
23
36

I just need help learning the CASE equiv in LINQ, I edited my question & remove the UPDATE – Scott Kramer Jun 01 '09 at 19:20

linq case statement

6 Answers6

Linked

Related