Operand type clash: int is incompatible with date in sql

Question

i using am writing a insert statement but getting above error

Insert query

var insertSql = string.Format(
                $"INSERT INTO Sales (Date, Team, Sales) VALUES ({0}, {1}, {2})",
                new List<SqlParameter>()
                {
                    new("@date", sales.Date.ToString("yyyy-MM-dd")),
                    new("@team", sales.Team),
                    new("@sales", sales.Sales),
                });
            var rowCount = Sql.ExecuteNonQuery(Conn,insertSql);

here is my table schema and class

SaleId int,
Date Date,
Team varchar(50),
Sales int

public class Sales
{
    public int SalesId { get; set; }
    public DateTime Date { get; set; }
    public string Team { get; set; }
    public int Sales { get; set; }
}

I want to use parameterized query to achive this so tried this but giving above error

Answer 1

The immediate problem here is that $"..." is itself an interpolated string, and the values you are injecting are the literal 0, 1, and 2 - they're not placeholders: they're the values. Removing the $ would make it a literal string suitable for string.Format, but you don't want to use string.Format here; also, you don't want to format the date - just pass it as a date.

Honestly: if you're writing the SQL yourself, I'm not sure EF is even needed here; with Dapper this would be simply:

conn.Execute("INSERT INTO Sales (Date, Team, Sales) Values (@Date, @Team, @Sales)",
    sales);

or with a trick for better SQL formatting:

conn.Execute("""
    INSERT INTO Sales (Date, Team, Sales)
    Values (@Date, @Team, @Sales)
    """, sales);

Dapper will deal with finding sales.Date, sales.Team and sales.Sales, and add them correctly. EF may also have an API for doing ad-hoc queries like this, but... this feels more like a Dapper scenario, unless you're emotionally invested in EF.