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

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

    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

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

    william xifaras

    Wednesday, December 4, 2019 11:56 AM