CODEWITHSUNDEEP
c#sqlitewindows-runtime
dub stylee
dub stylee

Reputation: 3342

Is there a simple way to JOIN tables from an SQLite repository?

I am working on a program to manage my personal finances. I have an SQLite database that is storing all of the data, and I am able to load/save accounts, bills, payments, etc.

What I am looking to do, is load the account name of the associated account based on the PayeeId of each Payment. I know how to achieve this using SQL, but my data is set up using repositories. For example, I load the Payments by calling

var payments = await _paymentsRepository.LoadAllAsync();

And the LoadAllAsync() method is in RepositoryBase class, and looks like so:

public async Task<IEnumerable<TTable>> LoadAllAsync()
{
    var query = _sqliteService.Conn.Table<TTable>();
    var array = (await query.ToListAsync()).ToArray();

    return array;
}

and is declared in the IPaymentsRepository interface like so:

Task<IEnumerable<Payment>> LoadAllAsync();

Each Payment object has a PayeeId property that links to the Payee for that Payment. The Payment itself doesn't store any of the other information about the Payee, but I would like to be able to load the PayeeName property to display with the Payment information. Is there a simple way to do this, or will I have to create a separate ViewModel to store the "hybrid" data containing both the Payment information as well as the Payee information?

EDIT

I know I can accomplish this using an extra class, for example PaymentInfo or something, and store both the Payment and Payee data, then access it like this: PaymentInfo.Payment.PaymentAmount or PaymentInfo.Payee.PayeeName, but I would have to load them in two separate queries. While this is certainly possible, I am hoping for a solution that can be accomplished in one query, which is why I am looking at using a JOIN. If I need to, I will just use LINQ, but my question is whether or not this is possible using the repository setup that I currently have.

EDIT 2

Here is the repository code. I have tried to only include the relevant pieces. Each table has its own repository. Here is the signature of the PaymentsRepository:

public class PaymentsRepository : RepositoryBase<Payment, int>, IPaymentsRepository
{
}

The RepositoryBase<> looks like this:

public abstract class RepositoryBase<TTable, TKey> : IRepository<TTable, TKey>
       where TTable : IKeyedTable<TKey>, new()
{
    protected readonly ISqliteService SqliteService;

    protected RepositoryBase(ISqliteService sqlLiteService)
    {
        SqliteService = sqlLiteService;
    }

    public async Task<IEnumerable<TTable>> LoadAllAsync()
    {
        var query = SqliteService.Conn.Table<TTable>();
        var array = (await query.ToListAsync()).ToArray();

        return array;
    }
    ......
}

The IRepository interface:

interface IRepository<TTable, in TKey>
 where TTable : IKeyedTable<TKey>, new()
{
    Task<TTable> LoadByIdAsync(TKey id);
    Task<IEnumerable<TTable>> LoadAllAsync();
    Task InsertAsync(TTable item);
    Task UpdateAsync(TTable item);
    Task DeleteAsync(TTable item);
    AsyncTableQuery<TTable> Query();
}

And the ISqliteService:

public interface ISqliteService
{
    SQLiteAsyncConnection Conn { get; }
    Task<object> ClearLocalDb();
    void Reconnect();
}

Everything is ultimately queried against that SQLiteAsyncConnection property, using the built-in SQLite methods. For example, in the LoadAllAsync() function, var query = _sqliteService.Conn.Table<TTable>(); uses this:

public AsyncTableQuery<T> Table<T> ()
    where T : new ()
{
    //
    // This isn't async as the underlying connection doesn't go out to the database
    // until the query is performed. The Async methods are on the query iteself.
    //
    var conn = GetConnection ();
    return new AsyncTableQuery<T> (conn.Table<T> ());
}

which is located in SQLiteAsync.cs

Upvotes: 0

Views: 1673

Answers (2)

evictednoise
evictednoise

Reputation: 593

I think you can get IQueryable<Payment> and IQueryable<Payee>, join them in LINQ, and then call .ToArray() on the result.

It will build up the query and perform it only when you actually access the data (in this case, on the ToArray() call). I believe this should generate a single query.

Upvotes: 0

dub stylee
dub stylee

Reputation: 3342

I wasn't able to figure out a way to directly query two different tables using LINQ, but I got things working with a "hybrid" class. I just created a PaymentInfo class that has a Payment property and a Payee property, which point to the relevant data. I added a method to my PaymentsRepository that looks like this:

public async Task<IEnumerable<PaymentInfo>> LoadAllPaymentInfoAsync()
{
    var payments = await SqliteService.Conn.Table<Payment>().ToListAsync();
    var payees = await SqliteService.Conn.Table<Payee>().ToListAsync();

    var query = from p1 in payments
        join p2 in payees on p1.PayeeId equals p2.Id
        select new PaymentInfo() {Payment = p1, Payee = p2};

    return query;
}

I am sure that this is not necessarily the best way to accomplish this, but I thought I would share it here in case anyone comes across this page looking to do what I did.

Upvotes: 1

Related Questions