CODEWITHSUNDEEP
c#visual-studiolinqlinq-to-sql
Penguen
Penguen

Reputation: 17278

How can i use in ( select method in linq?

how can i write "in( select" method in linq?

I'm trying to convert : 

UPDATE ActualAmountsByLocation SET isCustomerItem=1 WHERE ItemBarcode IN (SELECT barcode FROM StockMaterials WHERE barcode=@Barcode AND ownership=1)

I've tried like this:

    Array stockMaterials = ( from s in stockMovementCtx.StockMaterials where s.barcode == Barcode && s.ownership ==1 select s).ToArray();
                           actualAmountsByLocation = (from a in stockMovementCtx.ActualAmountsByLocations
                                                       where a.ItemBarcode.Contains(stockMaterials)
                                                      select a).First();

Upvotes: 0

Views: 210

Answers (6)

Serkan Hekimoglu
Serkan Hekimoglu

Reputation: 4284

Making update on LinQ is so easy thing, check the code block below for example.

var user = (from s in dataContext.USERS

Calling Users Table, and setting where condition.

where s.UserId.Equals(id) select s).FirstOrDefault();

I want to make change on user email.(Dont forget this: If you call with ToList(), you need to control list count to avoid exception for ex: if(user.Count > 0)..)

Updating user:

s.EmailAddress = [email protected];
dataContext.SubmitChanges();

and your data will be updated after SubmitChanges();

Upvotes: 0

Nate Pinchot
Nate Pinchot

Reputation: 3318

Hopefully this code example below is helpful for you

// these are using linq methods
var barcodes = stockMovementCtx.StockMaterials
                 .Where(s => s.barcode == Barcode && s.ownership == 1)
                 .Select(s => s.barcode);
var amounts = stockMovementCtx.ActualAmountsByLocations
                .Where(a => barcodes.Contains(a.ItemBarCode))
                .FirstOrDefault();
// if you would like to use the query expressions instead, here they are
//var barcodes = from s in stockMovementCtx.StockMaterials
//               where s.barcode = Barcode && s.ownership == 1
//               select s.barcode;
//var amounts = (from a in stockMovementCtx.ActualAmountsByLocations
//              where barcodes.Contains(a.ItemBarCode.Contains)
//              select a).FirstOrDefault();

// helpful to use FirstOrDefault if you are not sure that the query will return a result
if (amounts != null) {
  // change value
  amounts.IsCustomerItem = 1;
  // update database
  stockMovementCtx.SubmitChanges();
}

Upvotes: 0

Mau
Mau

Reputation: 14468

    var stockMaterials = (from s in stockMovementCtx.StockMaterials
                          where s.barcode == Barcode && s.ownership == 1
                          select s).ToArray();
    var actualAmountsByLocation = (from a in stockMovementCtx.ActualAmountsByLocations
                                   where stockMaterials.Contains(a.ItemBarcode)
                                   select a).First();

Upvotes: 0

Andy
Andy

Reputation: 46354

This what you're looking for?

ActualAmountsByLocation = StockMaterials.Where(s => s.barcode == Barcode && s.ownership == 1).ToArray();

Upvotes: 0

tvanfosson
tvanfosson

Reputation: 532435

To get an IN query you need to reverse the sense of the contains. Also, no need to materialize the first query, IMO.

var stockMaterials = from s in stockMovementCtx.StockMaterials
                     where s.barcode == Barcode && s.ownership ==1
                     select s;
actualAmountsByLocation = (from a in stockMovementCtx.ActualAmountsByLocations
                           where stockMaterials.Contains( a.ItemBarcode)
                           select a).First();

Upvotes: 2

Julien Lebosquain
Julien Lebosquain

Reputation: 41213

You're almost there. Remove the .ToArray call to prevent the query from being executed directly, and make your stockMaterials variable of type IQueryable<StockMaterial>.

Upvotes: 1

Related Questions