c# sqlite inner join

Question

I am creating a Xamarin Cross Platform app in VS2017 that uses an SQLite database and I am trying to work out how to perform a query using an inner join. I am using the sqlite-net-pcl NuGet package.

I created the databse using DB Browser for SQLite. I have 2 simple tables.

class Crime
{
    [PrimaryKey, AutoIncrement]
    public int Id {get; set; }
    [NotNull]
    public string Name { get; set; }
    [NotNull]
    public int Legislation { get; set; }
}

class Legislation
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    [NotNull]
    public string Title { get; set; }
}

Using DB Browser I have a foreign key set up on Crime.Legislation linked to Legislation.Id.

I am easily able to query a single table and populate a list using the code below.

public partial class LegislationListPage : ContentPage
{
    private SQLiteAsyncConnection _connetion;
    private ObservableCollection<Legislation> _legislations;
    public LegislationListPage ()
    {
        InitializeComponent ();
        _connetion = DependencyService.Get<ISQLiteDb>().GetConnection();
    }

    protected override async void OnAppearing()
    {
        await _connetion.CreateTableAsync<Legislation>();

        var legislations = await _connetion.Table<Legislation>().OrderBy(x => x.Title).ToListAsync();
        _legislations = new ObservableCollection<Legislation>(legislations);
        legislationList.ItemsSource = _legislations;
    }
}

What I want to do on another page is run a query that returns the Name column from Crime and the Title column from Legislation.

I can achieve this in SQL in DB Browser using

select c.Name, l.Title from Legislation as l inner join Crimes as c on l.Id = c.Legislation

However I don't know how to do this using c# as I have been doing with previous queries. I can't find any .join methods or anything similar. I'm hoping to store the results in a list based on a class that is created on the fly, but I'm not sure if this is possible so I'm happy to create a new class to hold the result of the two strings that will be returned. The end goal is just to populate a list with the results.

If someone could point me in the right direction that would be great.

Thanks in advance.

Answer 1

Ok I managed to find a solution. Quite simple really. I couldn't find a .join method cos i hadn't been 'using' System.Linq.

There are two ways of doing it. Using query syntax and lambda. Lambda is what I was hoping to find so I'm pleased I got it working.

For anyone else looking for something similar here is the two ways I

// First I put both tables into separate lists ( var crimes and var legislation) using the 
// way shown in my question. Code for that is omitted here

//Query style
var crimesListItems = from c in crimes
                      join l in legislations on c.Legislation equals l.Id
                      select new { c.Name, l.Title };

// Lambda style
var crimesListItems = crimes.Join(
          legislations,
          c => c.Legislation,
          l => l.Id,
          (c, l) => new {c.Name, l.Title} );

Credit to Jeremy Clark and his tutorial at https://www.youtube.com/watch?v=tzR2qY6S4yw