CODEWITHSUNDEEP
sqlvb.netlinqentity-framework
USER_8675309
USER_8675309

Reputation: 893

How can I convert this SQL to VB.NET LINQ

I am having some real trouble converting this statement from SQL to LINQ.

DECLARE @radians float
SELECT @radians = 57.295779513082323 
SELECT 
    t1.*, 
    DistanceInMiles = 
        3963.0*acos(
            sin(t1.Latitude/(@radians))
            * sin(t2.Latitude/(57.295779513082323))
            + cos(t1.Latitude/(@radians))
            * cos(t2.Latitude/(@radians)) 
            * cos(t2.Longitude/(@radians) - t1.Longitude/(@radians))
        )
FROM ZipCodeData t1
JOIN ZipCodeData t2 ON (
    3963.0*acos(
        sin(t1.Latitude/(@radians)) 
        * sin(t2.Latitude/(57.295779513082323)) 
        + cos(t1.Latitude/(@radians)) 
        * cos(t2.Latitude/(@radians)) 
        * cos(t2.Longitude/(@radians) 
        - t1.Longitude/(@radians))
    ) < 100
)
WHERE t2.ZipCode='10001'
ORDER BY DistanceInMiles

I am working in VB.NET

I don't have any idea where to start

Dim results = z1 in Entities.ZipCodeDatas

Ok so I have a start, but seriously

EDIT:

Dim radians = 57.29577951309232
Dim results = From z1 In Entities.ZipCodeDatas
              z1.ZipCode
              Join z2 In Entities.ZipCodeDatas On z2.ZipCode Equals z1.ZipCode

First, I don't know how to create DistanceInMiles and I don't know how to create this second Join and I don't know how to Join tables with a Less than < clause

Edit 2:

Dim result = (
    From z1 In Entities.ZipCodeDatas
    Join z2 In Entities.ZipCodeDatas On z2.ZipCode Equals z1.ZipCode
    Where (
        3963.0 * Math.Acos(
            Math.Sin(z1.Latitude.Value / (radians)) _
            * Math.Sin(z2.Latitude.Value / (57.295779513082323)) _
            + Math.Cos(z1.Latitude.Value / (radians)) _
            * Math.Cos(z2.Latitude.Value / (radians)) _
            * Math.Cos(z2.Longitude.Value / (radians) - z1.Longitude.Value / (radians)) _
        )
    ) < radius
)

Upvotes: 2

Views: 133

Answers (1)

Nic
Nic

Reputation: 12846

Something like this:

Dim radians = 57.295779513082323
Dim result = ( _
    From t1 in Entities.ZipCodeDatas
    From t2 in Entities.ZipCodeDatas
    Where t2.ZipCode = "10001" _
    And 3963.0 * SqlFunctions.Acos( _
        SqlFunctions.Sin(t1.Latitude / (radians)) _
        * SqlFunctions.Sin(t2.Latitude/(57.295779513082323)) _
        + SqlFunctions.Cos(t1.Latitude/(radians)) _
        * SqlFunctions.Cos(t2.Latitude/(radians)) _
        * SqlFunctions.Cos(t2.Longitude/(radians) - t1.Longitude/(radians)) _
    ) < 100
    Select New With { _
        ZipCodeDatas = t1, _
        DistanceInMiles = 3963.0 * SqlFunctions.Acos( _
            SqlFunctions.Sin(t1.Latitude/(@radians)) _
            * SqlFunctions.Sin(t2.Latitude/(57.295779513082323)) _
            + SqlFunctions.Cos(t1.Latitude/(@radians)) _
            * SqlFunctions.Cos(t2.Latitude/(@radians)) _
            * SqlFunctions.Cos(t2.Longitude/(@radians) - t1.Longitude/(@radians)) _
        )
    }
).OrderBy(o => o.DistanceInMiles)

Upvotes: 1

Related Questions