SQLite and List Generic C#

Question

I'm trying to convert some older code for Windows 8 using SQLite. Below is a code from a previous session that works.

using (SqliteConnection conn = new SqliteConnection("Version=3,uri=file://flashcards0904.db"))
        {
            conn.Open();
            using (SqliteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM Decks where DeckGroup='" + Global.currentDeckGroup.ToString() + Global.currentDeck.ToString() + "'" + "order by random()";

                List<string> myCollection = new List<string>();

                using (SqliteDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        myCollection.Add(reader.GetString(0) + "~" + reader.GetString(1) + "~" + reader.GetString(2));
                        Global.words = myCollection.ToArray();
                    }
                }
                conn.Close();
                Community.CsharpSqlite.FileStream.HandleTracker.Clear();
            }
        }

Now, I don't have access to SqliteDataReader and I'm trying to use a List<> function, but I cannot get the "Where" clause to work. Any help, here is my current code:

var root = Windows.Storage.ApplicationData.Current.LocalFolderPath;
var dbPath= Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolderPath,"flashcards0904.db");

using( var db= new SQLite.SQLiteConnection(dbPath))
{
// Here is location for the missing Where Clause"

    var list= db.Table<Decks>.Where??????? .ToList();

 }

Hope this explains the problem.

Thanks

score 3 · Accepted Answer · edited May 23 '17 at 12:08

3

var list= db.Table<Decks>.Where(n=>n.DeckGroup == (Global.currentDeckGroup.ToString() + Global.currentDeck.ToString())).ToList()

I'd encourage you to take a look at: http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

There are a large number of examples there that should help you get a handle on LINQ to SQL.

Paolo Moretti noticed that you were using order by random() in your previous implementation. If you would like to do this in LINQ you can do the following as found here: Linq Orderby random ThreadSafe for use in ASP.NET

Random random = new Random();
int seed = random.Next();
var list= db.Table<Decks>.Where(n=>n.DeckGroup == (Global.currentDeckGroup.ToString() + Global.currentDeck.ToString())).OrderBy(s => (~(s.Shuffle & seed)) & (s.Shuffle | seed)).ToList(); // ^ seed);

edited May 23 '17 at 12:08

Community

1
1

answered Oct 22 '12 at 15:05

JoshVarty

9,066
4
52
80

1

The original query was also shuffling the decks using `"order by random()"` – Paolo Moretti Oct 22 '12 at 15:14
2

You're right, I wasn't sure if he wanted help with that part. There is a clear example here: http://stackoverflow.com/questions/3339192/linq-orderby-random – JoshVarty Oct 22 '12 at 15:17
Excellent response, Mucho Thanks – Trey Balut Oct 22 '12 at 15:34

SQLite and List Generic C#

1 Answers1