Click here to Skip to main content
15,354,958 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
Thava Rajan 11-Jun-14 9:01am
   
more over the distinct is not needed since you use group by clause in the select query

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
   
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