Solved

Linq to SQL query, need to filter based on child/associated table?

Posted on 2010-09-06
15
799 Views
Last Modified: 2012-06-27
I have a series of dropdown lists that contain all possible options held on my project table. Each time a dropdown is changed, I kick off a "refilter" that then updates all the other dropdowns so that the options in the boxes always reflect the results matching all the dropdowns - it's a dynamic filter basically.

Now the project table has an associated table - projectVersions - and this is a one to many link as there can be up to 6 versions. I can successfully filter on fields in the version table (a kind of "get me all projects where ANY of the versions associated with that project contain X" query) using the "any" linq function by doing this:

...
And ((lf.sizeCapacity = -1) Or (p.tblProjectVersions.Any(Function(t) t.sizeCapacity = lf.sizeCapacity)))

Open in new window

...

but there is one more complexity where there is also an associated table to projectVersions called marketdestinations, so each version can have up to 5 destinations... I need to filter based on a dropdown for a market destination, so if I choose "Australia" for example, the project list returns all projects where any of that project's versions has one of its market destinations set to Australia.

I've tried doing this like this:
...And ((lf.marketDestination = -1) Or (p.tblProjectVersions.Any(Function(t) t.tblProjectVersionMarketShares.Any(Function(tt) tt.msID = lf.marketDestination))))

Open in new window


where I'm doing an "any" within an "any" but it doesn't seem to work, so I'm probably doing something wrong...

The full filter query looks like this, and works apart from the market destination filter.
        rc = (From p In pdc.tblProjects _
                             Where ((lf.liquidClass = "-1") Or (p.LiquidClass = lf.liquidClass)) _
                             And ((lf.liquidType = "-1") Or (p.LiquidType = lf.liquidType)) _
                             And ((lf.brand = "-1") Or (p.ParentBrand = lf.brand)) _
                             And ((lf.brandVariant = "-1") Or (p.brandVariant = lf.brandVariant)) _
                             And ((lf.marketDestination = -1) Or (p.tblProjectVersions.Any(Function(t) t.tblProjectVersionMarketShares.Any(Function(tt) tt.msID = lf.marketDestination)))) _
                             And ((lf.sizeCapacity = -1) Or (p.tblProjectVersions.Any(Function(t) t.sizeCapacity = lf.sizeCapacity))) _
                             And ((lf.region = -1) Or (p.tblAccounts.tblRegions.RegionID = lf.region)) _
                             And ((lf.user = Guid.Empty) Or (p.UserAccount = lf.user)) _
                             And p.isDeleted = False _
                             Select p).Count

Open in new window

0
Comment
Question by:carled
15 Comments
 
LVL 29

Expert Comment

by:Kumaraswamy R
ID: 33615121
0
 
LVL 1

Author Comment

by:carled
ID: 33615632
rkworlds - I appreciate you taking the time to participate in my question but whereas the answer to my question *may* lie somewhere in the thousands of words on those pages you provided links to, it's a bit like telling someone to look in an entire library as the book they need is somewhere in there... :-)

If you can direct me specifically to the section I need to do what I'm trying to do that would be a great help, thanks.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33617657
Unfortunately, I am getting lost in all the words.  It is difficult for me to see the entities, and their associations.  It might be easier to see, if you should show the designer, and the associations.
0
 
LVL 1

Author Comment

by:carled
ID: 33624546
Thanks TheLearnedOne.  I feel that may confuse the issue too due to the huge number of fields involved.  To simplify:

tblProjects
   ProjectID
   ProjectTitle
   ...
LINKS TO: (on ProjectID, one to many)
tblProjectVersions
   versionID
   projectID
   versionTitle
   sizeCapacity
   ...
LINKS TO: (on VersionID, one to many)
tblMarketShares
   mktShareID
   versionID
   country
   sharePercentage
   
The set of filters simply allows me to define which projects are returned in the list.  Several items are direct fields on the projects table and they're no problem to find.  Several other items are on the version table and I can use the example function of t and ".any" as shown in my original post to get projects where any of the versions contain the searched field.  Where I fall over is on the marketshare table and it's a case of finding projects where any of the marketshare records for any of the versions is set to the required value, so it's a ".any" within a ".any" in effect.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33625671
I think that a query with joins might serve your purpose.  If you will pardon my code (I think in C#, not VB.NET, when it comes to LINQ), I think it would be something like this:

var q = from project in pdc.tblProjects
             join version in pdc.tblProjectVersions on project.ProjectID equals version.ProjectID
             join market in pdc.tblMarketShares on version.VersionID equals market.VersionID
             where version.sizeCapacity == -1
             select project;
0
 
LVL 1

Author Comment

by:carled
ID: 33625729
Thanks but that's not going to get me past the problem I have at present.  I'm fine with anything on the first associated table by doing this:

       rc = (From p In pdc.tblProjects _
                             Where ((lf.sizeCapacity = -1) Or (p.tblProjectVersions.Any(Function(t) t.sizeCapacity = lf.sizeCapacity))) _
                             And p.isDeleted = False _
                             Select p)

as that will go and get all projects where any one of those projects' associated versions has a sizecapacity field set to the required value (or it is ignored if set to -1)

However with the marketshare, this is a one to many of a one to many.  As I said in the first post, I tried doubling up the "any" code but it doesn't work:

       rc = (From p In pdc.tblProjects _
                             Where ((lf.sizeCapacity = -1) Or (p.tblProjectVersions.Any(Function(t) t.sizeCapacity = lf.sizeCapacity))) _
                             And ((lf.marketDestination = -1) Or (p.tblProjectVersions.Any(Function(t) t.tblProjectVersionMarketShares.Any(Function(tt) tt.msID = lf.marketDestination)))) _
                             And p.isDeleted = False _
                             Select p)

So in the above example I'm trying to say, "go and get me any project where any one of the 5 marketshares of any of the associated versions is set to 'X' " (or if marketshare isn't currently being filtered on (-1) it's ignored.

Bear in mind that there are 8 dropdowns for filtering (see full code of query at bottom of original post) and so it's an accumulative filter each time a value is set..

Hope that helps clarify a bit?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33627295
Please clarify what you mean by "However with the marketshare, this is a one to many of a one to many"...
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:carled
ID: 33627756
I mean that, as you can see from my simplified table diagram, there is one project which has up to 6 versions, each of which have up to 5 marketshares. So there could be up to 30 marketshare records with the same parent projectID (although this is not on the marketshare file as they belong to versions not projects) and there could be up to 6 versions for each project.

So there is a one to many from project to versions and another one to many from versions to marketshare.  Therefore a one to many of a one to many...  does that help?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33628118
I prefer a picture, since it really does save me a thousand words *GRIN*. What you describe are two simple join conditions, as shown in the data model.
Snapshot.png
0
 
LVL 1

Author Comment

by:carled
ID: 33628416
You may well be right, however how would I then alter my main query as shown in the first post to use joins instead of the ".any" function please?
0
 
LVL 1

Author Comment

by:carled
ID: 33641590
Hi ThelearnedOne (r anyone else, please!)  if you could just help me out with adapting what I have so far to use the joins you mention that would be fantastic, thanks...
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 33642026
I'm sorry that I was delayed by my own production issues, and I am not the best with VB.NET LINQ syntax.

Here is my take on a sample.  You should be able to understand, and extend for your needs.
Dim query = From project In pdc.tblProjects

             Join version In pdc.tblProjectVersions On project.ProjectID Equals version.ProjectID

             Join market In pdc.tblMarketShares On version.VersionID Equals market.VersionID

             Where version.sizeCapacity = -1

             Select project

Open in new window

0
 
LVL 1

Author Comment

by:carled
ID: 33642038
No worries on the delay - didn't want to just get caught up myself and forget about it. Don't worry about vb/c# I can translate. Many, many thanks - will go off and have a play and see if  understand what you've suggested, thanks.
0
 
LVL 19

Expert Comment

by:Amandeep Singh Bhullar
ID: 34008456
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now