c# - Xamarin SQLite.net 按数据和总和数字对列进行分组

Question

我正在学习 Xamarin，我正在使用这个 [sqlite 插件] [1。我想按日期（MyCreateDate）汇总表值，并对那些日期（蛾，是的）中的单词数（Word1）求和。

例如，如果我有：

3 words in November 2001, nothing  in December 2001, 6 words in january 2002,2 words in febuary 2002, nothing  in March 2002.

我想得到这样的回报：

Moth      Year   NumberWords
11        2001      3 
12        2001      0
1         2002      6
2         2002      2
3         2002      0

这是我的模型 Model.cs：

[PrimaryKey, AutoIncrement]
public int ID { get; set; }

[JsonProperty(PropertyName = "id")]
public string MyId { get; set; }

[JsonProperty(PropertyName = "myCreated")]
public DateTime MyCreateDate { get; set; } = DateTime.UtcNow; // with this value of date

[JsonProperty(PropertyName = "myUpdate")]
public DateTime MyUpdateDate { get; set; } = DateTime.UtcNow;

[JsonProperty(PropertyName = "complete")]
public bool Complete { get; set; }

[JsonProperty(PropertyName = "wordissaved")]
public bool WordIsSaved { get; set; } = false;

[JsonProperty(PropertyName = "word1")]
public string Word1 { get; set; }

[JsonProperty(PropertyName = "word2")]
public string Word2 { get; set; }

这是数据库 MyWordsDatabase.cs，我设法获取单词并选择随机单词：

class MyWordsDatabase
    {
        private SQLiteAsyncConnection conn;

        //CREATE  
        public MyWordsDatabase()
        {
            conn = DependencyService.Get<ISQLite>().GetConnection();
            conn.CreateTableAsync<MyWords>();
        }

        //READ  
        public  Task <List<MyWords>> GetWords()
        {
            var wordList = conn.Table<MyWords>();
            return wordList.OrderByDescending(w => w.ID).ToListAsync();

        }

        // Select ramdon words and put them in a list 
         public   Task<List<MyWords>> GetRandomListWords(int number)
        {
            var words =  conn.QueryAsync<MyWords>("select DISTINCT  * from MyWords ORDER BY RANDOM() LIMIT " + number + "");

            return  words;
        }

     }

谢谢你的帮助

标签： c#sqlitexamarin.formssqlite-net

Answer 1

您可以使用 LINQ 进行分组

var query = (from w in wordList
         group w by new {w.MyCreateDate.Year, w.MyCreateDate.Month}
         into grp
                select new
                {
                    grp.Key.MyCreateDate.Year,
                    grp.Key.MyCreateDate.Month,
                    Quantity = grp.Count()
                }).ToList();