CODEWITHSUNDEEP
c#sqllinq
Scott Kramer
Scott Kramer

Reputation: 1731

linq case statement

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.

Upvotes: 54

Views: 138551

Answers (6)

Scott Kramer
Scott Kramer

Reputation: 1731

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.

Upvotes: 5

user110714
user110714

Reputation:

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

Upvotes: 136

Denis Troller
Denis Troller

Reputation: 7501

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.

Upvotes: 0

KM.
KM.

Reputation: 103607

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

Upvotes: 1

jrista
jrista

Reputation: 32960

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.

Upvotes: 0

bruno conde
bruno conde

Reputation: 48265

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

Upvotes: 2

Related Questions