null values on insert command
Closed this issue · 1 comments
StephanHartmann commented
Hello,
shouldn't null values be ignored in an insert command?
If you ignore them, the default values of database will be set.
I managed this in overwriting in a custom SQLformatter:
bool HasNullValue(Expression exp)
{
if (exp.NodeType != ExpressionType.Constant) return false;
var constexp = exp as ConstantExpression;
if (constexp == null) return false;
return constexp.Value == null;
}
protected override Expression VisitInsert(InsertCommand insert)
{
this.Write("INSERT INTO ");
this.WriteTableName(insert.Table.Name);
this.Write("(");
int ct = 0;
for (int i = 0, n = insert.Assignments.Count; i < n; i++)
{
ColumnAssignment ca = insert.Assignments[i];
if (ct > 0) this.Write(", ");
if (!HasNullValue(ca.Expression))
{
this.WriteColumnName(ca.Column.Name);
ct++;
}
}
this.Write(")");
this.WriteLine(Indentation.Same);
this.Write("VALUES (");
ct = 0;
for (int i = 0, n = insert.Assignments.Count; i < n; i++)
{
ColumnAssignment ca = insert.Assignments[i];
if (ct > 0) this.Write(", ");
if (!HasNullValue(ca.Expression))
{
this.Visit(ca.Expression);
ct++;
}
}
this.Write(")");
return insert;
}
mattwar commented
That's an alternative and equally valid way to think about nulls in the input. Unfortunately, there are two possible meanings and I had to pick one. I chose to go with sending null as a value, so the behavior between single entity and batch insert would be the same.