Set decimal precision for query result object

Question

I have a class supplied to me via Nuget. I don't have the source.

 public class SpecialProductResult
  {
    public int id { get; set; }
    public decimal SpecialPercent {get;set;}
  }

I want to populate a list of SpecialProductResult from a stored procedure

So in my DbContext I have

public DbQuery<SpecialProductDto> SpecialProducts { get; set; }

I populate the list using

var specialProducts =   connect.SpecialProducts.FromSql("spGetSpecialProducts").ToList()

In the error log I see messages like

No type was specified for the decimal column ‘“SpecialPercent”’ on entity type ‘“SpecialProductResult”’. This will cause values to be silently truncated if they do not fit in the default precision and scale. Explicitly specify the SQL server column type that can accommodate all the values using ‘ForHasColumnType()’.

I looked at this question and wanted to try

modelBuilder.Entity<SpecialProductResult>().Property(o => o.GoldPercent).HasPrecision(18,4)

But there is no property .HasPrecision

What should I try?

[Update]

I tried Ivan Stoev's answer but received a runtime error

The entity type 'SpecialProductResult' cannot be added to the model because a query type with the same name already exists

_GoldPercent_ obviously is wrong, no? Then instead of _HasPrecision()_ try _HasColumnType("decimal(18.4)")_ as the error message suggests. Alternatively, instead of using the fluent api, you could use property attribute _[Column(TypeName = "decimal(18,4)")]_ — Daniel Schmid, Sep 14 '19 at 09:25

score 19 · Answer 1 · answered Oct 26 '20 at 19:49

19

public class Part
{
    public Guid PartId { get; set; }
    public string Number { get; set; }
    [Column(TypeName = "decimal(18,4)")] // <--
    public decimal Size { get; set; }
}

source

answered Oct 26 '20 at 19:49

Ahmad Hamdy Hamdeen

426
5
8

score 15 · Accepted Answer · answered Sep 14 '19 at 09:25

15

Currently EF Core does not provide database independent way of specifying the numeric type precision and scale (similar to EF6 HasPrecision).

The only way to do that is to use HasColumnType and specify the database specific type. If you need to support different databases, you have to use if statements and different HasColumnType for each database type.

For SqlServer, it would be

modelBuilder.Query<SpecialProductResult>()
    .Property(o => o.GoldPercent)
    .HasColumnType("decimal(18,4)");

answered Sep 14 '19 at 09:25

Ivan Stoev

195,425
15
312
343

1

In EF5 I tried `.HasColumnType()` and I tried `HasPrecision()`, but the warning is still displaying in the log file. How do I make the warning go away? – Peet Brits Jan 25 '22 at 07:19
@PeetBrits Both should work (an do work in my tests). e.g. `.HasPrecision(18, 4)` – Ivan Stoev Jan 25 '22 at 07:47
As @PeetBrits stated, the error isn't going away for me even in EF7. The only thing that works is adding the `[Precision()]` attribute on the class property. – PoorInRichfield Jun 02 '23 at 16:32
@PoorInRichfield Not sure what you both are doing, definitely `.HasPrecision(18, 4)` removes the warning. With OP example (which now should be `DbSet`, no more `QbQuery`), the full config is `modelBuilder.Entity().HasNoKey().Property(e => e.SpecialPercent).HasPrecision(18, 4);` And the warning is gone. – Ivan Stoev Jun 02 '23 at 17:26
@IvanStoev I've given up on this one. I have `entityBuilder.Property(t => t.EntryNumber).HasColumnName("MY_COLUMN").IsRequired().HasColumnType("decimal(28, 0)").UseHiLo("MY_SEQ")` where `entityBuilder` = `EntityTypeBuilder` but no success. And yes everything is `DbSet`. – Peet Brits Jun 05 '23 at 07:17
The code works but the warning never goes away. – Peet Brits Jun 05 '23 at 07:42

score 3 · Answer 3 · answered Jun 11 '21 at 09:54

Since EF Core 2.0 there is IEntityTypeConfiguration. In case you are using that approach, you can solve it as follows:

class PartConfiguration : IEntityTypeConfiguration<Part>
{
  public void Configure(EntityTypeBuilder<Part> builder){
    
     builder.Property(c => c.PartId);
     builder.Property(c => c.Number);
     builder.Property(c => c.Size)
            .HasPrecision(18, 4) <-- Use Either This
            .HasColumnType("decimal(18,4)"); <-- Or this
  }
}

...
// OnModelCreating
builder.ApplyConfiguration(new PartConfiguration());

For more information about using the ModelBuilder please refer to Microsoft Docs

Set decimal precision for query result object

3 Answers3