如何在LINQ中实现SQL的CAST(DECIMAL(18,2))数值转换逻辑？

The Problem #

You have this SQL query that includes a precision-controlled conversion:

SELECT 
    [nu_ano], 
    [nu_mes], 
    [id_projeto], 
    [id_fase], 
    'Financial Progress ("Competência")' as ds_categoria, 
    'Baseline' as ds_curva, 
    vl_baseline as vl_curva, 
    cast((vl_baseline / (pc_baseline / 100)) as decimal(18,2)) as vl_curva_total 
FROM [Alvarez_Marsal].[dbo].[Schedule_Status]

And you're working on a LINQ query that looks like this (partial snippet):

var result = (from l in db.Schedule_Status
              .Where(x => x.nu_mes == 12)
              .Select(x => new Retorno { 
                  nu_ano = x.nu_ano, 
                  nu_mes = x.nu_mes, 
                  id_projeto = x.id_projeto,
                  // ... other properties
                  // Need to add vl_curva_total here with the CAST logic
              })

You want to know how to replicate the CAST(DECIMAL(18,2)) logic from your SQL into the LINQ query.

The Solution #

The key here is to replicate two things from your SQL: the division calculation, and the fixed-precision decimal conversion. Here's how to do it properly depending on your Entity Framework version:

For EF Core (Modern Projects) #

Use Math.Round with a decimal literal to ensure proper division and precision—this will generate SQL that aligns with your CAST(DECIMAL(18,2)) requirement:

var result = db.Schedule_Status
    .Where(x => x.nu_mes == 12)
    .Select(x => new Retorno
    {
        nu_ano = x.nu_ano,
        nu_mes = x.nu_mes,
        id_projeto = x.id_projeto,
        id_fase = x.id_fase,
        ds_categoria = "Financial Progress (\"Competência\")",
        ds_curva = "Baseline",
        vl_curva = x.vl_baseline,
        // Here's the converted logic matching your SQL
        vl_curva_total = Math.Round(x.vl_baseline / (x.pc_baseline / 100.0m), 2)
    })
    .ToList();

Key Details:

• 100.0m instead of 100: Using a decimal literal ensures the division uses decimal arithmetic, avoiding integer truncation that would happen with a plain integer.
• Math.Round(..., 2): This tells EF Core to generate SQL that rounds the result to 2 decimal places, matching the precision of DECIMAL(18,2). Under the hood, EF translates this to a SQL ROUND function paired with implicit/explicit casting to the correct decimal type.

For EF6 (Older Projects) #

If you're using Entity Framework 6, use SqlFunctions.Round to ensure accurate SQL translation:

using System.Data.Entity.SqlServer; // Required for SqlFunctions

var result = db.Schedule_Status
    .Where(x => x.nu_mes == 12)
    .Select(x => new Retorno
    {
        // ... other properties
        vl_curva_total = SqlFunctions.Round(x.vl_baseline / (x.pc_baseline / 100.0m), 2)
    })
    .ToList();

Strictly Replicating CAST(DECIMAL(18,2)) Syntax #

If you need to explicitly generate the CAST(...) AS DECIMAL(18,2) SQL instead of relying on rounding, use EF.Functions.Cast in EF Core:

vl_curva_total = EF.Functions.Cast<decimal>(x.vl_baseline / (x.pc_baseline / 100.0m))

Note: Most SQL engines automatically handle precision when casting to decimal, but if you need to enforce (18,2) specifically, you may need to use a raw SQL snippet or configure your model property to use that precision.

内容的提问来源于stack exchange，提问作者Leonardo Lima