How to apply Not In Linq?

Question

I have tow tables - OrderRequisition and Order. I can show all the records from OrderRequisition table using linq query:

var list = (from r in db.OrderRequisition 
          select new SalesOrderViewModel
                             {
                                 OrderId = r.OrderId ,
                                 OrderNo =  r.OrderNo 
                             }).ToList();

I want to show only those records from OrderRequisition table which are not included in Order table. Any clue

Thanks Partha

If you have the relation between `OrderRequisition` and `Order` in your model you could do `where r.Order == null` otherwise you should write a [left join in your linq](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) and check it. — Cleptus, Sep 20 '18 at 10:18

score 2 · Accepted Answer · answered Sep 20 '18 at 10:18

2

A simple approach that might be efficient enough because your database is able to optimize it:

var list = db.OrderRequisition
  .Where(or => !db.Order.Any(o => o.OrderId == or.OrderId))
  .ToList();

(skipped the SalesOrderViewModel initialization because not relevant for the question)

answered Sep 20 '18 at 10:18

Tim Schmelter

450,073
74
686
939

If the relation in in the model was created `where r.Order == null` could also be possible and the subselect would be avoided (plus easier to read code IMHO). – Cleptus Sep 20 '18 at 10:20
@bradbury9: it's still readable with the sub-select. But yes, if he wants null-order records then he could use that as well. If it was possible that there are `OrderId` that are not `null` but also not included in the `Order` table, then my query works but the `null` check not. Maybe he wants to check database consistency – Tim Schmelter Sep 20 '18 at 10:23
Or `where r.OrderId == null` if no relationships where created. Again avoiding the subselect. Do note that `OrderRequisition.OrderId` is most likely `int?` – Cleptus Sep 20 '18 at 10:27
@bradbury9: yes, but isn't it the same as what you've said in your previous comment? What if `OrderRequisition.OrderId` isn't `null` but not contained in the `Order`-table? If you want to check that you can't use the `null`-check. Sometimes you import records with foreign-keys and you have to check data integrity. Maybe this is what he's doing – Tim Schmelter Sep 20 '18 at 10:30
if `OrderRequisition.OrderId` isn't `null` but not contained in the `Order` table it has not data integrity and should be corrected (execute and update, set `null` those rows, alter the constraint to make it check the data) – Cleptus Sep 20 '18 at 10:34
@bradbury9: that's what i'm saying all the time. Sometimes you need to ensure data integrity of records(for example in temp tables). That's a classic importer use-case. Then the null-check doesn't help. – Tim Schmelter Sep 20 '18 at 10:36
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/180436/discussion-between-bradbury9-and-tim-schmelter). – Cleptus Sep 20 '18 at 10:37

How to apply Not In Linq?

1 Answers1