Sorting a list of objects by another list of objects VB.NET LINQ

Question

So, here's a simplified version of the situation: I have Gyms in a database:

Gym: GymID, Name
GymAmenities: GymID, AmenityID
Amenities: AmenityID

So a Gym, can have 0 to many "Amenities". Along comes a user who prioritizes amenities that are important to him or her:

UserPrefAmenities: UserID, AmenityID, Ranking

Now when searching for a gym in a zip code, I want the search results to be in order of the user preferred amenities in order of rank...

gyms = (From g In db.Gyms Where g.Zip = thisRequest.Zip Order By g.GymAmenitys.Contains(From upa In thisUser.UserPrefAmenitys Order By upa.Rank)).ToList

Or something like that...

*note that running the above results in:

Unable to create a constant value of type 'UserPrefAmenity'. Only primitive types or enumeration types are supported in this context.

Gert Arnold · Accepted Answer · 2015-05-14 22:11:29Z

1

I think this should do it:

gyms = (
        From g In db.Gyms 
        Where g.Zip = thisRequest.Zip 
        Order By If((From ga in g.GymAmenitys
                     Join upa In db.UserPrefAmenitys On ga.AmenityID
                                                     Equals upa.AmenityID
                     Where upa.UserID = thisUser.UserID
                     Order By upa.Rank Descending
                     Select CType(upa.Rank, integer?)).FirstOrDefault(), 0)
       ).ToList

The idea is that you find a user's highest ranked UserPrefAmenity that shares an AmenityID of the Gym's amenities. If the user has no matching UserPrefAmenity the value 0 is taken as rank.

edited May 14, 2015 at 22:11

answered May 14, 2015 at 20:59

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jarrette Over a year ago

Getting "First operand in a binary if expression must be nullable or a reference type. Also, the "==" is supposed to be "=", right? for this part: (From ga In g.GymAmenitys Join upa In db.UserPrefAmenitys On ga.AmenityID Equals (upa.AmenityID) Where upa.UserID = thisUser.UserID _ Order By upa.Rank Descending Select upa.Rank).FirstOrDefault

Gert Arnold Over a year ago

Ah, the "==" is my C# dialect shining through. As for the exception, what is the type of Rank?

Gert Arnold Over a year ago

I think I fixed the exception (if Rank is an integer).

Collectives™ on Stack Overflow

Sorting a list of objects by another list of objects VB.NET LINQ

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related