Using version 5.3

We find that when we retrieve a record from the database and we project to a new type in the query the decimal truncates

            int OrderId = 123;
            var da = new DataAccessAdapter();
            var md = new LinqMetaData(da);

            // Scenario 1 - Project to a new type in the query, this will truncate the decimal
            var resultsBad = (from cob in md.CalculatedOrderBilling
                           join o in md.Order on cob.OrderId equals o.OrderId
                           where cob.OrderId == OrderId
                           select new BillingCharge
                           {
                               Rate = Convert.ToDecimal(cob.Rate), // This will truncate the decimal from 24.23 to 24
                           }).ToList();

            // Scenario 2 - Map to a new type outside the query, this will not truncate the decimal
            var resultsGood = (from cob in md.CalculatedOrderBilling
                            join o in md.Order on cob.OrderId equals o.OrderId
                            where cob.OrderId == OrderId select cob).ToList();

            foreach(var result in resultsGood)
            {
                var foo = new BillingCharge
                {
                    Rate = Convert.ToDecimal(result.Rate), // This will not truncate the decimal
                };
            }

We looked at our entity mapping and they appear to have the correct scale and precision of (18,4) which matches the precision in SQL Server and it is mapping to a .Net decimal in the designer

Hi Graeme,

The Convert.ToDecimal is converted to an equivalent CONVERT/CAST sql code inline in the query. The Convert.ToDecimal doesn't apply any precision/scale value as the .NET Decimal type doesn't have a scale/precision setting as well. If you require a specific precision/scale specification with the conversion in SQL, please create a custom function mapping for Convert.ToDecimal() and map it to the proper CONVERT statement compatible to your DB of choice. This is mentioned in this documentation page.

Something like this:

internal class NorthwindFunctionsQS
{
    public static FunctionMappingExpression CastAsDecimal(IEntityFieldCore field)
    {
        return new FunctionMappingExpression(typeof(NorthwindFunctionsQS), "CastAsDecimal", 1, field);
    }
}

internal class NorthwindFunctionMappingsQS : FunctionMappingStore
{
    public NorthwindFunctionMappings()
        : base()
    {
        // define the mapping. 
        this.Add(new FunctionMapping(typeof(NorthwindFunctionsQS), "CastAsDecimal", 1, "CAST({0} AS DECIMAL(7,2))", "Northwind", "dbo"));
    }
}

Then use this function in your query:

var q = (from cob in md.CalculatedOrderBilling
   join o in md.Order on cob.OrderId equals o.OrderId
   where cob.OrderId == OrderId
   select new BillingCharge
   {
      Rate = NorthwindFunctionsQS.CastAsDecimal(cob.Rate).ToValue<decimal>()
   });

q.CustomFunctionMappingStore = new NorthwindFunctionMappingsQS();
var results = adapter.FetchQuery(q);

Hope that helps

Hi David,

I understand what you are saying but that seems like a confusing API that because you cannot set the precision of a decimal in .NET that you assume it has none in this specific case. Also the need to create two extra classes and have a more complex query syntax for what could be argued as default behavior given that all this precision is visible in the designer.

This is how .NET to SQL function mapping is designed, to give everyone enough flexibility as per their needs.

GraemeMuir wrote:

Hi David, I understand what you are saying but that seems like a confusing API that because you cannot set the precision of a decimal in .NET that you assume it has none in this specific case. Also the need to create two extra classes and have a more complex query syntax for what could be argued as default behavior given that all this precision is visible in the designer.

The place where the function mapping is applied has no notion of the precision / scale of the value, as the function you call doesn't accept a precision/scale too, so it's not part of the function call: The function Convert.ToDecimal() has to work with the input it gets, there's no information about what the precision/scale has to be. I don't see what else should be 'the default behavior', as there's no other information. The function you call is (Convert.ToDecimal) is simply converted to a call to an equivalent function. In general you don't need to call these, unless you really have to, i.e. you pass in a string and want to convert it to a decimal for instance.

The Linq query is handled and converted without any notion of any mappings, these are taken into account later on, so there's simply no way to know what field is going into the function.

You pass in a cob.Rate field, what's the type of that field? If it's already a decimal, why call Convert.ToDecimal?

Hi

The reason we were doing it is because in .Net

var result = Convert.ToDecimal(cob.rate);

works similar to

var result = cob.rate ?? 0;

But given that sometimes we want to parse a string in our logic the convert call gets used more often in our codebase so that pattern gets used in our projections if people assume that the code will work in a similar way in an LLBlGen query.

In this case, the ToDecimal call is translated to a function call inside a linq query where it can only use what it has got: the field's .net type (System.Decimal) and what function is called (Convert.ToDecimal), so there's little else it can do at that point.

There's no other way around that, as the information required (the precision/scale of the target field) isn't known at that point. A linq query shouldn't be seen as a piece of code that runs like it's a piece of C# code running in memory, but a specification of a SQL query, with all the characteristics that come with it (including NULL handling!).