"batch" delete

Editor
Jul 1, 2011 at 5:55 PM

It would be nice to add some kind of "batch delete" operations (with expressions for example), something like that...

_dstore.DeleteObjects<MyObject>(r => r.Id > 0 || r.Name != "Dont Delete");

Coordinator
Jul 1, 2011 at 6:09 PM
Edited Jul 1, 2011 at 6:10 PM

I like this idea.  Thanks for the suggestion!  I created an issue for this item

Editor
Aug 2, 2011 at 1:04 PM

Did you start implementing this?

I have started it my own... So, my Idea was to add method:

public void Delete<T>(Expression<Func<T, bool>> criteria)

My implementation goes like this:

        public void Delete<T>(Expression<Func<T, bool>> criteria)
        {
            IDbCommand command = GetDeleteCommand(criteria);
            DataStore.ExecuteCommand(command);
        }
        public IDbCommand GetDeleteCommand<T>(Expression<Func<T, bool>> criteria)
        {
            TypeInfo ti = DataStore.TypeInformationParser.GetTypeInfo(typeof(T));
            StringBuilder sb = new StringBuilder("DELETE FROM ");
            sb.Append(ResolveTableName(ti, true));
            sb.Append(" WHERE ");
            IDbCommand cmd = DataStore.Connection.GetCommand();

            var formatter = new DeleteTSqlFormatter();
            var whereParams = new Dictionary<string, object>();
            var whereString = formatter.FormatDelete(criteria, out whereParams);

            foreach (var par in whereParams)
            {
                cmd.Parameters.Add(DataStore.Connection.GetParameter(par.Key, par.Value));
            }
            sb.Append(whereString);

            cmd.CommandText = sb.ToString();
            return cmd;
        }

and deleteformatter (for MSSQL) looks like:

    public class DeleteTSqlFormatter : TSqlFormatter
    {
        protected Dictionary<string, Object> _parameters = new Dictionary<String, Object>();
        private DbTypeSystem dbTypeSystem = new DbTypeSystem();

        public DeleteTSqlFormatter()
            : base(new TSqlLanguage())
        {
        }

        public string FormatDelete(Expression expression, out Dictionary<string, Object> parameters)
        {
            if (!(expression is ConstantExpression))  //if expression is just constant then I skip it
            {
                if (expression is LambdaExpression)
                {
                    this.Visit(((LambdaExpression)expression).Body);
                }
                else
                    this.Visit(expression);
            }

            parameters = this._parameters;
            return this.ToString();
        }

        protected override Expression VisitConstant(ConstantExpression c)
        {
            //parametrize constants
            string par = String.Format("{0}", _parameters.Count + 1);
            if (_parameters == null) _parameters = new Dictionary<string, object>();
            _parameters.Add(par, c.Value);
            var result = new NamedValueExpression(par, dbTypeSystem.GetColumnType(c.Type), c);
            return base.VisitNamedValue(result);
        }

        protected override Expression VisitMemberAccess(MemberExpression m)
        {
            this.WriteColumnName(m.Member.Name);
            return m;
        }
    }

Do you like Idea (and implementation)?

Coordinator
Aug 2, 2011 at 3:31 PM

I do like your idea and your implementation, I have merged this change into the code base with a few minor changes

 

-> Tucked delete formatter behind an interface
-> Moved the delete GetDeleteCommand<T>(Expression<Func<T, bool>> criteria) behind ICommandGenerator

 

I also implemented it for all the other datastores and wrote a unit test to cover it.  Thanks for submitting this!