Why is Entity Framework passing a parameter as DEC

问题:

A database column is defined as NUMERIC(19) in SQL Server. In <edmx:StorageModels>, the edmx shows:

<Property Name="supplier_id" Type="numeric" Nullable="false" Precision="19" />

In <edmx:ConceptualModels>, I see it as:

<Property Type="Decimal" Name="supplier_id" Nullable="false" Precision="19" Scale="0" />

In the database, a SELECT with this column in the WHERE clause comes through with this parameter definition:

(@p__linq__0 decimal(5,0))

for this WHERE clause:

WHERE ([Extent1].[supplier_id] = @p__linq__0)

I understand that numeric and decimal are equivalent, but why is the precision getting changed?

回答1:

(@p__linq__0 decimal(5,0)) is the Precision of the parameter value your provided.

For example,

decimal number = 12345m;
var result = context.MyTables.Where(x => x.Number == number).ToList();

Then the query will be like this -

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Number] AS [Number]
    FROM [dbo].[MyTable] AS [Extent1]
    WHERE [Extent1].[Number] = @p__linq__0',N'@p__linq__0 decimal(5,0)',
    @p__linq__0=12345

The answer is SQL Statement doesn't need to be same Precision number for where clause.

Entity Framework is smart enough to figure out the Precision of a number, and only creates the query with required Precision.

Note: it is not the case for Insert and Update; EF creates a query with the same Precision as Column.