Linq-to-SQL "order by" child table values and return custom type
Simple set of 4 tables that link together: Projects -> Versions -> Marketshares -> Countries
Linq to SQL class has been set up to that each subsequent table as shown above is an entity set of the one before with ALL being one to many from parent to child except Marketshares -> Countries which is one to one.
If a list (of project) is returned then it's possible to iterate through the other tables on the linked values and all works well.
However, one particular query requires that I return only those projects where a particular element matches a filter setting on the marketshare table. This is fine and I can happily select the matching projects. Unfortunately I need to return a custom list and this means I need access to the values in marketshare and countries. But as these are one-to-many relationships, I cannot directly do something like:
p.project.version.marketsh
So basically there is a value in marketshare called marketdestination and this links to an id in countries from which I pull the countryname. Assuming that I have the query correct (and the query below works for selection) then how do I specify that I want to sort the results by countryname from countries and return marketdestination and countryname in the query please?
I tried using "firstordefault" but it doesn't work...
Linq to SQL class has been set up to that each subsequent table as shown above is an entity set of the one before with ALL being one to many from parent to child except Marketshares -> Countries which is one to one.
If a list (of project) is returned then it's possible to iterate through the other tables on the linked values and all works well.
However, one particular query requires that I return only those projects where a particular element matches a filter setting on the marketshare table. This is fine and I can happily select the matching projects. Unfortunately I need to return a custom list and this means I need access to the values in marketshare and countries. But as these are one-to-many relationships, I cannot directly do something like:
p.project.version.marketsh
So basically there is a value in marketshare called marketdestination and this links to an id in countries from which I pull the countryname. Assuming that I have the query correct (and the query below works for selection) then how do I specify that I want to sort the results by countryname from countries and return marketdestination and countryname in the query please?
thisList = (From p In pdc.tblProjects _
Where p.tblProjectVersions.Any(Function(t) t.tblProjectVersionMarketShares.Any(Function(tt) tt.marketDestination = lf.marketDestination))) _ And p.isDeleted = False _
Order By p.tblProjectVersions.FirstOrDefault.tblProjectVersionMarketShares.FirstOrDefault.tblCountries.CountryName Ascending _
Select New genListItem With {.listID = p.tblProjectVersions.FirstOrDefault.tblProjectVersionMarketShares.FirstOrDefault.marketDestination, .listValue = .tblProjectVersions.FirstOrDefault.tblProjectVersionMarketShares.FirstOrDefault.tblCountries.CountryName}).Distinct().ToListI tried using "firstordefault" but it doesn't work...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER