Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Calculate distance based on latitude/longitude
 

Pages: 1
LLBLGen Pro Runtime Framework
Calculate distance based on latitude/longitude
Page:1/1 

  Print all messages in this thread  
Poster Message
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 14-Apr-2009 19:32:10.  
Hi,

I have a database with a table that contains latitude and longitude fields (float), and I'm trying to select the distance to each record from a certain fixed point, but LLBLGen doesn't seem to be cooperating...

I use 'float' fields because they're mapped to System.Double, the only data type most of the Math functions allow operations on without casting.

Code:

[Test]
public void GetDistanceToAddresses()
{
    Location location = new Location(52.43,04.23);

    const double EarthRadius = 3963.1;
    var addresses = from address in metadata.Address
                    let lat1 = address.Latitude.Value * Math.PI / 180
                    let lon1 = address.Longitude.Value * Math.PI / 180
                    let lat2 = location.Latitude * Math.PI / 180
                    let lon2 = location.Longitude * Math.PI / 180
                    let x2 =    Math.Sin(lat1) *
                                Math.Sin(lat2) +
                                Math.Cos(lat1) *
                                Math.Cos(lat2) *
                                Math.Cos(lon2 - lon1)
                    let d = EarthRadius * (-1 * Math.Atan(x2 / Math.Sqrt(1 - x2 * x2)) + Math.PI / 2)
                    where address.Latitude.HasValue && address.Longitude.HasValue
                    select d;

    foreach (var d in addresses)
    {
        Console.WriteLine(d);
    }
}


When I run this, I get the following error from LLBLGen:
System.InvalidOperationException : The binary operator Multiply is not defined for the types 'System.Decimal' and 'System.Double'.

output:
Code:

TestCase 'VindCreatieven.Tests.VacancyTests.GetDistanceToAddresses'
failed: System.InvalidOperationException : The binary operator Multiply is not defined for the types 'System.Decimal' and 'System.Double'.
    at System.Linq.Expressions.Expression.GetUserDefinedBinaryOperatorOrThrow(ExpressionType binaryType, String name, Expression left, Expression right, Boolean liftToNull)
    at System.Linq.Expressions.Expression.Multiply(Expression left, Expression right)
    at System.Linq.Expressions.Expression.Multiply(Expression left, Expression right, MethodInfo method)
    at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method, LambdaExpression conversion)
    at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleBinaryExpression(BinaryExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleBinaryExpressionArithmeticOrBitOperator(BinaryExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleBinaryExpression(BinaryExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleBinaryExpressionArithmeticOrBitOperator(BinaryExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpressionList(ReadOnlyCollection`1 listToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleNewExpression(NewExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleProjectionExpression(ProjectionExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleWhereExpression(WhereExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()


File versions:
SQL Server 2005
LinqSupportClasses.NET35.dll 2.6.9.206
ORMSupportClasses.NET20.dll 2.6.9.116
DQE.SqlServer.NET20.dll 2.6.8.1114



Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 14-Apr-2009 20:06:12.  
When using decimals everywhere and creating custom function mappings for 'Math' that allow decimals, LLBLGen doesn't like it either:

Code:

[Test]
public void GetDistanceToAddressesWithMapping()
{
    Location location = new Location(52.43m,04.23m);

    adapter.FunctionMappings.Add(new FunctionMapping(typeof(Math2), "Sin", 1, "sin({0})"));
    adapter.FunctionMappings.Add(new FunctionMapping(typeof(Math2), "Cos", 1, "cos({0})"));
    adapter.FunctionMappings.Add(new FunctionMapping(typeof(Math2), "Atan", 1, "atan({0})"));

    const decimal earthRadius = 3963.1m;
    const decimal pi = (decimal)Math.PI;

    var lat1 = location.LatitudeDec * pi / 180;
    var lon1 = location.LongitudeDec * pi / 180;
    var lat1sin = (decimal) Math.Sin((double) lat1);
    var lon1cos = (decimal) Math.Cos((double) lat1);

    var addresses = from address in metadata.CreativeAddress
                    let lat2 = address.Latitude.Value * pi / 180
                    let lon2 = address.Longitude.Value * pi / 180
                    let total =
                            Math2.Sin(lat2) * lat1sin +
                            Math2.Cos(lat2) * lon1cos *
                            Math2.Cos(lon1 - lon2)
                    let distance = earthRadius * (-1 * Math2.Atan(total / Math2.Sqrt(1 - total * total)) + (pi / 2))
                    where address.Latitude.HasValue && address.Longitude.HasValue
                    select distance;

    foreach (var d in addresses)
    {
        Console.WriteLine(d);
    }
}

    // math overloads class:
    public static class Math2
    {
        public static decimal Sin(decimal value)
        {
            return (decimal) Math.Sin((double) value);
        }

        public static decimal Cos(decimal value)
        {
            return (decimal) Math.Cos((double) value);
        }

        public static decimal Atan(decimal value)
        {
            return (decimal) Math.Atan((double) value);
        }

        public static decimal Sqrt(decimal value)
        {
            return (decimal) Math.Sqrt((double) value);
        }
    }

    
Error message:
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException : The parameter at position 0 is of an unsupported type: Call

Code:

Method Enter: DataAccessAdapterBase.StartTransaction
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.StartTransaction
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[VindCreatieven.Dal.EntityClasses.CreativeAddressEntity]).Select(address => new <>f__AnonymousType0`2(address = address, lat2 = ((address.Latitude.Value * 3,14159265358979) / 180))).Select(<>h__TransparentIdentifier9 => new <>f__AnonymousType4`2(<>h__TransparentIdentifier9 = <>h__TransparentIdentifier9, lon2 = ((<>h__TransparentIdentifier9.address.Longitude.Value * 3,14159265358979) / 180))).Select(<>h__TransparentIdentifiera => new <>f__AnonymousType5`2(<>h__TransparentIdentifiera = <>h__TransparentIdentifiera, total = ((Sin(<>h__TransparentIdentifiera.<>h__TransparentIdentifier9.lat2) * value(VindCreatieven.Tests.VacancyTests+<>c__DisplayClassd).lat1sin) + ((Cos(<>h__TransparentIdentifiera.<>h__TransparentIdentifier9.lat2) * value(VindCreatieven.Tests.VacancyTests+<>c__DisplayClassd).lon1cos) * Cos((value(VindCreatieven.Tests.VacancyTests+<>c__DisplayClassd).lon1 - <>h__TransparentIdentifiera.lon2)))))).Select(<>h__TransparentIdentifierb => new <>f__AnonymousType6`2(<>h__TransparentIdentifierb = <>h__TransparentIdentifierb, distance = (3963,1 * ((-1 * Atan((<>h__TransparentIdentifierb.total / Sqrt((1 - (<>h__TransparentIdentifierb.total * <>h__TransparentIdentifierb.total)))))) + 1,570796326794895)))).Where(<>h__TransparentIdentifierc => (<>h__TransparentIdentifierc.<>h__TransparentIdentifierb.<>h__TransparentIdentifiera.<>h__TransparentIdentifier9.address.Latitude.HasValue && <>h__TransparentIdentifierc.<>h__TransparentIdentifierb.<>h__TransparentIdentifiera.<>h__TransparentIdentifier9.address.Longitude.HasValue)).Select(<>h__TransparentIdentifierc => <>h__TransparentIdentifierc.distance)
Method Enter: DataAccessAdapterBase.Rollback
Method Exit: DataAccessAdapterBase.Rollback
TestCase 'VindCreatieven.Tests.VacancyTests.GetDistanceToAddressesWithMapping'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException : The parameter at position 0 is of an unsupported type: Call
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleDbFunctionCallExpression(DbFunctionCallExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleBinaryExpressionArithmeticOrBitOperator(BinaryExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleBinaryExpressionArithmeticOrBitOperator(BinaryExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleNewExpression(NewExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleProjectionExpression(ProjectionExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleWhereExpression(WhereExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()


Any alternative ideas?

Thanks,

Wiebe


Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37879 posts
# Posted on: 14-Apr-2009 21:19:49.  
Will look into it. Might be a result of the '/' operation inside the let, but I've to study the expression tree.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37879 posts
# Posted on: 15-Apr-2009 10:16:33.  
The second post, the error likely comes from the fact you add the function mappings to some FunctionMapping construct you didnt pass to LinqMetaData so the mapping you defined isn't chosen and the DbFunctionCall ends up with an argument which isn't handled as it should be and is still a call to an in-memory method. These aren't handled as they're compiled (in the projection).

(edit) Could you please give me the types (layout) and table DDL you used? It seems you used a custom type (Location) and I'm not sure about the type of Location (it's not a BCL class).

Also you have let statements which result in a constant (the let statements on Location) so you can move these outside the query (which is recommended, 'let' isn't a friendly statement, it causes slower SQL). Also, could you comment out lines in the query to see which one gives this issue and change '180' to '180.0' so you divide by a double and not an int? The reason for that is that in C# the cast is implicit but it's to be seen what the expression tree results in: it might be a functionmapping is used which uses a different result type.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 15-Apr-2009 11:33:21.  
Hi Frans,

I've changed the code to this (fields use decimals as store now, will change that in a bit), removed the Location because it wasn't really necessary:
Code:

[SetUp()]
public void Setup()
{
    adapter = DataAccessAdapter.Create();
    adapter.StartTransaction(IsolationLevel.Serializable, "test");
    adapter.FunctionMappings.Add(new FunctionMapping(typeof(Math2), "Sin", 1, "sin({0})"));
    adapter.FunctionMappings.Add(new FunctionMapping(typeof(Math2), "Cos", 1, "cos({0})"));
    adapter.FunctionMappings.Add(new FunctionMapping(typeof(Math2), "Atan", 1, "atan({0})"));

    store = new VacancyStore(adapter);
    metadata = new LinqMetaData(adapter,adapter.FunctionMappings);
}
        
[Test]
public void GetDistanceToAddresses()
{
    const double EarthRadius = 3963.1;
    const double lat1 = (52.0 * Math.PI) / 180.0;
    const double lon1 = (4.0 * Math.PI) / 180.0;

    double lat1sin = Math.Sin(lat1);
    double lon1sin = Math.Cos(lat1);

    var addresses = from address in metadata.CreativeAddress
                    let lat2 = (((double)address.Latitude.Value) * Math.PI) / 180.0
                    let lon2 = (((double)address.Longitude.Value) * Math.PI) / 180.0
                    let d1 = Math.Sin(lat2) * lat1sin +
                             Math.Cos(lat2) * lon1sin *
                             Math.Cos(lon1 - lon2)
                    let distance = EarthRadius * (-1.0 * Math.Atan(d1 / Math.Sqrt(1.0 - d1 * d1)) + (Math.PI / 2.0))
                    where address.Latitude.HasValue && address.Longitude.HasValue
                    select distance;

    foreach (var d in addresses)
    {
        Console.WriteLine(d);
    }
}


the DDL for the table (snipped some fields):
Code:


CREATE TABLE [dbo].[CreativeAddresses](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [City] [nvarchar](50) NULL,
    [Latitude] [decimal](18, 10) NULL,
    [Longitude] [decimal](18, 10) NULL,
CONSTRAINT [PK_CreativeAddresses] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



Now I'm getting a NullReferenceException:

Code:

------ Test started: Assembly: VindCreatieven.Tests.dll ------

Method Enter: DataAccessAdapterBase.StartTransaction
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.StartTransaction
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[VindCreatieven.Dal.EntityClasses.CreativeAddressEntity]).Select(address => new <>f__AnonymousType0`2(address = address, lat2 = ((Convert(address.Latitude.Value) * 3,14159265358979) / 180))).Select(<>h__TransparentIdentifier3 => new <>f__AnonymousType1`2(<>h__TransparentIdentifier3 = <>h__TransparentIdentifier3, lon2 = ((Convert(<>h__TransparentIdentifier3.address.Longitude.Value) * 3,14159265358979) / 180))).Select(<>h__TransparentIdentifier4 => new <>f__AnonymousType2`2(<>h__TransparentIdentifier4 = <>h__TransparentIdentifier4, d1 = ((Sin(<>h__TransparentIdentifier4.<>h__TransparentIdentifier3.lat2) * value(VindCreatieven.Tests.VacancyTests+<>c__DisplayClass7).lat1sin) + ((Cos(<>h__TransparentIdentifier4.<>h__TransparentIdentifier3.lat2) * value(VindCreatieven.Tests.VacancyTests+<>c__DisplayClass7).lon1sin) * Cos((0,0698131700797732 - <>h__TransparentIdentifier4.lon2)))))).Select(<>h__TransparentIdentifier5 => new <>f__AnonymousType3`2(<>h__TransparentIdentifier5 = <>h__TransparentIdentifier5, distance = (3963,1 * ((-1 * Atan((<>h__TransparentIdentifier5.d1 / Sqrt((1 - (<>h__TransparentIdentifier5.d1 * <>h__TransparentIdentifier5.d1)))))) + 1,5707963267949)))).Where(<>h__TransparentIdentifier6 => (<>h__TransparentIdentifier6.<>h__TransparentIdentifier5.<>h__TransparentIdentifier4.<>h__TransparentIdentifier3.address.Latitude.HasValue && <>h__TransparentIdentifier6.<>h__TransparentIdentifier5.<>h__TransparentIdentifier4.<>h__TransparentIdentifier3.address.Longitude.HasValue)).Select(<>h__TransparentIdentifier6 => <>h__TransparentIdentifier6.distance)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: DataAccessAdapterBase.Rollback
Method Exit: DataAccessAdapterBase.Rollback
TestCase 'VindCreatieven.Tests.VacancyTests.GetDistanceToAddresses'
failed: System.NullReferenceException : Object reference not set to an instance of an object.
    at SD.LLBLGen.Pro.ORMSupportClasses.Expression.OperandToText(StringBuilder& queryText, IExpressionElement operand, Int32& uniqueMarker, Boolean isLeftOperand, Boolean inHavingClause)
    at SD.LLBLGen.Pro.ORMSupportClasses.Expression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause)
    at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.ConvertFieldToRawName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Int32& uniqueMarker, Boolean applyAggregateFunction)
    at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateFieldName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Int32& uniqueMarker, Boolean applyAggregateFunction)
    at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
    at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
    at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32& uniqueMarker)
    at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PrepareJoinableFragments(Int32& uniqueMarker, StringBuilder queryText, IEntityRelation relationAsEntityRelation, IDynamicRelation relationAsDynamicRelation, Boolean relationIsDynamicRelation, Boolean& isSingleOperandDynamicRelation, String& pkElement, String& fkElement, String& aliasPKSide, String& aliasFKSide, String& pkElementReference, String& fkElementReference)
    at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Int32& uniqueMarker, Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix)
    at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText(Int32& uniqueMarker)
    at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
    at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
    at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(IEntityFields2 fieldCollectionToFetch, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize, IFieldPersistenceInfo[]& persistenceInfo, IRetrievalQuery& selectQuery)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()

Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 15-Apr-2009 11:50:52.  
no changing the database fields back to float (remove the casting to double) in the database doesn't help:

Code:

Method Enter: DataAccessAdapterBase.StartTransaction
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.StartTransaction
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[VindCreatieven.Dal.EntityClasses.CreativeAddressEntity]).Select(address => new <>f__AnonymousType0`2(address = address, lat2 = ((address.Latitude.Value * 3,14159265358979) / 180))).Select(<>h__TransparentIdentifier3 => new <>f__AnonymousType1`2(<>h__TransparentIdentifier3 = <>h__TransparentIdentifier3, lon2 = ((<>h__TransparentIdentifier3.address.Longitude.Value * 3,14159265358979) / 180))).Select(<>h__TransparentIdentifier4 => new <>f__AnonymousType2`2(<>h__TransparentIdentifier4 = <>h__TransparentIdentifier4, d1 = ((Sin(<>h__TransparentIdentifier4.<>h__TransparentIdentifier3.lat2) * value(VindCreatieven.Tests.VacancyTests+<>c__DisplayClass7).lat1sin) + ((Cos(<>h__TransparentIdentifier4.<>h__TransparentIdentifier3.lat2) * value(VindCreatieven.Tests.VacancyTests+<>c__DisplayClass7).lon1sin) * Cos((0,0698131700797732 - <>h__TransparentIdentifier4.lon2)))))).Select(<>h__TransparentIdentifier5 => new <>f__AnonymousType3`2(<>h__TransparentIdentifier5 = <>h__TransparentIdentifier5, distance = (3963,1 * ((-1 * Atan((<>h__TransparentIdentifier5.d1 / Sqrt((1 - (<>h__TransparentIdentifier5.d1 * <>h__TransparentIdentifier5.d1)))))) + 1,5707963267949)))).Where(<>h__TransparentIdentifier6 => (<>h__TransparentIdentifier6.<>h__TransparentIdentifier5.<>h__TransparentIdentifier4.<>h__TransparentIdentifier3.address.Latitude.HasValue && <>h__TransparentIdentifier6.<>h__TransparentIdentifier5.<>h__TransparentIdentifier4.<>h__TransparentIdentifier3.address.Longitude.HasValue)).Select(<>h__TransparentIdentifier6 => <>h__TransparentIdentifier6.distance)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: DataAccessAdapterBase.Rollback
Method Exit: DataAccessAdapterBase.Rollback
TestCase 'VindCreatieven.Tests.VacancyTests.GetDistanceToAddresses'
failed: System.NullReferenceException : Object reference not set to an instance of an object.
    at SD.LLBLGen.Pro.ORMSupportClasses.Expression.OperandToText(StringBuilder& queryText, IExpressionElement operand, Int32& uniqueMarker, Boolean isLeftOperand, Boolean inHavingClause)
    at SD.LLBLGen.Pro.ORMSupportClasses.Expression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause)
    at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.ConvertFieldToRawName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Int32& uniqueMarker, Boolean applyAggregateFunction)
    at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateFieldName(IEntityFieldCore fieldCore, IFieldPersistenceInfo persistenceInfo, String fieldName, String objectAlias, Int32& uniqueMarker, Boolean applyAggregateFunction)
    at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
    at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
    at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateSubQuery(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldPersistenceInfos, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32& uniqueMarker)
    at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PrepareJoinableFragments(Int32& uniqueMarker, StringBuilder queryText, IEntityRelation relationAsEntityRelation, IDynamicRelation relationAsDynamicRelation, Boolean relationIsDynamicRelation, Boolean& isSingleOperandDynamicRelation, String& pkElement, String& fkElement, String& aliasPKSide, String& aliasFKSide, String& pkElementReference, String& fkElementReference)
    at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Int32& uniqueMarker, Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix)
    at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText(Int32& uniqueMarker)
    at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
    at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
    at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(IEntityFields2 fieldCollectionToFetch, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize, IFieldPersistenceInfo[]& persistenceInfo, IRetrievalQuery& selectQuery)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()


even with this code, I keep getting the mapping error:

Code:


        [SetUp()]
        public void Setup()
        {
            adapter = DataAccessAdapter.Create();
            adapter.StartTransaction(IsolationLevel.Serializable, "test");
            adapter.FunctionMappings.Add(new FunctionMapping(typeof(Math2), "Sin", 1, "sin({0})"));
            adapter.FunctionMappings.Add(new FunctionMapping(typeof(Math2), "Cos", 1, "cos({0})"));
            adapter.FunctionMappings.Add(new FunctionMapping(typeof(Math2), "Atan", 1, "atan({0})"));

            store = new VacancyStore(adapter);
            metadata = new LinqMetaData(adapter,adapter.FunctionMappings);
        }

[Test]
public void GetDistanceToAddressesWithMapping()
{
    const decimal earthRadius = 3963.1m;
    const decimal pi = (decimal)Math.PI;

    const decimal lat1 = 52.0m * pi / 180m;
    const decimal lon1 = 4.0m * pi / 180m;
    var lat1sin = Math2.Sin(lat1);
    var lat1cos = Math2.Cos(lat1);

    var addresses = from address in metadata.CreativeAddress
                    let lat2 = (decimal)address.Latitude.Value * pi / 180m
                    let lon2 = (decimal)address.Longitude.Value * pi / 180m
                    let total =
                            Math2.Sin(lat2) * lat1sin +
                            Math2.Cos(lat2) * lat1cos *
                            Math2.Cos(lon1 - lon2)
                    let distance = earthRadius * (-1m * Math2.Atan(total / Math2.Sqrt(1m - total * total)) + (pi / 2m))
                    where address.Latitude.HasValue && address.Longitude.HasValue
                    select distance;

    foreach (var d in addresses)
    {
        Console.WriteLine(d);
    }
}


output:

Code:

Method Enter: DataAccessAdapterBase.StartTransaction
Method Enter: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.OpenConnection
Method Exit: DataAccessAdapterBase.StartTransaction
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[VindCreatieven.Dal.EntityClasses.CreativeAddressEntity]).Select(address => new <>f__AnonymousType0`2(address = address, lat2 = ((Convert(address.Latitude.Value) * 3,14159265358979) / 180))).Select(<>h__TransparentIdentifier9 => new <>f__AnonymousType4`2(<>h__TransparentIdentifier9 = <>h__TransparentIdentifier9, lon2 = ((Convert(<>h__TransparentIdentifier9.address.Longitude.Value) * 3,14159265358979) / 180))).Select(<>h__TransparentIdentifiera => new <>f__AnonymousType5`2(<>h__TransparentIdentifiera = <>h__TransparentIdentifiera, total = ((Sin(<>h__TransparentIdentifiera.<>h__TransparentIdentifier9.lat2) * value(VindCreatieven.Tests.VacancyTests+<>c__DisplayClassd).lat1sin) + ((Cos(<>h__TransparentIdentifiera.<>h__TransparentIdentifier9.lat2) * value(VindCreatieven.Tests.VacancyTests+<>c__DisplayClassd).lat1cos) * Cos((0,0698131700797731111111111111 - <>h__TransparentIdentifiera.lon2)))))).Select(<>h__TransparentIdentifierb => new <>f__AnonymousType6`2(<>h__TransparentIdentifierb = <>h__TransparentIdentifierb, distance = (3963,1 * ((-1 * Atan((<>h__TransparentIdentifierb.total / Sqrt((1 - (<>h__TransparentIdentifierb.total * <>h__TransparentIdentifierb.total)))))) + 1,570796326794895)))).Where(<>h__TransparentIdentifierc => (<>h__TransparentIdentifierc.<>h__TransparentIdentifierb.<>h__TransparentIdentifiera.<>h__TransparentIdentifier9.address.Latitude.HasValue && <>h__TransparentIdentifierc.<>h__TransparentIdentifierb.<>h__TransparentIdentifiera.<>h__TransparentIdentifier9.address.Longitude.HasValue)).Select(<>h__TransparentIdentifierc => <>h__TransparentIdentifierc.distance)
Method Enter: DataAccessAdapterBase.Rollback
Method Exit: DataAccessAdapterBase.Rollback
TestCase 'VindCreatieven.Tests.VacancyTests.GetDistanceToAddressesWithMapping'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException : The parameter at position 0 is of an unsupported type: Call
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleDbFunctionCallExpression(DbFunctionCallExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleBinaryExpressionArithmeticOrBitOperator(BinaryExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleBinaryExpressionArithmeticOrBitOperator(BinaryExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleNewExpression(NewExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleProjectionExpression(ProjectionExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleWhereExpression(WhereExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()



Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37879 posts
# Posted on: 15-Apr-2009 12:14:03.  
Please add the mappings the normal way: Add function mappings to a Functionmapping store and pass that to the LinqMetaData ctor, not the way you use it. The null ref is caused by some function mapping not being correct.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 15-Apr-2009 12:53:52.  
Otis wrote:
Please add the mappings the normal way: Add function mappings to a Functionmapping store and pass that to the LinqMetaData ctor, not the way you use it. The null ref is caused by some function mapping not being correct.


what is the 'normal' way?

I'm getting the error when using it this way as well:

Code:


[SetUp()]
public void Setup()
{
    adapter = DataAccessAdapter.Create();
    adapter.StartTransaction(IsolationLevel.Serializable, "test");
    store = new VacancyStore(adapter);
    
    FunctionMappingStore mappings = new FunctionMappingStore();
    mappings.Add(new FunctionMapping(typeof(Math2), "Sin", 1, "sin({0})"));
    mappings.Add(new FunctionMapping(typeof(Math2), "Cos", 1, "cos({0})"));
    mappings.Add(new FunctionMapping(typeof(Math2), "Atan", 1, "atan({0})"));
    metadata = new LinqMetaData(adapter,mappings);
}


Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14588 posts
# Posted on: 15-Apr-2009 13:00:57.  
FunctionMappings should map to a .NET class with a method defined corresponding to the one used in the linq code.

example from the docs:
Code:
/// Class which is used to specify the call to the database function. We'll map
/// the function inside it, CalculateOrderTotal, to the database function.
public class NorthwindFunctions
{
    /// <summary>
    /// Calculates the order total.
    /// </summary>
    /// <param name="orderId">The order id.</param>
    /// <param name="useDiscounts">if set to <c>true</c> [use discounts].</param>
    /// <returns></returns>
    public static decimal CalculateOrderTotal(int orderId, bool useDiscounts)
    {
        // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
        return 0.0M;
    }
}

/// Class which defines the custom mapping between NorthwindFunctions.CalculateOrderTotal and
/// fn_CalculateOrderTotal, the database function.
public class NorthwindFunctionMappings : FunctionMappingStore
{
    public NorthwindFunctionMappings() : base()
    {
        // define the mapping. SQLServer 2000 needs the schema to be present for the function call,
        // so we specify that as well.
        this.Add(new FunctionMapping(typeof(NorthwindFunctions), "CalculateOrderTotal", 2,
                        "fn_CalculateOrderTotal({0}, {1})", "Northwind", "dbo"));
    }
}


//...

// these constructs are now allowing the developer to actually call fn_CalculateOrderTotal from within
// the Linq query. We have to pass the custom FunctionMappingStore 'NorthwindFunctionMappings' to
// the LinqMetaData first:
metaData.CustomFunctionMappings = new NorthwindFunctionMappings();
// fetch the orders with the total calculated by the function inside the database
var q = from o in metaData.Order
        where o.CustomerId == "CHOPS"
        select new { o.OrderId, OrderTotal = NorthwindFunctions.CalculateOrderTotal(o.OrderId, true) };
  Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 15-Apr-2009 13:11:38.  
Hi Walaa,

Other than that you inherited from MappingStore, I don't see what's different, or is it just not possible to create custom function mappings to built-in statements (sin(), cos(), atan()).

I'll try your way and let you know.


Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 15-Apr-2009 14:18:09.  
Hi,

I also get the NullReferenceException when not adding/using the custom mapped functions at all.
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37879 posts
# Posted on: 15-Apr-2009 14:26:36.  
Ok, so that's not it. I know where to look (it's tedious to traverse the big expression tree). I'll get back to you when I've fixed it.

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37879 posts
# Posted on: 15-Apr-2009 15:21:30.  
reproduced. It's some kind of crash where an expression is an operand of the dbfunction call but it is by itself null... very odd. Looking into it.

(though I can see why, sin, cos, atan and sqrt don't have a db equivalent mapped by default).

This is the tree right before everything is wrapped up:
Code:
Entity(CreativeAddressEntity).Select(Projection(address => new <>f__AnonymousType0`2(address = [507], lat1 = (([508] * 3,14159265358979) / 180)))).Select(Projection(<>h__TransparentIdentifier0 => new <>f__AnonymousType1`2(<>h__TransparentIdentifier0 = [531], lon1 = (([508] * 3,14159265358979) / 180)))).Select(Projection(<>h__TransparentIdentifier1 => new <>f__AnonymousType2`2(<>h__TransparentIdentifier1 = [531], x2 = (Sin([508]) * Cos([508]))))).Select(Projection(<>h__TransparentIdentifier2 => new <>f__AnonymousType3`2(<>h__TransparentIdentifier2 = [531], d = (3963,1 * ((-1 * Atan(([508] / Sqrt((1 - ([508] * [508])))))) + 1,5707963267949))))).Where(<>h__TransparentIdentifier3 => (([508] != null) && ([508] != null))).Select(Projection(<>h__TransparentIdentifier3 => [508]))


i.o.w., you don't want this Regular Smiley. Will look into a way to run this though.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37879 posts
# Posted on: 15-Apr-2009 15:39:03.  
Ok I have it working Regular Smiley

1) I used float fields:
Code:

CREATE TABLE [dbo].[CreativeAddresses](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [City] [nvarchar](50) NULL,
    [Latitude] [decimal](18, 10) NULL,
    [Longitude] [decimal](18, 10) NULL,
    [LatitudeF] FLOAT NULL,
    [LongitudeF] FLOAT NULL,
CONSTRAINT [PK_CreativeAddresses] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Then I defined the missing mappings for the functions:
Code:

internal class AdventureWorksFunctions
{
    public static double Sin(double a)
    {
        return 0.0;
    }

    public static double Cos(double a)
    {
        return 0.0;
    }

    public static double Atan(double a)
    {
        return 0.0;
    }

    public static double Sqrt(double a)
    {
        return 0.0;
    }
}


internal class AdventureWorksFunctionMappings : FunctionMappingStore
{
    public AdventureWorksFunctionMappings()
    {
        this.Add(new FunctionMapping(typeof(AdventureWorksFunctions), "Sin", 1, "SIN({0})"));
        this.Add(new FunctionMapping(typeof(AdventureWorksFunctions), "Cos", 1, "COS({0})"));
        this.Add(new FunctionMapping(typeof(AdventureWorksFunctions), "Atan", 1, "ATAN({0})"));
        this.Add(new FunctionMapping(typeof(AdventureWorksFunctions), "Sqrt", 1, "SQRT({0})"));
    }
}


Then I wrote the query with these functions:
Code:

[Test]
public void DoubleAndDecimalMathCalculationsWithLet()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter, new AdventureWorksFunctionMappings());
        const double EarthRadius = 3963.1;
        var q = from address in metaData.CreativeAddress
                //let lat1 = (double)address.Latitude.Value * Math.PI / 180
                //let lon1 = (double)address.Longitude.Value * Math.PI / 180
                let lat1 = address.LatitudeF.Value * Math.PI / 180
                let lon1 = address.LongitudeF.Value * Math.PI / 180
                let x2 = AdventureWorksFunctions.Sin(lat1) *
                            AdventureWorksFunctions.Cos(lat1)
                let d = EarthRadius * (-1 * AdventureWorksFunctions.Atan(x2 / AdventureWorksFunctions.Sqrt(1 - x2 * x2)) + Math.PI / 2)
                where address.Latitude.HasValue && address.Longitude.HasValue
                select d;

        foreach(var d in q)
        {
            Console.WriteLine(d);
        }
    }
}


which gives a value, I added random values to the db, so it has no meaning, but it does work. Please check your code what's different in your code.

(generated query: )
Code:
    Query: SELECT [LPA_L1].[d] FROM (SELECT [LPA_L3].[Id], [LPA_L3].[City], [LPA_L3].[Latitude], [LPA_L3].[Longitude], [LPA_L3].[LatitudeF], [LPA_L3].[LongitudeF], [LPA_L3].[lat1], [LPA_L3].[lon1], [LPA_L3].[x2], @LLBLEP1 * ((@LLBLEP2 * (ATAN([LPA_L3].[x2] / (SQRT(@LLBLEP3 - ([LPA_L3].[x2] * [LPA_L3].[x2])))))) + @LLBLEP4) AS [d] FROM (SELECT [LPA_L4].[Id], [LPA_L4].[City], [LPA_L4].[Latitude], [LPA_L4].[Longitude], [LPA_L4].[LatitudeF], [LPA_L4].[LongitudeF], [LPA_L4].[lat1], [LPA_L4].[lon1], (SIN([LPA_L4].[lat1])) * (COS([LPA_L4].[lat1])) AS [x2] FROM (SELECT [LPA_L5].[Id], [LPA_L5].[City], [LPA_L5].[Latitude], [LPA_L5].[Longitude], [LPA_L5].[LatitudeF], [LPA_L5].[LongitudeF], [LPA_L5].[lat1], ([LPA_L5].[LongitudeF] * @LLBLEP5) / @LLBLEP6 AS [lon1] FROM (SELECT [LPLA_1].[ID] AS [Id], [LPLA_1].[City], [LPLA_1].[Latitude], [LPLA_1].[Longitude], [LPLA_1].[LatitudeF], [LPLA_1].[LongitudeF], ([LPLA_1].[LatitudeF] * @LLBLEP7) / @LLBLEP8 AS [lat1] FROM [AdventureWorks].[dbo].[CreativeAddresses] [LPLA_1] ) [LPA_L5]) [LPA_L4]) [LPA_L3]) [LPA_L1] WHERE ( ( ( ( ( ( [LPA_L1].[Latitude] IS NOT NULL) AND ( [LPA_L1].[Longitude] IS NOT NULL))))))
    Parameter: @LLBLEP1 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3963,1.
    Parameter: @LLBLEP2 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: -1.
    Parameter: @LLBLEP3 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
    Parameter: @LLBLEP4 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1,5707963267949.
    Parameter: @LLBLEP5 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3,14159265358979.
    Parameter: @LLBLEP6 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 180.
    Parameter: @LLBLEP7 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3,14159265358979.
    Parameter: @LLBLEP8 : Double. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 180.


(edit) with the latest runtime libs of course (of April 2009)


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
wtijsma
User



Location:
Amsterdam
Joined on:
18-Apr-2006 15:46:26
Posted:
252 posts
# Posted on: 15-Apr-2009 16:39:52.  
Otis wrote:
Ok I have it working Regular Smiley


Brilliant! I have it running too, now all I have to do is see whether it performs good enough Regular Smiley
Wiebe Tijsma
http://www.netindustry.nl | http://www.twitter.com/wtijsma
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37879 posts
# Posted on: 15-Apr-2009 16:42:31.  
wtijsma wrote:
Otis wrote:
Ok I have it working Regular Smiley


Brilliant! I have it running too, now all I have to do is see whether it performs good enough Regular Smiley

hehe Laugh

Well, back in the days of the demoscene where angle/rotation calculations were common nature, we tend to precalculate as much as possible. So for example, you could pre-calc PI/180 and perhaps other values. SQL is pretty poor when it comes to doing these kind of operations. Regular Smiley


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.