none
Removing ONLY one item from EF table RRS feed

  • Question

  • I've got a SaleItems table with three snickers bars in it.  I want to remove only one item and no more.  In SQL I can use 

    Select TOP(1) *
    FROM [SaleItems]
    Where ItemName = 'SNICKERS EACH' AND SaleID = 5

    to get one item to remove but when I try to use DELETE I get an error

    DELETE TOP(1) *
    FROM [SaleItems]
    Where ItemName = 'SNICKERS EACH' AND SaleID = 5

    I'm trying to incorporate this into a entity.table.remove but not sure how to write the syntax.

     private void RemoveSaleItem(string name, int saleID)
            {
                Entities pse = new Entities();
              
                //Select TOP(1) from [SaleITems] Where ItemName = name AND SaleID = saleID
                pse.SaleItems.Remove(pse.SaleItems.Find());   
                pse.SaveChanges();
            }

    Monday, December 2, 2019 3:16 PM

All replies

  • Where do you get an error using DELETE TOP (1)? In SSMS or when you try using it in EF? What error do you get?

    Can you use a different key to exclude the row which you want to keep?

    Monday, December 2, 2019 3:52 PM
  • Hi Mimosa Arts,
    You can use the LINQ to query, then use the First method to get the first element.
    Here is a code you can refer to.

    var item = (from d in pse.SaleItems
                       where d.ItemName == "Report" and d.SaleID == "saleID"
                       select d).First();
    pse.SaleItems.Remove(item );
    pse.SaleItems.SaveChanges();

    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 3, 2019 6:32 AM
  • Use a Linq-to-Entities Query and FirstOrDefault which will return a default value if nothing is found.

    var saleItem = (from d in pse.SaleItems
                    where d.ItemName == "XXXX" and d.SaleID == "YYYY"
                    select d).FirstOrDefault();
    
    pse.SaleItems.Remove(saleItem);
    pse.SaleItems.SaveChanges();






    william xifaras


    Wednesday, December 4, 2019 11:56 AM