CODEWITHSUNDEEP
linqentity-frameworklinq-to-entities
SamJolly
SamJolly

Reputation: 6477

Some assistance with a Delete LINQ expression please

I am trying to write a LINQ query to maintain a distinct list of suppliers

Order
  - SupplierId


UniqueSupplierList
  - Id
  - SupplierId

Example

Order
   Supplier1
   Supplier1
   Supplier2

UniqueSupplierList
   1, Supplier1
   2, Supplier2

Now if I change Order3 so that:

Order
   Supplier1
   Supplier1
   Supplier1

I would need to delete UniqueSupplierList, record 2, to produce

UniqueSupplierList
   1, Supplier1

This is the delete I am struggling with and require help for. I realise the example is a little contrived, but it is the delete LINQ sample I am interested in.

There is no foreign key between the tables.

In SQL I might do something like:

delete from UniqueSupplierList where supplierid not in (Select SupplierId from Order where...)

Some LINQ code to start with:

db.UniqueSupplierList.Where(r.SupplierId!=????).ToList().ForEach(db.UniqueSupplierList.DeleteObject);

Many thanks in advance.

Upvotes: 0

Views: 28

Answers (1)

Corey Adler
Corey Adler

Reputation: 16137

I'm assuming that you also have an Orders collection in your database. In which case:

var supplierIDs = db.Orders.Select(o => o.SupplierID).Distinct();
var toDelete = db.UniqueSupplierList.Where(usl => !supplierIDs.Contains(usl.SupplierID)).ToList();

toDelete.ForEach(td => db.UniqueSupplierList.Remove(td));

db.SaveChanges();

Upvotes: 1

Related Questions