locked
Joining two tables with SQLite RRS feed

  • Question

  • I am trying to understand how to use LINQ properly with WinRT and SQLite. I have had some success such as the following.

            protected async void buildSearch()
            {
                string MyListItem = "";
                GlobalVar.CopyDatabase();
                SQLiteAsyncConnection conn = new SQLiteAsyncConnection("ems_lookup.db");
                var query = conn.Table<EMS.Model.Tbl_Providers>().Where(x => x.County == "MAN");
                var result = await query.ToListAsync();
                foreach (var item in result)
                {
                    MyListItem = item.ProviderID + "-[" + item.LastName + ", " + item.FirstName.ToString() + "] [" + item.AddrLn1 + ", " + item.City + ", " + item.State + "]";
                    fResults.Items.Add(MyListItem);
                }
            }

    Now I am trying to create a more verbose result by joining two tables together:

            protected void buildSearch2()
            {
                string MyListItem = "";
                string MyQuery = "SELECT DISTINCT Tbl_Providers.ProviderID as ProviderID, Tbl_Providers.LastName as LastName, Tbl_Providers.FirstName as FirstName, Tbl_ProviderAddress.DBA as DBA, Tbl_ProviderAddress.AddrLn1 as AddrLN1, Tbl_ProviderAddress.City as City, Tbl_ProviderAddress.State as State, Tbl_ProviderAddress.County as County FROM Tbl_Providers, Tbl_ProviderAddress WHERE Tbl_Providers.ProviderID = Tbl_ProviderAddress.ProviderID;";
                GlobalVar.CopyDatabase();
                SQLiteConnection db = new SQLiteConnection("ems_lookup.db");
                var data = db.Query<EMS.Model.ProviderSearchResults>(MyQuery);
                foreach (var item in data)
                {
                    MyListItem = item.ProviderID + "-[" + item.LastName + ", " + item.FirstName.ToString() + "] [" + item.AddrLn1 + ", " + item.City + ", " + item.State + "]";
                    fResults.Items.Add(MyListItem);
                }
            }

    It is executing without errors but I am getting 0 results back. I have defined a results class. is it something different about how SQLite processes SQL? I am really stuck on this.

    Results class is as follows:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace EMS.Model
    {
        class ProviderSearchResults
        {
            public string ProviderID { get; set; }
            public string LastName { get; set; }
            public string FirstName { get; set; }
            public string DBA { get; set; }
            public string AddrLn1 { get; set; }
            public string City { get; set; }
            public string State { get; set; }
            public string County { get; set; }
        }
    }

    Thursday, December 20, 2012 6:59 PM

All replies