sql query to LINQ entities

Question

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 10-Jun-14 20:26pm

Member 9500954

Updated 11-Jun-14 5:40am

Nelek

v3

Add a Solution

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

Add a Solution

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

Member 9500954 · Accepted Answer · 2014-06-11T04:25:00

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

sql query to LINQ entities

1 solution

Solution 1

Add your solution here

Preview 0

sql query to LINQ entities

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us