linq case statement

Question

Hello,

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.

Thanks !!!

Answer 1

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();

Answer 2

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.

Answer 3

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

Answer 4

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"
        )
    }
);

Hope that helps :)

Answer 5

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.

Answer 6

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

Answer 7

http://bartdesmet.net/blogs/bart/archive/2008/03/30/a-functional-c-type-switch.aspx talks about implementing a functional switch case.