code first insert主键问题
其一:
model 如下:
[Table("goods")]
public class goods
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
[Column("id")]
public int id { get; set; }
[Required]
[MaxLength(50)]
[Column("name", TypeName="nvarchar")]
[DefaultValue("")]
public string name { get; set; }
[Required]
[DefaultValue(0)]
[Column("num")]
public uint num { get; set; }
[Required]
[Column("price")]
[DefaultValue(0)]
public decimal price { get; set; }
[Required]
[Column("create_date")]
public DateTime create_date { get; set; }
[Required]
[DefaultValue(0)]
[Column("status")]
public int status { get; set; }
}
C# 代码如下:
Model.goods info = new Model.goods()
{
name = goods_name,
price = _price,
status = 1,
create_date = DateTime.Now
};
db.goods.Add(info);
db.SaveChanges();
code first生成的sql如下:
exec sp_executesql N'insert [dbo].[goods]([name], [price], [create_date], [status])
values (@0, @1, @2, @3)
select [id]
from [dbo].[goods]
where @@ROWCOUNT > 0 and [id] = scope_identity()',N'@0 nvarchar(50),@1 decimal(18,2),@2 datetime2(7),@3 int',@0=N'苹果389',@1=389.00,@2='2016-09-27 15:43:17.2030654',@3=1
其二:
model:
[Table("goods")]
public class goods
{
[Key]
//[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
//[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Column("id")]
public int id { get; set; }
[Required]
[MaxLength(50)]
[Column("name", TypeName="nvarchar")]
[DefaultValue("")]
public string name { get; set; }
[Required]
[DefaultValue(0)]
[Column("num")]
public uint num { get; set; }
[Required]
[Column("price")]
[DefaultValue(0)]
public decimal price { get; set; }
[Required]
[Column("create_date")]
public DateTime create_date { get; set; }
[Required]
[DefaultValue(0)]
[Column("status")]
public int status { get; set; }
}
C#代码如上
code first生成的sql如下:
exec sp_executesql N'insert [dbo].[goods]([name], [price], [create_date], [status])
values (@0, @1, @2, @3)
select [id]
from [dbo].[goods]
where @@ROWCOUNT > 0 and [id] = scope_identity()',N'@0 nvarchar(50),@1 decimal(18,2),@2 datetime2(7),@3 int',@0=N'橘子951',@1=951.00,@2='2016-09-27 15:47:40.6601343',@3=1
如何能让sql片段:
select [id]
from [dbo].[goods]
where @@ROWCOUNT > 0 and [id] = scope_identity()
去掉,而是让数据库来实现主键id的自增??
原因:
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["shop"].ToString()))
{
conn.Open();
string sql = string.Format(@"INSERT INTO goods (name, price, status, create_date) VALUES (@name, @price, @status, @create_date)");
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@name", goods_name);
cmd.Parameters.AddWithValue("@price", _price);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@create_date", DateTime.Now);
cmd.ExecuteNonQuery();
}
}
与code first的代码执行时间对比 59132:95360 (Stopwatch, sw.ElapsedMilliseconds的值)