The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

Svideo (21) [Avatar] Offline
#1
I'm writing an application where I'm using Linq to Datasets in VB 2008. I have two parent tables. The first contains the inventory. The second parent has prospect information to match to arriving new inventory. A email is sent when a new match is found. A child table in the prospect table has the inventory numbers (DLS_Number) of the products already sent to the prospect so they don't get resent.

What is the best way to filter the numbers already sent from being re-matched from the child table? I want to exclude them during the query.

For Each MyRow As DRDataSet.Lift_ProspectsRow _
In DRDataSet.Lift_Prospects.Rows

'Must not have been already sent
Dim SentNums = From MyChildRows In MyRow.GetSentLiftNumbersRows
SentNums = SentNums.Where(Function(O) O.IsSentLiftNumberNull = False _
And O.SentLiftNumber > 0)
‘this is the list of all the email numbers sent
Dim CompleteSentNums = SentNums.Select(Function(Z) _
New With {Z.SentLiftNumber})

Dim query = From Lift In DRDataSet.Lift_Info.AsEnumerable

'Must be active and for sale
query = query.Where(Function(I) I.IsActiveNull = False _
AndAlso I.Active = True)
query = query.Where(Function(I) I.IsOfferPendingNull = False _
AndAlso I.OfferPending = False)
query = query.Where(Function(I) I.IsSoldNull = False _
AndAlso I.Sold = False)
query = query.Where(Function(I) I.IsSalePendingNull = False _
AndAlso I.SalePending = False)
Dim CompleteQuery = query.Select(Function(Q) New With {Q!DLS_Number})

ProspectsDataGridView.DataSource = CompleteQuery.ToList

Next
Svideo (21) [Avatar] Offline
#2
Re: Excluding query matches from numbers in a child table
Looks like everyone is on vacation on this board. Does anyone have a board I might want to check to find a solution to my problem?

I have this ugly solution right now so I can continue, but there must be a more robust solution to this.

I'm really looking at a way to build a query and strip the numbers I don't want in the query.

Also I found a pretty good tool for free, google LINQPad.



>>> Ugly solution

For Each dls In CompleteSentNums

Dim MyNum As Integer = dls.SentLiftNumber
query = query.Where(Function(I) I.DLS_Number <> MyNum)

Next
jwooley (123) [Avatar] Offline
#3
Re: Excluding query matches from numbers in a child table
Please be patient. This forum is intended to be support for our book, but we try to answer general LINQ questions as we are able. The official support forums for LINQ are on MSDN:

LINQ General: https://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=123&SiteID=1
LINQ to SQL: https://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=2035&SiteID=1

In your case, there are a number of possible solutions. You might be able to do a Join where the object on the right side is null or use a Not Contains clause in the filter. Here's a possible implementation (using query expressions rather than Lambda's). I would recommend you check the alternative possibilities as they may perform better in your situation.

Dim SentNums = From MyChildRow In MyRow.GetSentLiftNumbersRows _
Where MyChildRow.IsSentLiftNumberNull = False AndAlso _
MyChildRow.SentLiftNumber>0 _
Select MyChildRow.SentLiftNumber

Dim query = From Lift in DRDataSet.Lift_Info _
Where Not SentNums.Contains(function(Lift) Lift.LiftNumber) AndAlso _
Lift.IsActiveNull = False AndAlso Lift.Active=True AndAlso _
Lift.IsOfferPendingNull = False AndAlso Lift.OfferPending = False AndAlso _
Lift.IsSoldNull = False AndAlso Lift.Sold = False AndAlso _
Lift.IsSalePendingNull = False AndAlso Lift.SalePending = False _
Select Lift.DLS_Number
Svideo (21) [Avatar] Offline
#4
Re: Excluding query matches from numbers in a child table
Thanks for the speedy reply, and sorry if I was short. This have been kicking me all weekend and I feel like I'm stuck on stupid.

I see where you are going on that, there is one error that I'm not sure how to correct in the

>Where Not SentNums.Contains(function(Lift) Lift.LiftNumber)<

Lambda expression cannot be converted to 'Integer' because 'Integer' is not a delegate type. C:UsersAdministratorDocumentsVisual Studio 2008ProjectsDRDataBaseDRDataBaseForm1.vb 1471 45 DRDataBase

I also like your talk on screenscraping.
jwooley (123) [Avatar] Offline
#5
Re: Excluding query matches from numbers in a child table
I'm sorry, try changing the where clause to

Where Not SentNums.Contains(Lift.LiftNumber))

It can be a tad tricky getting your head around the implementation of the Contains method at times.

A couple other options you may want to consider is using the .Except or Not .Exists methods. To perform this in a more set based method.

Jim
www.ThinqLinq.com
Svideo (21) [Avatar] Offline
#6
Re: Excluding query matches from numbers in a child table
Woo Woo now I'm cooking with gas!