Click here to Skip to main content
15,882,278 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hey!

Hopefully, someone of you can help me to transfer a sql query to LINQ in vb.net:

SQL:
SQL
SELECT DISTINCT hal.EANHotelID
FROM            EANHotelsAttributeLink AS hal INNER JOIN
                         EANHotelsAttribute AS ha ON hal.AttributeID = ha.AttributeID INNER JOIN
                         EANHotels AS eh ON hal.EANHotelID = eh.EANHotelID
WHERE        (ha.GroupAmenityID IN (1, 4, 6)) AND (eh.RegionID = '1385')
GROUP BY hal.EANHotelID
HAVING        (COUNT(hal.EANHotelID) = 3)

thanks an advance
Cheers
Posted
Updated 11-Jun-14 5:40am
v3
Comments
Member 9500954 11-Jun-14 4:41am    
Hey guys! Someone here who can help me with this :)? Would be awesome! thanks
Andreas Gieriet 11-Jun-14 7:37am    
Sorry, this is not a gimme-code forum.
Andi
PS: Show what you did try so far and what the problem is.
Member 9500954 11-Jun-14 10:22am    
come on: giving and taking
dbrenth 11-Jun-14 8:54am    
Why does it have to be in LINQ? You could:
1. Create a view on the SQL Server and load the view.
2. Call the SQL directly from your program and load a data table.
3. Use LINQ to do the where clause, then post process to get the distinct items with 3 instances.
Thava Rajan 11-Jun-14 9:00am    
we need to know what type of object you used and what are the property names so please prove some of your linq code we will assist you

1 solution

hey!

thanks for your help
i will use stored procedures in the second phase of the project (thanks for the tip)

I found out the LINQ statement that is working fine for the moment:
VB
Dim amenityGroupIDs As String = "1,4,6"
Dim GroupAmenityList As New List(Of String)

Dim TempGroupAmenities() As String = amenityGroupIDs.Split(",")
For Each GroupAm As String In TempGroupAmenities
   GroupAmenityList.Add(GroupAm)
Next

Dim FilterHotelsPriceStarsRatingAmenities = (From hal In _db.EANHotelsAttributeLink Join ha In _db.EANHotelsAttribute On hal.AttributeID Equals ha.AttributeID Join eh In _db.EANHotels On hal.EANHotelID Equals eh.EANHotelID Where GroupAmenityList.Contains(ha.GroupAmenityID) And eh.RegionID = 1385 Group hal By hal.EANHotelID Into myGroup = Group Where myGroup.Count = GroupAmenityList.Count Select myGroup.FirstOrDefault).ToList

For Each Hotel In FilterHotelsPriceStarsRatingAmenities
   ViewBag.Test1 &= Hotel.EANHotelID
Next


Cheers
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900