LINQ – Nested (address & city) query

I have a problem with a project. I’m trying to get a list of companies that are located in a specific country or city.

Table structure:

Company

CompanyID
CompanyName
etc…

CompanyAddressDetails (relation table)

Company_CompanyID
CorrespondingAddress_AddressID

CorrespondingAddress:

AddressID
StreetName
RegionID
etc…

Region

RegionID
RegionName
RegionRegionTypeID

RegionDetails (relation table)

RegionParent
RegionChild

So to find an address in example Stockholm (which has ID 1198 in the Region table), I would do:

var addresses = from c in db.CorrespondingAddress select c;
addresses = addresses.Where(s => s.RegionID.Equals(1198));

And to find a company in Stockholm I would do:

companyModel = from c in db.Company select c;
companyModel =  companyModel.Where(s => s.CorrespondingAddress.Any(x => x.RegionID.Equals(1198)));

But now I want to take into account the RegionDetails table (which has a parent, and child, for example: 1 (Sweden) is parent, and 1198 (Stockholm) is child etc)

How can I do to find a company which is located in Sweden, but has the ID 1198 (Stockholm) in its address row?

In plain SQL I would maybe do something like:

SELECT CompanyName FROM Company
LEFT JOIN CompanyAddressDetails ON (Company.CompanyID = CompanyAddressDetails.Company_CompanyID)
LEFT JOIN CorrespondingAddress ON (CompanyAddressDetails.CorrespondingAddress_AddressID = CorrespondingAddress.AddressID)
LEFT JOIN Region ON (CorrespondingAddress.RegionID = Region.RegionID)
WHERE CorrespondingAddress IN (SELECT RegionChild FROM RegionDetails WHERE RegionParent = 1)

Answers


First off, the following code can be refactored into one line:

companyModel = from c in db.Company select c;
companyModel =  companyModel.Where(s => s.CorrespondingAddress.Any(x => x.RegionID.Equals(1198)));

Can't you just nest another Any?

companyModel = db.Company.Where(s => s.CorrespondingAddress.Any(x => x.Region.Any(r => r.RegionDetails.Any(rd => rd.Parent == 1 && rd.Child == 1198)));

Edit

Given the following property of CorrespondingAddress:

public virtual Region Region { get; set; } 

And assuming Region has a property RegionDetails (making Region -> RegionDetails one-to-one):

public virtual RegionDetails RegionDetails{ get; set; } 

The following should work:

companyModel = db.Company.Where(s => s.CorrespondingAddress.Any(x => x.Region.RegionDetails.RegionParent == 1 && x.Region.RegionDetails.RegionChild == 1198)));

since there is no model for the details tables (many-to-many relationship tables), I solved it by using this method:

companyModel = companyModel.Where(s => s.CorrespondingAddress.Any(x => x.Region.RegionParent.Any(d => d.RegionID == region)));

Need Your Help

Boosting fields in Zend Search Lucene

php zend-framework zend-search-lucene

When I look at the Zend framework documentation, Zend Search Lucene has the ability to boost search terms.

Providing *hint* in text field in ios 7 as edittext field in android

objective-c ios7 uitextfield xcode5.0.1

I am creating a feedback form in an ios7 application and want to place few UITextFields which contain hints in the respective UITextField for name, phone number, email address, etc. I am using xcod...