Support class Property Annotation

[ahmad-moussawi]

The ability to ignore a property, override the column name, when using the Insert(object) and Update(object) overloads.

class Entry {

   [Ignore]
   public string Type {get; set;}
   
   [Column("user_name")]
   public string UserName {get; set;}
}

Insert()

[mota57]

I already make couple of test I solved on my local I just need to upload it to prod but the name of the attributes will be SqlKataIgnoreAttribute and SqlKataColumn

So the idea is when calling Insert or Update it will call the method AsInsert and AsUpdate.

This will be code change.

Annotations

   public class SqlKataIgnoreAttribute : Attribute
    {
    }


 public class SqlKataColumn : Attribute
    {
        public string Name { get; private set; }
        public SqlKataColumn(string name)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentNullException("SqlKataColumn empty or null Name parameter, please provide a name");
            }
            Name = name;
        }

    }

Code for Update

 public Query AsUpdate(object data)
        {
            var dictionary = new Dictionary<string, object>();

            var props = data.GetType().GetRuntimeProperties()
                .Where(_ => _.GetCustomAttribute(typeof(SqlKataIgnoreAttribute)) == null);

            foreach (var item in props)
            {
                var attr = item.GetCustomAttribute(typeof(SqlKataColumn)) as SqlKataColumn;
                if (attr != null)
                {
                    dictionary.Add(attr.Name, item.GetValue(data));
                }
                else
                {
                    dictionary.Add(item.Name, item.GetValue(data));
                }
            }

            return AsUpdate(dictionary);
        }

Code for Insert

   public Query AsInsert(object data, bool returnId = false)
        {
            var dictionary = new Dictionary<string, object>();

            var props = data.GetType()
                        .GetRuntimeProperties()
                        .Where(_ => _.GetCustomAttribute(typeof(SqlKataIgnoreAttribute)) == null);

            foreach (var item in props)
            {
                var attr = item.GetCustomAttribute(typeof(SqlKataColumn)) as SqlKataColumn;
                if (attr != null)
                {
                    dictionary.Add(attr.Name, item.GetValue(data));
                }
                else
                {
                    dictionary.Add(item.Name, item.GetValue(data));
                }
            }

            return AsInsert(dictionary, returnId);
        }

As a said is very simple and I added a couple of test in order to make sure it works

Test for Update

  private class Book
        {
            public Book(string name, string author, decimal price = 1.0m, string color = null)
            {
                this.Name = name ?? throw new ArgumentNullException("name must be provided");
                this.BookPrice = price;
                this.color = color;
                this.BookAuthor = author;
            }

            public string Name { get; set; }
            [SqlKataColumn("Author")]
            public string BookAuthor { get; set; }
            [SqlKataColumn("Price")]
            public decimal BookPrice { get; set; }
            [SqlKataIgnore]
            public string color { get; set; }
        }

        [Fact]
        public void UpdateWithIgnoreAndColumnProperties()
        {
            var account = new Book(name: $"SqlKataBook", author: "Kata", color: $"red", price: 100m);
            var query = new Query("Book").AsInsert(account);

            var c = Compile(query);

            Assert.Equal(
                "INSERT INTO [Book] ([Name], [Author], [Price]) VALUES ('SqlKataBook', 'Kata', 100)",
                c[EngineCodes.SqlServer]);
        }

Test for Insert

  private class Account
        {
            public Account(string name, string currency = null, string created_at = null, string color = null)
            {
                this.name = name ?? throw new ArgumentNullException("name must be provided");
                this.Currency = currency;
                this.color = color;
            }

            public string name { get; set; }
            [SqlKataColumn("currency_id")]
            public string Currency { get; set; }
            [SqlKataIgnore]
            public string color { get; set; }
        }

        [Fact]
        public void InsertWithIgnoreAndColumnProperties()
        {
            var account = new Account(name: $"popular", color: $"blue", currency: "US");
            var query = new Query("Account").AsInsert(account);

            var c = Compile(query);

            Assert.Equal(
                "INSERT INTO [Account] ([name], [currency_id]) VALUES ('popular', 'US')",
                c[EngineCodes.SqlServer]);
        }

[mota57]

@ahmad-moussawi I dont even know how to add a pull request what should I do?

[mota57]

hello?

[ahmad-moussawi]

@mota57 thanks!, but I think keeping the attributes name shorter (Column and Ignore) could be much simpler for developers. and please add some unit tests, check this https://github.com/sqlkata/querybuilder/blob/master/QueryBuilder.Tests/InsertTests.cs to get an idea.

From your side you have to create a new branch on your local git something like

git checkout -b annotations

Do your changes then commit and push back to the remote repo, or you can follow a simpler approach mentioned here https://help.github.com/en/desktop/contributing-to-projects/creating-a-pull-request

[mota57]

I am trying to push it and I can't because I dont have permission.

I also generate a ssh key then I found an alternative solution on stackoverflow (the second answer) but still this is the results that I get.

Also I clone another project, move the code there and create the branch from scratch then attempt to push the code and I have kind of the same error.

[mota57]

@ahmad-moussawi ready.

[k3davis]

@ahmad-moussawi FWIW I think [Ignore] and [Column] are too generic; another developer will see those and not have a clue they are provided by SqlKata (or are related to the database at all).

Specifically in the case of "Ignore" I think it would be sufficient if the provided column list didn't include the properties we wanted to ignore. For example I have a class with 3 properties but only 2 are written to the table. If I provide a column list with the 2 properties I want to write and then the object (or list of objects for "insert many") it should ignore the missing ones by default. Then we don't need decorators all over the classes, nor to throw an exception as is currently done in that case ("columns should be equal to values count").

[PhilConnor]

@Mota57 I suggest using System.ComponentModel.DataAnnotations.Schema.Column instead of creating a new attribute that does the same thing with the same name. Aside from reducing code, this would allow you to use the same annotation for sqlkata and dapper mapping.

[mota57]

@PhilConnor The suggestion should be for the owner of the repository for @ahmad-moussawi

[azhe403]

Now, ColumnAtribute from SqlKata not yet support SELECT?

[denisulmer]

Any news on this? Is it not supported for selects and is this intended? What is the proposed workaround?