Need help with code
-
Table Parts has PartID as identity Table Orders has a field PartID as a foreign key As the stock of a part drops below OrderLevel it gets ordered and delivered. So many orders for this part. Come end of the month I need a list of Parts where Stock < OrderLevel, but no outstanding Order exists. This was easy using SQL and Datasets - just delete the row in the dataset if there is an Order for that PartID where Received < Ordered. But if I use
neededParts = from np in MainMenu.db.Parts
where np.Stock < np.OrderLevel
orderby np.StoreNumber
select np;and then identify PartID's that need to be removed, how do I do the removal? I could add a field to Parts, AreNeeded, and set that flag and then put that in the where statement. Doesn't seem very elegant.
-
Table Parts has PartID as identity Table Orders has a field PartID as a foreign key As the stock of a part drops below OrderLevel it gets ordered and delivered. So many orders for this part. Come end of the month I need a list of Parts where Stock < OrderLevel, but no outstanding Order exists. This was easy using SQL and Datasets - just delete the row in the dataset if there is an Order for that PartID where Received < Ordered. But if I use
neededParts = from np in MainMenu.db.Parts
where np.Stock < np.OrderLevel
orderby np.StoreNumber
select np;and then identify PartID's that need to be removed, how do I do the removal? I could add a field to Parts, AreNeeded, and set that flag and then put that in the where statement. Doesn't seem very elegant.
So you want to get parts that have no matching row in the Orders table. Here's a try:
from np in MainMenu.db.Parts
where
np.Stock < np.OrderLevel
&&
(
from o in MainMenu.db.Orders where o.PartID == np.PartID select o
).Count() == 0
orderby np.StoreNumber
select np;Hope that helps. Regards,
Syed Mehroz Alam My Blog | My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein -
So you want to get parts that have no matching row in the Orders table. Here's a try:
from np in MainMenu.db.Parts
where
np.Stock < np.OrderLevel
&&
(
from o in MainMenu.db.Orders where o.PartID == np.PartID select o
).Count() == 0
orderby np.StoreNumber
select np;Hope that helps. Regards,
Syed Mehroz Alam My Blog | My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert EinsteinPartial solution! There will be lots of entries where a specific PartID was ordered and delivered. But now they need ordering again. As written, only allows parts that have never been ordered. So needs one more clause only zapping PartID's that have oo.Ordered == oo.Received So I have changed it to
neededParts = from np in MainMenu.db.Parts
where np.Stock < np.OrderLevel
&&
(
from oo in MainMenu.db.Orders
where (oo.PartID == np.PartID) && (oo.Ordered==oo.Received)
select oo
).Count() == 0
orderby np.StoreNumber
select np;and it works just the way I want. Thanks for the help. It gives the solution to other posiible scenarios as well.